Another Easy Question
in [Programming]
|
Prev: Link checker: checking if a URL exists
Next: Debugging
From: MovingBeyondtheRecordButton on 26 Mar 2010 08:50 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:10 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:28 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: Don Guillett on 26 Mar 2010 09:27 I have no idea what you mean by "give a name". You would NOT want to name each cell. Sub addto() dim f as range For Each f In Worksheets("Sheet30").Range("A4:A40", "F4:F40") If Len(Application.Trim(f)) > 0 And IsNumeric(f) Then f.Value = 1000 & f.Value End If Next f End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "MovingBeyondtheRecordButton" <MovingBeyondtheRecordButton(a)discussions.microsoft.com> wrote in message news:A486B6A6-9EAD-4ECA-A640-392FB8119773(a)microsoft.com... > 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:42
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 > > > > > > > > > |