Changing Dim?

M

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



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

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
 
J

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
 
M

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.






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

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
 
M

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



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

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
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top