Changing a Formula Variable
 

I am using HLookup to copy budget model data into a "User" column based on
the size of the business. The HLookup is working great, but when I copy the
first cell down for all the rows of data, the "row variable" (1,TRUE) in the
HLookup formula does not change. It stays at the first row.
Here is the formula:
=HLOOKUP($A$13,$C$1:$F$10,1,TRUE)
I want to create a macro that let's me select all the cells containing the
formula, starting with the first (good) formula, goes to the next cell in the
selected range, reads the formula, adds 1 to the row variable...(so that the
2nd row would be 2,TRUE), writes the formula, goes to the next cell, adds 1
again (assuming the macro would have a variable that would be kept until the
next time 1 is added) so that the 3rd cell would be 3,TRUE and continue until
the end of the selected range.
All the formulas would be in the same column. Just need each cell to advance
by 1.
Here's what I've started with:
Sub DoOnSelection()
Dim oCell As Range
For Each oCell In Selection
NEED CODE HERE TO CHANGE THE NUMBER IN FORMULA
Next
End Sub

Thanks in advance for the help!!

Changing a Formula Variable
 

this was posted somewhere, would something like this help?

line1=line1.offset(1,0)



--


Gary


"David" wrote in message
...
I am using HLookup to copy budget model data into a "User" column based on
the size of the business. The HLookup is working great, but when I copy
the
first cell down for all the rows of data, the "row variable" (1,TRUE) in
the
HLookup formula does not change. It stays at the first row.
Here is the formula:
=HLOOKUP($A$13,$C$1:$F$10,1,TRUE)
I want to create a macro that let's me select all the cells containing the
formula, starting with the first (good) formula, goes to the next cell in
the
selected range, reads the formula, adds 1 to the row variable...(so that
the
2nd row would be 2,TRUE), writes the formula, goes to the next cell, adds
1
again (assuming the macro would have a variable that would be kept until
the
next time 1 is added) so that the 3rd cell would be 3,TRUE and continue
until
the end of the selected range.
All the formulas would be in the same column. Just need each cell to
advance
by 1.
Here's what I've started with:
Sub DoOnSelection()
Dim oCell As Range
For Each oCell In Selection
NEED CODE HERE TO CHANGE THE NUMBER IN FORMULA
Next
End Sub

Thanks in advance for the help!!

Changing a Formula Variable
 

Hi,

if you change the formula like

=HLOOKUP($A$13,$C$1:$F$10,ROW(A1),TRUE)

ROW(A1) is the substitute of 1 and it increases as you copy down

is this what you wanted?


--
jindon
------------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135
View this thread: http://www.excelforum.com/showthread...hreadid=376194

Changing a Formula Variable
 

That's it!! All I needed...thanks much!

"jindon" wrote:


Hi,

if you change the formula like

=HLOOKUP($A$13,$C$1:$F$10,ROW(A1),TRUE)

ROW(A1) is the substitute of 1 and it increases as you copy down

is this what you wanted?


--
jindon
------------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135
View this thread: http://www.excelforum.com/showthread...hreadid=376194

Changing a Formula Variable
 

Actually, as I have data in multiple rows, the PERFECT solution is....
=HLOOKUP($A$13,$C$1:$F$10,ROW(1:1),TRUE)
Thanks for pointing me in the right direction!!

"jindon" wrote:


Hi,

if you change the formula like

=HLOOKUP($A$13,$C$1:$F$10,ROW(A1),TRUE)

ROW(A1) is the substitute of 1 and it increases as you copy down

is this what you wanted?


--
jindon
------------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135
View this thread: http://www.excelforum.com/showthread...hreadid=376194