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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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


"Michael Smith" wrote in message
...
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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Julie,

How do you know it contains a number? :-)

Regards

Bob


"JulieD" wrote in message
...
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


"Michael Smith" wrote in message
...
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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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

"Bob Phillips" wrote in message
...
Hi Julie,

How do you know it contains a number? :-)

Regards

Bob


"JulieD" wrote in message
...
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


"Michael Smith" wrote in message
...
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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

You trusting soul :-). Must be the Aussie in you.

Regards

Bob


"JulieD" wrote in message
...
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

"Bob Phillips" wrote in message
...
Hi Julie,

How do you know it contains a number? :-)

Regards

Bob


"JulieD" wrote in message
...
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


"Michael Smith" wrote in message
...
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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

:) ... one of my more serious faults

"Bob Phillips" wrote in message
...
You trusting soul :-). Must be the Aussie in you.

Regards

Bob


"JulieD" wrote in message
...
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

"Bob Phillips" wrote in message
...
Hi Julie,

How do you know it contains a number? :-)

Regards

Bob


"JulieD" wrote in message
...
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


"Michael Smith" wrote in message
...
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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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.






*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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

"Michael Smith" wrote in message
...
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.






*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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



"Michael Smith" wrote in message
...
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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Michael Smith wrote:
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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!