Another Easy Question
in [Programming]
|
Prev: Link checker: checking if a URL exists
Next: Debugging
From: MovingBeyondtheRecordButton on 26 Mar 2010 09:47 I tried your code and it changed the submission numbers in my range to include the preface 1000 but...maybe I didn't explain myself...I don't want see the number with the 1000 in front in each cell but rather the sql database needs the longer form of the submission number in order to run the query. That is why I wanted to give this new number the name Num and use Num in my query. On a side note: The other thing that was strange when I ran your code was cell F4 has the 1000 placed in front of it twice. Originally F4=39480 Now F4=1000100039480 "Mike H" wrote: > Hi, > > I don't understand what you mean by > > >and give this new number a name. > > But this macro puts the 1000 in front of each number > > Sub sonic() > Dim F As Range > Set sht = Sheets("Sheet1") > For Each F In sht.Range("A4:A40") > > If F.Value <> "" Then > F.Value = 1000 & F.Value > End If > > If F.Offset(, 5).Value <> "" Then > F.Offset(, 5).Value = 1000 & F.Offset(, 5).Value > End If > Next > End Sub > > > Mike > > When competing hypotheses are otherwise equal, adopt the hypothesis that > introduces the fewest assumptions while still sufficiently answering the > question. > > > "MovingBeyondtheRecordButton" wrote: > > > How do I take the value from each cell defined in the range("A4:A40,F4:F40") > > and place 1000 before the value and give this new number a name. > > > > Example: > > Number in A4 is 37984 > > I want to use 100037984 > > > > I have tried... > > > > Dim F As Range > > Dim myNum As Variant > > Dim Num As Long > > Worksheets("Sheet1").Activate > > For Each F In Range("A4:A40", "F4:F40") > > myNum = F.Value > > Num = myNum & 1000 > > > > > >
From: MovingBeyondtheRecordButton on 26 Mar 2010 09:51 I don't want to assign it back to the cell. I want to put the 1000 in front of each F in the range and assign this number to the name Num and use Num as the input for my sql query. "Mike H" wrote: > Hi, > > I agree that Range("A4:A40,F4:F40") would work but in my solution I did it > differently by using offset. I still don't understand what you mean by 'Num' > unless you create an array a variable can only have 1 value. > > You loop through the range using the range object F. In my solution I used > F.value and while this is good practice using value isn't necessary because > value is the default property of a range object so we could get away with > > For Each F In sht.Range("A4:A40") > If F <> "" Then > F = 1000 & F > End If > > > To assign the value of F to a variable put 1000 in front of it and then > write ot back to the cell is simply not necessary and adds needless lines of > code. > -- > Mike > > When competing hypotheses are otherwise equal, adopt the hypothesis that > introduces the fewest assumptions while still sufficiently answering the > question. > > > "MovingBeyondtheRecordButton" wrote: > > > I should have written the range as Range("A4:A40,F4:F40") since I am trying > > to use the numbers listed in columns A and F cells 4 through 40. > > > > What I meant by give it a name is...I used the name "Num" to represent the > > number all the way down through the macro. So basically, I want to define > > this number created after putting the 1000 in front and call it "Num". > > > > "Mike H" wrote: > > > > > Hi, > > > > > > I don't understand what you mean by > > > > > > >and give this new number a name. > > > > > > But this macro puts the 1000 in front of each number > > > > > > Sub sonic() > > > Dim F As Range > > > Set sht = Sheets("Sheet1") > > > For Each F In sht.Range("A4:A40") > > > > > > If F.Value <> "" Then > > > F.Value = 1000 & F.Value > > > End If > > > > > > If F.Offset(, 5).Value <> "" Then > > > F.Offset(, 5).Value = 1000 & F.Offset(, 5).Value > > > End If > > > Next > > > End Sub > > > > > > > > > Mike > > > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > > introduces the fewest assumptions while still sufficiently answering the > > > question. > > > > > > > > > "MovingBeyondtheRecordButton" wrote: > > > > > > > How do I take the value from each cell defined in the range("A4:A40,F4:F40") > > > > and place 1000 before the value and give this new number a name. > > > > > > > > Example: > > > > Number in A4 is 37984 > > > > I want to use 100037984 > > > > > > > > I have tried... > > > > > > > > Dim F As Range > > > > Dim myNum As Variant > > > > Dim Num As Long > > > > Worksheets("Sheet1").Activate > > > > For Each F In Range("A4:A40", "F4:F40") > > > > myNum = F.Value > > > > Num = myNum & 1000 > > > > > > > > > > > >
From: Mike H on 26 Mar 2010 09:56 Hi, No you didn't mention you needed to pass that to an SQL query. Is this what you want? Sub sonic() Dim F As Range Set sht = Sheets("Sheet1") For Each F In ActiveSheet.Range("A4:A40, F4:F40") If F.Value <> "" Then Num = 1000 & F.Value 'Run SQL query using NUM? End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "MovingBeyondtheRecordButton" wrote: > I tried your code and it changed the submission numbers in my range to > include the preface 1000 but...maybe I didn't explain myself...I don't want > see the number with the 1000 in front in each cell but rather the sql > database needs the longer form of the submission number in order to run the > query. That is why I wanted to give this new number the name Num and use Num > in my query. > > On a side note: The other thing that was strange when I ran your code was > cell F4 has the 1000 placed in front of it twice. Originally F4=39480 Now > F4=1000100039480 > > "Mike H" wrote: > > > Hi, > > > > I don't understand what you mean by > > > > >and give this new number a name. > > > > But this macro puts the 1000 in front of each number > > > > Sub sonic() > > Dim F As Range > > Set sht = Sheets("Sheet1") > > For Each F In sht.Range("A4:A40") > > > > If F.Value <> "" Then > > F.Value = 1000 & F.Value > > End If > > > > If F.Offset(, 5).Value <> "" Then > > F.Offset(, 5).Value = 1000 & F.Offset(, 5).Value > > End If > > Next > > End Sub > > > > > > Mike > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > introduces the fewest assumptions while still sufficiently answering the > > question. > > > > > > "MovingBeyondtheRecordButton" wrote: > > > > > How do I take the value from each cell defined in the range("A4:A40,F4:F40") > > > and place 1000 before the value and give this new number a name. > > > > > > Example: > > > Number in A4 is 37984 > > > I want to use 100037984 > > > > > > I have tried... > > > > > > Dim F As Range > > > Dim myNum As Variant > > > Dim Num As Long > > > Worksheets("Sheet1").Activate > > > For Each F In Range("A4:A40", "F4:F40") > > > myNum = F.Value > > > Num = myNum & 1000 > > > > > > > > >
From: Mike H on 26 Mar 2010 09:56 I meant Sub sonic() Dim F As Range Set sht = Sheets("Sheet1") For Each F In sht.Range("A4:A40, F4:F40") If F.Value <> "" Then Num = 1000 & F.Value 'Run SQL query using NUM? End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "MovingBeyondtheRecordButton" wrote: > I tried your code and it changed the submission numbers in my range to > include the preface 1000 but...maybe I didn't explain myself...I don't want > see the number with the 1000 in front in each cell but rather the sql > database needs the longer form of the submission number in order to run the > query. That is why I wanted to give this new number the name Num and use Num > in my query. > > On a side note: The other thing that was strange when I ran your code was > cell F4 has the 1000 placed in front of it twice. Originally F4=39480 Now > F4=1000100039480 > > "Mike H" wrote: > > > Hi, > > > > I don't understand what you mean by > > > > >and give this new number a name. > > > > But this macro puts the 1000 in front of each number > > > > Sub sonic() > > Dim F As Range > > Set sht = Sheets("Sheet1") > > For Each F In sht.Range("A4:A40") > > > > If F.Value <> "" Then > > F.Value = 1000 & F.Value > > End If > > > > If F.Offset(, 5).Value <> "" Then > > F.Offset(, 5).Value = 1000 & F.Offset(, 5).Value > > End If > > Next > > End Sub > > > > > > Mike > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > introduces the fewest assumptions while still sufficiently answering the > > question. > > > > > > "MovingBeyondtheRecordButton" wrote: > > > > > How do I take the value from each cell defined in the range("A4:A40,F4:F40") > > > and place 1000 before the value and give this new number a name. > > > > > > Example: > > > Number in A4 is 37984 > > > I want to use 100037984 > > > > > > I have tried... > > > > > > Dim F As Range > > > Dim myNum As Variant > > > Dim Num As Long > > > Worksheets("Sheet1").Activate > > > For Each F In Range("A4:A40", "F4:F40") > > > myNum = F.Value > > > Num = myNum & 1000 > > > > > > > > >
From: MovingBeyondtheRecordButton on 26 Mar 2010 10:39
Thank you...I learned a lot through your various post. And your last post was the answer to my question. "Mike H" wrote: > I meant > > Sub sonic() > Dim F As Range > Set sht = Sheets("Sheet1") > For Each F In sht.Range("A4:A40, F4:F40") > If F.Value <> "" Then > Num = 1000 & F.Value > 'Run SQL query using NUM? > End If > > Next > End Sub > -- > Mike > > When competing hypotheses are otherwise equal, adopt the hypothesis that > introduces the fewest assumptions while still sufficiently answering the > question. > > > "MovingBeyondtheRecordButton" wrote: > > > I tried your code and it changed the submission numbers in my range to > > include the preface 1000 but...maybe I didn't explain myself...I don't want > > see the number with the 1000 in front in each cell but rather the sql > > database needs the longer form of the submission number in order to run the > > query. That is why I wanted to give this new number the name Num and use Num > > in my query. > > > > On a side note: The other thing that was strange when I ran your code was > > cell F4 has the 1000 placed in front of it twice. Originally F4=39480 Now > > F4=1000100039480 > > > > "Mike H" wrote: > > > > > Hi, > > > > > > I don't understand what you mean by > > > > > > >and give this new number a name. > > > > > > But this macro puts the 1000 in front of each number > > > > > > Sub sonic() > > > Dim F As Range > > > Set sht = Sheets("Sheet1") > > > For Each F In sht.Range("A4:A40") > > > > > > If F.Value <> "" Then > > > F.Value = 1000 & F.Value > > > End If > > > > > > If F.Offset(, 5).Value <> "" Then > > > F.Offset(, 5).Value = 1000 & F.Offset(, 5).Value > > > End If > > > Next > > > End Sub > > > > > > > > > Mike > > > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > > introduces the fewest assumptions while still sufficiently answering the > > > question. > > > > > > > > > "MovingBeyondtheRecordButton" wrote: > > > > > > > How do I take the value from each cell defined in the range("A4:A40,F4:F40") > > > > and place 1000 before the value and give this new number a name. > > > > > > > > Example: > > > > Number in A4 is 37984 > > > > I want to use 100037984 > > > > > > > > I have tried... > > > > > > > > Dim F As Range > > > > Dim myNum As Variant > > > > Dim Num As Long > > > > Worksheets("Sheet1").Activate > > > > For Each F In Range("A4:A40", "F4:F40") > > > > myNum = F.Value > > > > Num = myNum & 1000 > > > > > > > > > > > > |