Table Reference (Round 2)

[bryanworkman]

I am very novice to VBA and table references are new to me. I have a macro that needs to go loop through every row of a table and determine the value of each cell in a specific column. That column is subject to change depending on other factors in the macro. I have a variable which captures the name of the column header that needs to be searched (colHeader) but I get an error everytime I try to use that variable in the code below. When I replace the concatenation with just the name of the column ([Tbl_BIS[Column 2]].Column) it works just fine so I'm assuming I'm concatenating wrong or this just can't work this way. Is there a simple mistake here, or is there another way to do this?

Dim rowNum as Long
Dim colHeader as String
Dim cellValue as string

rowNum = 1
colHeader = "Column 2"

cellValue = [Tbl_BIS].Cells(rowNum, "[Tbl_BIS[" & orgLevel & "]].Column")

 

[DanteAmor]

Another approach

Sub test2()
  Dim rowNum As Long, colHeader As String, cellValue As String
  [COLOR=#0000ff]rowNum [/COLOR]= 1
  [COLOR=#ff0000]colHeader [/COLOR]= "Column 2"
  cellValue = ActiveSheet.ListObjects("Tbl_BIS").ListColumns([COLOR=#ff0000]colHeader[/COLOR]).DataBodyRange([COLOR=#0000ff]rowNum[/COLOR])
End Sub

 

[bryanworkman]

Another approach

Sub test2()
  Dim rowNum As Long, colHeader As String, cellValue As String
  [COLOR=#0000ff]rowNum [/COLOR]= 1
  [COLOR=#ff0000]colHeader [/COLOR]= "Column 2"
  cellValue = ActiveSheet.ListObjects("Tbl_BIS").ListColumns([COLOR=#ff0000]colHeader[/COLOR]).DataBodyRange([COLOR=#0000ff]rowNum[/COLOR])
End Sub

This worked perfectly! I tried a version of this format and was getting nowhere. Thanks!

 

[DanteAmor]

Im glad to help you. Thanks for the feedback.