folks,
not quite sure how to present this properly. so bear with with me:
1. i have userform, and would like to do a calc inside the userform, referencing a cell in the xls
2. the cell is in the same row as the 'active cell', but it is in a different column
and also,
3. since the xls is in development, i keep moving columns around; so i would like to use a reference to the name of the column in the header row, rather than a column letter [like col A, or B, or whatever]
now, this item in the userform gets me the active row: ActiveCell.Row and this gets me the column: "H"
so to get the value in the cell: Me.abc.Value = Range("L" & ActiveCell.Row).Value
what i need to is to replace "L" with a header row 'name' so if i move columns this link still works. i tried this:
Me.txtABC.Value = Range("Table134235[[#Headers],[Sales]]" & ActiveCell.Row).Value and i get this error:
..run time error 1004.. ..Method 'Range' of object '_Global' failed
so the reference to the header row and column: Sales is not returning the equivalent of the old "L"
can anyone tell me what i am missing here?
probably my lack of understanding of what is 'inside' the reference Table134235[[#Headers],[Sales]]. it clearly isn't equal to L.
i tried putting the reference into a MsgBox to display, but that failed as well.
thanks,
ron
not quite sure how to present this properly. so bear with with me:
1. i have userform, and would like to do a calc inside the userform, referencing a cell in the xls
2. the cell is in the same row as the 'active cell', but it is in a different column
and also,
3. since the xls is in development, i keep moving columns around; so i would like to use a reference to the name of the column in the header row, rather than a column letter [like col A, or B, or whatever]
now, this item in the userform gets me the active row: ActiveCell.Row and this gets me the column: "H"
so to get the value in the cell: Me.abc.Value = Range("L" & ActiveCell.Row).Value
what i need to is to replace "L" with a header row 'name' so if i move columns this link still works. i tried this:
Me.txtABC.Value = Range("Table134235[[#Headers],[Sales]]" & ActiveCell.Row).Value and i get this error:
..run time error 1004.. ..Method 'Range' of object '_Global' failed
so the reference to the header row and column: Sales is not returning the equivalent of the old "L"
can anyone tell me what i am missing here?
probably my lack of understanding of what is 'inside' the reference Table134235[[#Headers],[Sales]]. it clearly isn't equal to L.
i tried putting the reference into a MsgBox to display, but that failed as well.
thanks,
ron
