View Full Version : VBA Array Syntax

11-02-2008, 05:03 AM

I am very much a starter in VBA.

I want the following Column Headers to be in the Range(A5:A12)

"Company name", "Address Line1", "Address Line2", "Address Line3", "Address Line4", "Telephone", "Fax", "E Mail"

How to use an array to do this in VBA.

Thank You.

11-02-2008, 05:17 AM
Heyyyy... I'm awfully tired, but yer trying to trick us huh?

Okay, just teasing, but you said "A5:A12", which are rows in the first column...

Did you mean "E1:L1" ?

Or are you trying to place indicators/titles/headings in the same row (presumably for the cells beside them)?


11-02-2008, 05:42 AM
Oops, I am Sorry. Its "E1:L1".

11-02-2008, 06:27 AM
Sub TopRowHeader_ActiveSheet()
Dim _
aArray() As Variant, _
rngCell As Range
'// In this example, we chose to "build" the array simply by using the //
'// built-in 'Array' function. //
aArray = Array("Company name", "Address Line1", _
"Address Line2", "Address Line3", _
"Address Line4", "Telephone", "Fax", "E Mail")

'// Herein, we simply refer to the Range; thus the sheet that the range belongs //
'// to is presumed to be the active sheet.
For Each rngCell In Range("E1:L1")
rngCell.Value = aArray(rngCell.Column - 5)

End Sub

11-02-2008, 06:57 AM
Try this.

Range("E1:L1") = Array("Company name", "Address Line1", "Address Line2", "Address Line3", "Address Line4", "Telephone", "Fax", "E Mail")