refernce header text in code

[KUMPFfrog]

vb novice

in my code currently, i am using (1,3) format for cell ref in things like
Offset, etc.
What i would like to do is have the column not be based on a number, but the
header text in row 5.

in other word when b8 is active cell then the offset would be (0, and
whatever column contains "Invoice #" in header row 5.

 

[Per Jessen]

Hi

If you have the header "Invoice #" in column D I would use something like:

cells(ActiveCell.Row, "D")

Hopes this helps.

 

[KUMPFfrog]

Thanks, but the reason i wanted to ref header text is because it is always
going to be in the column that i want my form to place the user entries, no
matter if columns are added or deleted around it. IOW, if i later insert a
column in front of say "C" then "Invoice #" is no longer in "D", but "E".

 

[Per Jessen]

Then we have to look at named ranges.

Select the cell containing the header "Invoice #" and goto Insert > Name >
Define : Name the cell "Invoice" > Ok

Now you can reference to the cell using Range("Invoice"), so the answer to
our provious question will be:

iCol = Range("Invoice").Column
Cells(ActiveCell.Row, iCol) = "Hello"

Hopes this helps