Consulting

Results 1 to 5 of 5

Thread: VBA Array Syntax

  1. #1

    VBA Array Syntax

    Hi,

    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.

  2. #2
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,362
    Location
    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)?

    Mark

  3. #3
    Oops, I am Sorry. Its "E1:L1".

  4. #4
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,362
    Location
    [vba]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)
    Next

    End Sub[/vba]

  5. #5
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    Try this.
    [vba]
    Range("E1:L1") = Array("Company name", "Address Line1", "Address Line2", "Address Line3", "Address Line4", "Telephone", "Fax", "E Mail")
    [/vba]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •