Hello,

I would like to have a formula entered into a cell which references the
address of another cell, already determined by VBA variables. I have tried
different combinations of code but can only get the referenced cell's value,
or the referenced cell's address with single quotes around it, which doesn't
work. Here's a bit of my code...

Dim T1cell, T2cell, T3cell As Range

For Each c In Range("J2:J" & Rws)
If c.Value = "1 Total" Then Set T1cell = c.Offset(, 2)
If c.Value = "2 Total" Then Set T2cell = c.Offset(, 2)
If c.Value = "3 Total" Then Set T3cell = c.Offset(, 2)
Next c

Other code here...

If Tier = 1 Then
USDcell.FormulaR1C1 = "=if(" & T1cell.Address & "<0,RC[-4]*0.03,0)"
ElseIf Tier = 2 Then
USDcell.FormulaR1C1 = "=if(" & T2cell.Address & "<0,RC[-4]*0.05,0)"
ElseIf Tier = 3 Then
USDcell.FormulaR1C1 = "=if(" & T3cell.Address & "<0,RC[-4]*0.08,0)"
End If

Any ideas?

Thanks,
Nate Kolman

Hi Nate,

Dim T1cell, T2cell, T3cell As Range

Didn't try you code, but only T3cell is dimensioned as a range
try changing to

Dim T1cell as Range, T2cell as Range, T3cell as Range
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Nate Kolman" <Nate wrote in message ...
Hello,

I would like to have a formula entered into a cell which references the
address of another cell, already determined by VBA variables. I have tried
different combinations of code but can only get the referenced cell's value,
or the referenced cell's address with single quotes around it, which doesn't
work. Here's a bit of my code...

Dim T1cell, T2cell, T3cell As Range

For Each c In Range("J2:J" & Rws)
If c.Value = "1 Total" Then Set T1cell = c.Offset(, 2)
If c.Value = "2 Total" Then Set T2cell = c.Offset(, 2)
If c.Value = "3 Total" Then Set T3cell = c.Offset(, 2)
Next c

Other code here...

If Tier = 1 Then
USDcell.FormulaR1C1 = "=if(" & T1cell.Address & "<0,RC[-4]*0.03,0)"
ElseIf Tier = 2 Then
USDcell.FormulaR1C1 = "=if(" & T2cell.Address & "<0,RC[-4]*0.05,0)"
ElseIf Tier = 3 Then
USDcell.FormulaR1C1 = "=if(" & T3cell.Address & "<0,RC[-4]*0.08,0)"
End If

Any ideas?

Thanks,
Nate Kolman

try something like ...

For Each c In Range("J2:J" & Rws)
If c.Value = "1 Total" Then Set T1cell.Formula = "=" & c.Offset(,
2).Address
--- etc
Next c

I asssume that T1Cell has already been set to a range?

"Nate Kolman" <Nate wrote in message
...
Hello,

I would like to have a formula entered into a cell which references the
address of another cell, already determined by VBA variables. I have
tried
different combinations of code but can only get the referenced cell's
value,
or the referenced cell's address with single quotes around it, which
doesn't
work. Here's a bit of my code...

Dim T1cell, T2cell, T3cell As Range

For Each c In Range("J2:J" & Rws)
If c.Value = "1 Total" Then Set T1cell = c.Offset(, 2)
If c.Value = "2 Total" Then Set T2cell = c.Offset(, 2)
If c.Value = "3 Total" Then Set T3cell = c.Offset(, 2)
Next c

Other code here...

If Tier = 1 Then
USDcell.FormulaR1C1 = "=if(" & T1cell.Address & "<0,RC[-4]*0.03,0)"
ElseIf Tier = 2 Then
USDcell.FormulaR1C1 = "=if(" & T2cell.Address & "<0,RC[-4]*0.05,0)"
ElseIf Tier = 3 Then
USDcell.FormulaR1C1 = "=if(" & T3cell.Address & "<0,RC[-4]*0.08,0)"
End If

Any ideas?

Thanks,
Nate Kolman