Referencing columns by defined names in Range Function

[Abish Musthafa]

Hi,

Appreciate if someone can help me with this.
I am trying to reference a column with a defined name inside a range function. However, I am getting the dreaded run time error 1004 whenever I run the macro.
An extract of my code looks like this now:


If Sheet1.Range("lapcolshortname" & FirstRow + i) <> "" And _
Sheet1.Range("lapcolannualbenefit" & FirstRow + i) <> "" And _
......
"lapcolshortname" refers to a defined named that refers to a column, in this case Sheet1!$X$X.

Please advice what is it that I might be doing wrong here or should I define the names at an earlier stage?

I am new to VBA, so please bear any mistakes I might have made in the presentation of this question.
Cheers
A

 

[NARAYANK991]

Hi Abish ,

The problem is that the named range refers to an entire column ; thus , its definition is $X:$X , and not just $X.

An Excel address is a concatenation of a column and row , as in A1 ; thus , you can have a construct such as :

Range("A" & FirstRow)

but you cannot have a construct such as :

Range("X:X" & FirstRow)

Hence , when you have a named range which refers to an entire column , change the usage to something like this :

Range("lapcolshortname").Cells(FirstRow + i).Value

Narayan

 

[Abish Musthafa]

Thanks Narayan..This is brilliant and it worked!!