Changing Dim?

[Michael Smith]

How would I Dim whatever number is in row 1 of the ActiveColumn
...what I have is a formula that I want to react based on the cell in
row 1 of the column the formula is in.

TIA - mike

 

[JulieD]

Hi Mike

do you mean how do you assign the value of row 1 of the activecolumn to a
variable?
if so, here's an example

Sub mymacro2()
Dim i As Long
i = Cells(1, ActiveCell.Column)
MsgBox i
End Sub


hope this helps
Cheers
JulieD

 

[Bob Phillips]

Hi Julie,

How do you know it contains a number?

Regards

Bob

 

[JulieD]

well i'm assuming that when the OP said

"How would I Dim whatever NUMBER is in row 1"
(emphasis mine)

he meant what he said - on that bit anyway

Cheers
JulieD

 

[Michael Smith]

That gets me closer....when I place that Variable in my formula it
works....but as I copy that formula across to different columns it
retains the first column's data, instead of using the active column the
formula is in.

 

[JulieD]

Hi Michael

are you doing this via code (if so could you copy & paste the relevant bit
of code where you're "copying it across to different columns")
or are you doing this in the worksheet as a formula?

Cheers
JulieD

 

[Bob Phillips]

You trusting soul . Must be the Aussie in you.

Regards

Bob

 

[JulieD]

... one of my more serious faults

You trusting soul . Must be the Aussie in you.

Regards

Bob

 

[Michael Smith]

Yeah I'm doing it in code....here's a little more detail, basically I
have a vlookup that needs to look at a limited piece of a table array,
the limitation of rows is my Variable.
Sooooo in this piece of formula....

=vlookup(cell,$A$1:$A$50,.....

I need the 50 to be whatever is in row 1 of the active column, (which is
where my variable is)

I know I am sharing my headache with you by now....much appreciated.

-mike

 

[JulieD]

Hi Michael

apart from the fact that i think your look-up table should be more than one
column wide will this give you what you need

=VLookup(cell, Range("$A$1:$A$" & i & ""),.....

Cheers
JulieD

 

[Dick Kusleika]

Michael

I'm not sure why you're doing it in code.

=VLOOKUP(B3,INDIRECT("A1:A" & B$1),1,FALSE)

where the formula is in column B. The relative column in B$1 will move as
you copy it accross columns.