VBA formulas (Excel 200 or 2003)

Topic

New Reply

Hi All, me again …

I have dynamically placed a cell link in cell AH3 via VBA code. Now, I need to determine the row number of the formula in this cell and add a constant to it.
Let’s say,

Constant = 10
AG3 contains this formula /link………….. =E8
AH3 should contain this formula/link…. =E18 (8 + Constant)

I do not know how to obtain the ‘8’ from the formula in cell AG3. Is this possible? If not, I will try another way …

Thanks for any help …
–cat

Reply | Quote

Replies

Check out the Row function

Reply | Quote

Hi Catherine,

The Row function gives me the number 3 … since the cell is AG3.

I need to grab the ‘8’ from the formula ‘=E8’

Thanks,
–cat

Reply | Quote

It might help if we knew what code you used to place a formula in AG3.

Reply | Quote

Hi Hans,

In another macro, the formula “=E8” was placed in cell AH3. Please note, though, that E8 is not always constant .

The formula was placed using:

Range(“AH3”).formula = “=E” & nextrow

to place the formula.

Now, I need to know that row number …
I cannot get to this macro in order to keep track of the row number when the link is placed in cell AH3 … which would be the best way to do this.

So, I was wondering if there is a way to “pull” the row number out of the formula. The “E” will always be the same …

Does any of this make any sense?

Thank you very much for your time and effort …

–cat

Reply | Quote

I’m not sure I understand your description, but try something like this:

Dim lngRow As Long
‘ Retrieve row
lngRow = Val(Mid(Range(“AH3”).Formula, 3))
‘ Add 10
lngRow = lngRow + 10
‘ Change formula
Range(“AH3”).Formula = “=E” & lngRow

Reply | Quote