-
Is there a faster way to do the following?
I have a lot of code in my macro like the following example.
Is there a more efficient way of doing it by somehow selecting a larger range and using an array of string vars?
[vba]
Range("E1").Select
ActiveCell.FormulaR1C1 = "Step 2"
Range("E3").Select
ActiveCell.FormulaR1C1 = "ID #"
Range("E5").Select
ActiveCell.FormulaR1C1 = "Address"
Range("E7").Select
ActiveCell.FormulaR1C1 = "City"
Range("E8").Select
ActiveCell.FormulaR1C1 = "Province"
Range("E11").Select
ActiveCell.FormulaR1C1 = "Postal Code"
Range("E13").Select
ActiveCell.FormulaR1C1 = "rank"
[/vba]
-
[VBA]Range("E1").Value2 = "Step 2" [/VBA]
-
Thanks for the tip. What's the difference between Value2 and Value?
How about something more along the lines of (my syntax is wrong I am certain)
Range("E1,E3,E5,E7,E9").Value = {"Step 2","ID #","Address","City","Province"}
-
If you press F1 with cursor at or on a keyword in the vbe, you get specific help.
e.g.
Remarks
The only difference between this property and the Value property is that the Value2 property doesn’t use the Currency and Date data types. You can return values formatted with these data types as floating-point numbers by using the Double data type.
[VBA]Sub test()
Dim cell As Range, i As Integer, a()
a() = Array("Step 2", "ID #", "Address", "City", "Province")
For Each cell In Range("E1,E3,E5,E7,E9")
cell.Value2 = a(i)
i = i + 1
Next cell
End Sub[/VBA]
-
Ken's code is correct, but since you have to spend all that time looping as well as doing the 7 'one at a time' assigns anyway (since the data is scattered) I'd go with the more readable (IMHO) and on paper faster
[VBA]
Range("E1").Value = "Step 2"
Range("E3").Value = "ID #"
Range("E5").Value = "Address"
Range("E7").Value = "City"
Range("E8").Value = "Province"
Range("E11").Value = "Postal Code"
Range("E13").Value = "rank"
[/VBA]
Paul
-
Late reply but thanks for the help guys.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules