Since you are inside a With/End With block, I **think** you may want to put
a dot in front of the Range reference so you pick up the Range from the
currently being looked at worksheet...

Chng = .Range("F2")

I am presuming here you are trying to get to the Value property of F2 (I
hate relying on default properties the way I think you are doing as it makes
the code much harder to read when debugging) and not trying to Set a
reference to the Range itself (which would still need the leading dot)

Rick


"ryguy7272" wrote in message
...
I have to use, what I thought was, a simple variable input, but it's
turning
out to be not so simple. Basically I loop through all sheets in a book,
as
long as the sheets < "Sheet1". Then do some basic formatting and some
simple math. Everything works fine except for this part:
Chng = Range("F2")

and this part:
c.Offset(, 19).Value =
"=(IF(RC[-20]="&Chng&",RC[-18],0)+IF(RC[-15]="&Chng&",RC[-14],0))*RC[-3]"

I have names in column F of each sheet, which are all the same on each
sheet, but different names are on different sheets. All I need to do is
figure out the value in F2 on each sheet and use that in my IF function.
I
thought I could just assign the value in F2 to the variable Chng and then
just use that variable in my IF function. Excel has other thoughts and it
is
telling me that I can't do that. Why?

All code below:
Sub Math()
Dim lastRow As Long
Dim c As Variant
Dim sh As Worksheet
Dim myA As Range
Dim lastYRow As Long
Dim sumRng As Range
Dim Tot As Double
Dim Chng As Variant


For Each sh In Worksheets
If LCase(sh.Name) < LCase("Sheet1") Then
sh.Activate

With sh
.Rows(1).Font.Bold = True

Chng = Range("F2")

lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
For Each c In .Range("F2:F" & lastRow)
If c.Value < "" Then
c.Offset(, 19).Value =
"=(IF(RC[-20]="&Chng&",RC[-18],0)+IF(RC[-15]="&Chng&",RC[-14],0))*RC[-3]"
End If
Next c

lastYRow = .Cells(.Rows.Count, "Y").End(xlUp).Row
Set sumRng = .Range("Y2:Y" & lastYRow)
Tot = Application.WorksheetFunction.Sum(sumRng)
'MsgBox Tot
.Cells(lastRow + 1, "Y").Value = Tot

'.Rows(2).Delete
End With

End If

Next sh

End Sub

Any help is appreciated.

Regards,
Ryan--

--
RyGuy