Row reference in my VBA code

[gabe]

I wrote some VBA code that checks for a couple different variables and if the code passes then a formula is pasted into a specific location into my spreadsheet.

The formula is as follows:

=10^($AN19*LOG10($K19)+$AO19-$AP19*0.6745)

What's happening is that the formula is being pasted in my cell exactly as written above (row and column reference as-is). What I really want is for the column references to be locked (as noted) but the row reference to change based on the row where my formula ends up. It could be anywhere from row 1 to row 1000. How do I change the formula to allow the row reference to change based on the row where my formula is inserted into my spreadsheet?

Thanks,
Gabe

 

[mvptomlinson]

How about providing your current code so we can take a look how you're deducing which row to put the formula into? (Remember to use CODE tags!)

 

[Smitty]

How about setting a variable to your row reference:

Cells(i, "B").Formula = "=10^($AN" & i & "*LOG10($K" & i & ")+$AO" & i & "-$AP" & i & "*0.6745) "

HTH

 

[gabe]

I was at home last night and didn't have access to my spreadsheet with the actual code. Below is the code right before the formula that I wanted to insert into my spreadsheet. Again, the column references will always stay the same but I want the row references to change based on the row where the formula is inserted.

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Range(Lname.Name).Offset(LlngOutRow, 0).Formula = "=10^(AJ15*LOG10(G15*1000)+AK15-AL15*0.6745)/1000"

 

[pgc01]

Hi

One way

Dim r As Range
 
' ...
 
Set r = Range(Lname.Name).Offset(LlngOutRow, 0)
r.Formula = "=10^(AJ" & r.Row & "*LOG10(G" & r.Row & "*1000)+AK" & r.Row & "-AL" & r.Row & "*0.6745)/1000"

or use thr R1C1 notation:

Range(Lname.Name).Offset(LlngOutRow, 0).FormulaR1C1 = "=10^(RC36*LOG10(RC7*1000)+RC37-RC38*0.6745)/1000"

In this case the the column references are absolute.

 

[gabe]

The R1C1 code worked perfectly. Thanks for the help with this.