VBA Array Handling

[slawson7]

I want to create an array which looks like the following:
----------------------------
HEADER1 | HEADER2 | HEADER3|
----------------------------
15 |20 |25 |
1 |2 |3 |

This is a dynamic array. It will have at least one column, but most likley more. If my script finds a value of HEADER2, the two values below get updated. If it finds a HEADER4 value, the array is redimensioned and the values added.
My question is - how do I search the array to see if the header value already exists?

 

[Bong]

Are you really talking about an array, or is this really a table in a spreadsheet? If it's really an array, that is, arrayname(1,1)="header 1", etc., then you can use UBound to see if there's a column. That is, you would loop through column numbers until you get an error from colPop=UBound(arrayname, i). You'll have to use On Error ... to deal with a query into a column that isn't there (you'll get a subscript out of range error). I assume you'll be using reDim every time you add a new column.

If, on the other hand, you're really talking about columns in a spreadsheet (and not an array), you would then just loop through the columns in row-1 (assuming your table headers are in row-1) to see if the cells are poplulated, while not cells(1,i).value = ""

_________________
Bob Rashkin

 

[SkipVought]

Hi,

You are not referring to a VB array, but rather a spreadsheet array (range).

Check out How can I rename a table as it changes size faq68-1331

Skip,
[sub]
[red][/red]
[/sub]

 

[slawson7]

Bob - Thanks for the help. It is an array I'm talking about - I could use a spreadsheet to hold the value, but I think it would be clunky.

Skip - Thanks for the pointers, but I'm not sure I see how this helps. I need to search for the value in my header row in the array before I decide whether to add a new column, or update values in an existing column.

The approach I think I'll take is to loop through the array looking for the header. If it exists, set a flag. Then if the flag is set, simply update the appropriate values. If the flag is not set, redimension the array and write the data to it.

THanks for your help.

 

[duGly]

Based on what you've written, I think that Bong's reply is the best solution to your issue. Here is a code example based on his reply.

Private Sub CheckArray()
    Dim i%

    ' Iterate through first row of array.
    For i = 0 To UBound(myArray, 2)
    
        ' If match is found, update values and exit sub.
        If myArray(0, i) = "Header " & i Then
            myArray(1, i) = "Value x"
            myArray(2, i) = "Value y"
            Exit Sub
        End If
    Next    ' i
    
    ' If array is empty, decrease i before proceeding.
    If myArray(0, 0) = "" Then i = 0
    
    ' If no match found, redimension array.
    ReDim Preserve myArray(2, i)
    
    ' Set values of new member.
    myArray(0, i) = "Header " & i + 1
    myArray(1, i) = "Value x"
    myArray(2, i) = "Value y"

End Sub

Hope this helps.

[purple]— Artificial intelligence is no match for natural stupidity.[/purple]