VBA array question - column headings?

[TheWennerWoman]

Let's say I have three columns of data which I am pushing into an array.

If someone decides to insert a column after column A, say, then my data isn't what I want (arrA(a, 1) is fine but arrA(a, 2) actually refers to arrA(a, 3) and arrA(a, 3) actually refers to arrA(a, 4).

Can I substitute the numeric element with a variable?

So, let's assume column A of my source data is called "Surgery ID".

Could I assign a value to a variable based on a search for that value in column header?

In English, I'd say "search column headers for the value "Surgery ID" and assign it to a variable "a" which I would then reference in (arrA(a, a)"?

 

[offthelip]

You can set up an array which you can populate with numbers to work to transform a 1 to 3 index into the correct columns like this:

Option Base 1
Sub test()
 ' this is just t ostart the arrays at 1 as per the columns
lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
Firstrow = Range(Cells(1, 1), Cells(1, lastcol))  ' Load the column headers into an array
Col2Find = Array("Surgery ID", "Location", "Height")  ' Set up the headers you are looking for
Dim colnums() As Long   ' the is the array the column numbers end up in
ReDim colnums(1 To UBound(Col2Find))  
For i = 1 To UBound(Col2Find)
  For j = 1 To lastcol
   If Firstrow(1, j) = Col2Find(i) Then
    colnums(i) = j
    Exit For
   End If
  Next j
 Next i
 
For k = 1 To UBound(Col2Find)
 MsgBox (Col2Find(k) & "Column = " & colnums(k))
Next k


End Sub

 

[TheWennerWoman]

Perfect, works a treat. Many thanks

 

[Peter_SSs]

I may be mis-reading where you are headed with this but could you just read the columns-of -interest into your array and ignore the others?
For example, suppose ..

• you want the data from the columns "Surgery ID", "Location", "Height" but don't know for sure where they will be
• the last row of data can be determined from column A

.. then this should read those 3 columns of data directly into an array that has just 3 columns and in the order specified.

Sub Data_Into_Array()
  Dim MyArray As Variant, Col2Find As Variant
  Dim i As Long
  
  Col2Find = Split("Surgery ID|Location|Height", "|")  ' Set up the headers you are looking for, in the order you want
  For i = 0 To UBound(Col2Find)
    Col2Find(i) = Rows(1).Find(What:=Col2Find(i), LookAt:=xlWhole, MatchCase:=False).Column
  Next i
  MyArray = Application.Index(Cells, Evaluate("sequence(" & Range("A" & Rows.Count).End(xlUp).Row & ")"), Col2Find)
End Sub

Here is my sample data ..

TheWennerWoman.xlsm
	A	B	C	D
1	Surgery ID	Other	Height	Location
2	3	6	7	0
3	6	1	8	5
4	2	2	10	6
Sheet1

And here is 'MyArray' after the code has been run. Note that the order of columns in MyArray is the order specified in the 'Col2Find' line in the code, not the order in the worksheet.
If columns are inserted and/or rearranged and the code is run again, myArray would end up exactly the same as shown here

 

[TheWennerWoman]

Thank you for that, much appreciated.

All I was looking to achieve was to identify which column certain headers were in, I'm not overly bothered about the data underneath.

So in your example, Surgery ID would return 1 (as it's first), Height would return 3 and Location 4.

But if someone thinks it's a jolly good idea to insert six columns between Surgery ID and Other, I'd need to know that Height would now be at position 9 (column I) and Location at position 10 (column J).

 

[Peter_SSs]

All I was looking to achieve was to identify which column certain headers were in, I'm not overly bothered about the data underneath.

Hmm, I guess that I was swayed by ...

three columns of data which I am pushing into an array.

Anyway, I think that you can trim the suggested code down somewhat if you want. I think all that you need is this. At the end of the code, the array Col2Find will hold the relevant column numbers instead of names. The column numbers could be put into a different array if you wanted that instead.

Sub ColNumbers()
  Dim Col2Find As Variant
  Dim i As Long
  
  Col2Find = Array("Surgery ID", "Location", "Height")   ' Set up the headers you are looking for
  For i = LBound(Col2Find) To UBound(Col2Find)
    Col2Find(i) = Rows(1).Find(What:=Col2Find(i), LookAt:=xlWhole, MatchCase:=False).Column
  Next i
End Sub

 

[TheWennerWoman]

That works as well, many thanks and apologies for my clumsy wording

 

[TheWennerWoman]

Just one extra question about that, if the column heading isn't found the VBA errors (which I can fix with On Error Resume Next). Can I somehow inject a zero into the relevant element of the array if it's not found?

 

[offthelip]

wit h my code:

Option Base 1
Sub test()
 ' this is just t ostart the arrays at 1 as per the columns
lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
Firstrow = Range(Cells(1, 1), Cells(1, lastcol))  ' Load the column headers into an array
Col2Find = Array("Surgery ID", "Location", "Height")  ' Set up the headers you are looking for
Dim colnums() As Long   ' the is the array the column numbers end up in
ReDim colnums(1 To UBound(Col2Find))
For i = 1 To UBound(Col2Find)
  colnums(i) = 0                ' add this line
  For j = 1 To lastcol
   If Firstrow(1, j) = Col2Find(i) Then
    colnums(i) = j
    Exit For
   End If
  Next j
 Next i
 
For k = 1 To UBound(Col2Find)
 MsgBox (Col2Find(k) & "Column = " & colnums(k))
Next k


End Sub

 

[Peter_SSs]

For my code ..

Sub ColNumbers_v2()
  Dim Col2Find As Variant
  Dim rFound As Range
  Dim i As Long
  
  Col2Find = Array("Surgery ID", "Location", "Height")   ' Set up the headers you are looking for
  For i = LBound(Col2Find) To UBound(Col2Find)
    Set rFound = Rows(1).Find(What:=Col2Find(i), LookAt:=xlWhole, MatchCase:=False)
    Col2Find(i) = 0
    If Not rFound Is Nothing Then Col2Find(i) = rFound.Column
  Next i
End Sub