Variable Input

R

ryguy7272

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--
 
G

Gary''s Student

Chng = Range("F2").Value
to get the contents of the cell

c.Offset(, 19).Formula =
to setup a formula
 
R

ryguy7272

This line causes an error:
c.Offset(, 19).Formula = "=(IF(RC[-19]= & "" & Chng & "" &
,RC[-17],0)+IF(RC[-15]= & "" & Chng & "" & ,RC[-14],0))*RC[-2]"

These two tips got me one step closer:
Chng = Range("F2").Value
c.Offset(, 19).Formula =

This did not work:
"&Chng.address&"


This:
c.Offset(, 19).Formula = "=(IF(RC[-19]="" & Chng &
"",RC[-17],0)+IF(RC[-15]="" & Chng & "",RC[-14],0))*RC[-2]"

Gives this:
=(IF(F2=" & Chng & ",H2,0)+IF(J2=" & Chng & ",K2,0))*W2
As you can see, the 'Chng' goes into the function, but the individual's
name, which essentially is the F2.Value, should be in the function. I think
the issue now is the double quotes. I tried a few combinations of things;
nothing has worked so far. Any suggestions?



Thanks so much,
Ryan---

--
RyGuy


Joel said:
I think all you need is a minor change

from
"&Chng&"
to
"&Chng.address&"

ryguy7272 said:
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--
Click to expand...
Click to expand...
 
G

Gary''s Student

Double quotes can be a pain in the a$$ if you want to put them in a cell. I
usually use:

dq=Chr(34)

and then use this String variable.
--
Gary''s Student - gsnu200785


ryguy7272 said:
This line causes an error:
c.Offset(, 19).Formula = "=(IF(RC[-19]= & "" & Chng & "" &
,RC[-17],0)+IF(RC[-15]= & "" & Chng & "" & ,RC[-14],0))*RC[-2]"

These two tips got me one step closer:
Chng = Range("F2").Value
c.Offset(, 19).Formula =

This did not work:
"&Chng.address&"


This:
c.Offset(, 19).Formula = "=(IF(RC[-19]="" & Chng &
"",RC[-17],0)+IF(RC[-15]="" & Chng & "",RC[-14],0))*RC[-2]"

Gives this:
=(IF(F2=" & Chng & ",H2,0)+IF(J2=" & Chng & ",K2,0))*W2
As you can see, the 'Chng' goes into the function, but the individual's
name, which essentially is the F2.Value, should be in the function. I think
the issue now is the double quotes. I tried a few combinations of things;
nothing has worked so far. Any suggestions?



Thanks so much,
Ryan---

--
RyGuy


Joel said:
I think all you need is a minor change

from
"&Chng&"
to
"&Chng.address&"

ryguy7272 said:
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--
Click to expand...
Click to expand...
Click to expand...
 
J

Joel

Does this work?
=(IF(F2=" & F2 & ",H2,0)+IF(J2=" & F2 & ",K2,0))*W2

because that would be equivalent to
=(IF(F2=" & Chng.address & ",H2,0)+IF(J2=" & Chng.address & ",K2,0))*W2

Note : You need a double quote at the beginning and after the W2.
"=(IF(F2=" & F2 & ",H2,0)+IF(J2=" & F2 & ",K2,0))*W2"

"=(IF(F2=" & Chng.address & "H2,0)+IF(J2=" & Chng.address & ",K2,0))*W2"

ryguy7272 said:
This line causes an error:
c.Offset(, 19).Formula = "=(IF(RC[-19]= & "" & Chng & "" &
,RC[-17],0)+IF(RC[-15]= & "" & Chng & "" & ,RC[-14],0))*RC[-2]"

These two tips got me one step closer:
Chng = Range("F2").Value
c.Offset(, 19).Formula =

This did not work:
"&Chng.address&"


This:
c.Offset(, 19).Formula = "=(IF(RC[-19]="" & Chng &
"",RC[-17],0)+IF(RC[-15]="" & Chng & "",RC[-14],0))*RC[-2]"

Gives this:
=(IF(F2=" & Chng & ",H2,0)+IF(J2=" & Chng & ",K2,0))*W2
As you can see, the 'Chng' goes into the function, but the individual's
name, which essentially is the F2.Value, should be in the function. I think
the issue now is the double quotes. I tried a few combinations of things;
nothing has worked so far. Any suggestions?



Thanks so much,
Ryan---

--
RyGuy


Joel said:
I think all you need is a minor change

from
"&Chng&"
to
"&Chng.address&"

ryguy7272 said:
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--
Click to expand...
Click to expand...
Click to expand...
 
R

ryguy7272

Thant's a neat trick. I've used Char in the sheet, but never Chr in the
module. I guess there is a small difference in the syntax. Anyway, it still
doesn't work. This is one of those things that may only be resolved by using
the brute force method. Ugh....

--
RyGuy


Gary''s Student said:
Double quotes can be a pain in the a$$ if you want to put them in a cell. I
usually use:

dq=Chr(34)

and then use this String variable.
--
Gary''s Student - gsnu200785


ryguy7272 said:
This line causes an error:
c.Offset(, 19).Formula = "=(IF(RC[-19]= & "" & Chng & "" &
,RC[-17],0)+IF(RC[-15]= & "" & Chng & "" & ,RC[-14],0))*RC[-2]"

These two tips got me one step closer:
Chng = Range("F2").Value
c.Offset(, 19).Formula =

This did not work:
"&Chng.address&"


This:
c.Offset(, 19).Formula = "=(IF(RC[-19]="" & Chng &
"",RC[-17],0)+IF(RC[-15]="" & Chng & "",RC[-14],0))*RC[-2]"

Gives this:
=(IF(F2=" & Chng & ",H2,0)+IF(J2=" & Chng & ",K2,0))*W2
As you can see, the 'Chng' goes into the function, but the individual's
name, which essentially is the F2.Value, should be in the function. I think
the issue now is the double quotes. I tried a few combinations of things;
nothing has worked so far. Any suggestions?



Thanks so much,
Ryan---

--
RyGuy


Joel said:
I think all you need is a minor change

from
"&Chng&"
to
"&Chng.address&"

:

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--
Click to expand...
Click to expand...
Click to expand...
 
R

Rick Rothstein \(MVP - VB\)

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
 
R

ryguy7272

This thing is becoming the bane of my existence. The function below gets me
pretty close:
c.Offset(, 19).Formula = "=(IF(RC[-19]=" & Chng & " ,RC[-17],0)+IF(RC[-15]="
& Chng & ",RC[-14],0))*RC[-2]"

The thing now is that I see the person's name in the function, as such:
=(IF(F2=Todd,H2,0)+IF(J2=Todd,K2,0))*W2

The only thing missing is the quotes around the individual's name. What I
really need to see is this:
=(IF(F2="Todd",H2,0)+IF(J2="Todd",K2,0))*W2

Any more suggestions?

Regards,
Ryan---


--
RyGuy


Joel said:
Does this work?
=(IF(F2=" & F2 & ",H2,0)+IF(J2=" & F2 & ",K2,0))*W2

because that would be equivalent to
=(IF(F2=" & Chng.address & ",H2,0)+IF(J2=" & Chng.address & ",K2,0))*W2

Note : You need a double quote at the beginning and after the W2.
"=(IF(F2=" & F2 & ",H2,0)+IF(J2=" & F2 & ",K2,0))*W2"

"=(IF(F2=" & Chng.address & "H2,0)+IF(J2=" & Chng.address & ",K2,0))*W2"

ryguy7272 said:
This line causes an error:
c.Offset(, 19).Formula = "=(IF(RC[-19]= & "" & Chng & "" &
,RC[-17],0)+IF(RC[-15]= & "" & Chng & "" & ,RC[-14],0))*RC[-2]"

These two tips got me one step closer:
Chng = Range("F2").Value
c.Offset(, 19).Formula =

This did not work:
"&Chng.address&"


This:
c.Offset(, 19).Formula = "=(IF(RC[-19]="" & Chng &
"",RC[-17],0)+IF(RC[-15]="" & Chng & "",RC[-14],0))*RC[-2]"

Gives this:
=(IF(F2=" & Chng & ",H2,0)+IF(J2=" & Chng & ",K2,0))*W2
As you can see, the 'Chng' goes into the function, but the individual's
name, which essentially is the F2.Value, should be in the function. I think
the issue now is the double quotes. I tried a few combinations of things;
nothing has worked so far. Any suggestions?



Thanks so much,
Ryan---

--
RyGuy


Joel said:
I think all you need is a minor change

from
"&Chng&"
to
"&Chng.address&"

:

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--
Click to expand...
Click to expand...
Click to expand...
 
R

ryguy7272

Thanks for the look Rick. I tried it both ways:
Chng = .Range("F2")

and:
Chng = Range("F2").Value

The results were the same with both methods. So much for that statement
about this being 'simple' in my original post. Eyes rolling now...

Each time I get:
=(IF(F2=Frank,H2,0)+IF(J2=Frank,K2,0))*W2

What I really want is:
=(IF(F2="Frank",H2,0)+IF(J2="Frank",K2,0))*W2

Any other thought on this???

--
RyGuy


Rick Rothstein (MVP - VB) said:
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 said:
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--
Click to expand...
Click to expand...
 
R

Rick Rothstein \(MVP - VB\)

If, as you said in one of your other posts, that this...

C.Offset(, 19).Formula = "=(IF(RC[-19]=" & Chng & _
" ,RC[-17],0)+IF(RC[-15]=" & _
Chng & ",RC[-14],0))*RC[-2]"

gives you everything you need except for the quotes around the name, then
change it to this...

C.Offset(, 19).Formula = "=(IF(RC[-19]=""" & Chng & _
""" ,RC[-17],0)+IF(RC[-15]=""" & _
Chng & """,RC[-14],0))*RC[-2]"

Note that I made use of line continuation characters in order to control how
the newsreader word-wrapped the code lines.

Rick


ryguy7272 said:
Thanks for the look Rick. I tried it both ways:
Chng = .Range("F2")

and:
Chng = Range("F2").Value

The results were the same with both methods. So much for that statement
about this being 'simple' in my original post. Eyes rolling now...

Each time I get:
=(IF(F2=Frank,H2,0)+IF(J2=Frank,K2,0))*W2

What I really want is:
=(IF(F2="Frank",H2,0)+IF(J2="Frank",K2,0))*W2

Any other thought on this???

--
RyGuy


Rick Rothstein (MVP - VB) said:
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 said:
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--
Click to expand...
Click to expand...
Click to expand...
 
R

ryguy7272

TRIPLE QUOTES!!!?????


It's going to be a long time before I have MVP after my name, and I don't
think I will have one of those silver circle by name for a while either...

Thanks for everything guys!!
Problem resolved!!

Ryan---
--
RyGuy


Rick Rothstein (MVP - VB) said:
If, as you said in one of your other posts, that this...

C.Offset(, 19).Formula = "=(IF(RC[-19]=" & Chng & _
" ,RC[-17],0)+IF(RC[-15]=" & _
Chng & ",RC[-14],0))*RC[-2]"

gives you everything you need except for the quotes around the name, then
change it to this...

C.Offset(, 19).Formula = "=(IF(RC[-19]=""" & Chng & _
""" ,RC[-17],0)+IF(RC[-15]=""" & _
Chng & """,RC[-14],0))*RC[-2]"

Note that I made use of line continuation characters in order to control how
the newsreader word-wrapped the code lines.

Rick


ryguy7272 said:
Thanks for the look Rick. I tried it both ways:
Chng = .Range("F2")

and:
Chng = Range("F2").Value

The results were the same with both methods. So much for that statement
about this being 'simple' in my original post. Eyes rolling now...

Each time I get:
=(IF(F2=Frank,H2,0)+IF(J2=Frank,K2,0))*W2

What I really want is:
=(IF(F2="Frank",H2,0)+IF(J2="Frank",K2,0))*W2

Any other thought on this???

--
RyGuy


Rick Rothstein (MVP - VB) said:
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


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--
Click to expand...
Click to expand...
Click to expand...
 
R

Rick Rothstein \(MVP - VB\)

Yep, triple quotes... but it not really that hard to see why. Consider
this... **all** String constants (non-variable text) **must** have quote
marks around it. This you already know. For example...

SomeVariable = "I am a String constant"

You have to surround the text with quote marks so VB will not it is a String
constant. Now, to put an actual quote mark symbol inside of a String
constant, you place two of them next to each other. So, consider this String
constant...

SomeVariable = "This symbol "" is a quote mark"

If you execute this statement, SomeVariable will contain this...

This symbol " is a quote mark

I think you already probably knew this. Okay, now, back to this statement...

SomeVariable = "This symbol "" is a quote mark"

Remove the text in front of the double quote marks....

SomeVariable = """ is a quote mark"

Notice there are three quote marks next to each other at the beginning of
the String constant; and notice I have not changed any rule... all I did is
remove some text from a statement you already knew worked. If you execute
this statement, the following is what is assigned to the variable...

" is a quote mark

Similarly, we could have removed the text after the double quote marks
instead. If we did that, this is what would have resulted...

SomeVariable = "This symbol """

and, again, we have three quote marks next to each other. The rule to be
garnered from this is... to have a leading quote mark in a String, the
String will start with three quote marks; to have a trailing quote mark in a
String, the String will end with three quote marks.

Okay, that takes care of your "question"; but let's take this one step
further. Going back to the original assignment, namely....

SomeVariable = "This symbol "" is a quote mark"

This time, instead of removing only the text in front of the double quote
marks, or the text after it, let's remove the text from both sides. Doing
that, we are left with this...

SomeVariable = """"

Notice we have four quote marks next to each other! What prints out? Simply
the " that was in the original text when there was other text around it. We
removed the text and all that was left is what was needed to produce a
single quote mark. That is why you will see four quote marks strung together
in a concatenation in order to produce a single quote mark at the give
location. The outer two quote marks are required because it is a String
constant and the inner two quote marks are how a single quote mark is placed
within a String constant.

Rick



ryguy7272 said:
TRIPLE QUOTES!!!?????


It's going to be a long time before I have MVP after my name, and I don't
think I will have one of those silver circle by name for a while either...

Thanks for everything guys!!
Problem resolved!!

Ryan---
--
RyGuy


Rick Rothstein (MVP - VB) said:
If, as you said in one of your other posts, that this...

C.Offset(, 19).Formula = "=(IF(RC[-19]=" & Chng & _
" ,RC[-17],0)+IF(RC[-15]=" & _
Chng & ",RC[-14],0))*RC[-2]"

gives you everything you need except for the quotes around the name, then
change it to this...

C.Offset(, 19).Formula = "=(IF(RC[-19]=""" & Chng & _
""" ,RC[-17],0)+IF(RC[-15]=""" & _
Chng & """,RC[-14],0))*RC[-2]"

Note that I made use of line continuation characters in order to control
how
the newsreader word-wrapped the code lines.

Rick


ryguy7272 said:
Thanks for the look Rick. I tried it both ways:
Chng = .Range("F2")

and:
Chng = Range("F2").Value

The results were the same with both methods. So much for that
statement
about this being 'simple' in my original post. Eyes rolling now...

Each time I get:
=(IF(F2=Frank,H2,0)+IF(J2=Frank,K2,0))*W2

What I really want is:
=(IF(F2="Frank",H2,0)+IF(J2="Frank",K2,0))*W2

Any other thought on this???

--
RyGuy


:

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


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--
Click to expand...
Click to expand...
Click to expand...
 
R

ryguy7272

I extend a very worthy thanks to you Rick!! I saved your final post!!
Thanks to everyone!!


Regards,
Ryan---

--
RyGuy


Rick Rothstein (MVP - VB) said:
Yep, triple quotes... but it not really that hard to see why. Consider
this... **all** String constants (non-variable text) **must** have quote
marks around it. This you already know. For example...

SomeVariable = "I am a String constant"

You have to surround the text with quote marks so VB will not it is a String
constant. Now, to put an actual quote mark symbol inside of a String
constant, you place two of them next to each other. So, consider this String
constant...

SomeVariable = "This symbol "" is a quote mark"

If you execute this statement, SomeVariable will contain this...

This symbol " is a quote mark

I think you already probably knew this. Okay, now, back to this statement...

SomeVariable = "This symbol "" is a quote mark"

Remove the text in front of the double quote marks....

SomeVariable = """ is a quote mark"

Notice there are three quote marks next to each other at the beginning of
the String constant; and notice I have not changed any rule... all I did is
remove some text from a statement you already knew worked. If you execute
this statement, the following is what is assigned to the variable...

" is a quote mark

Similarly, we could have removed the text after the double quote marks
instead. If we did that, this is what would have resulted...

SomeVariable = "This symbol """

and, again, we have three quote marks next to each other. The rule to be
garnered from this is... to have a leading quote mark in a String, the
String will start with three quote marks; to have a trailing quote mark in a
String, the String will end with three quote marks.

Okay, that takes care of your "question"; but let's take this one step
further. Going back to the original assignment, namely....

SomeVariable = "This symbol "" is a quote mark"

This time, instead of removing only the text in front of the double quote
marks, or the text after it, let's remove the text from both sides. Doing
that, we are left with this...

SomeVariable = """"

Notice we have four quote marks next to each other! What prints out? Simply
the " that was in the original text when there was other text around it. We
removed the text and all that was left is what was needed to produce a
single quote mark. That is why you will see four quote marks strung together
in a concatenation in order to produce a single quote mark at the give
location. The outer two quote marks are required because it is a String
constant and the inner two quote marks are how a single quote mark is placed
within a String constant.

Rick



ryguy7272 said:
TRIPLE QUOTES!!!?????


It's going to be a long time before I have MVP after my name, and I don't
think I will have one of those silver circle by name for a while either...

Thanks for everything guys!!
Problem resolved!!

Ryan---
--
RyGuy


Rick Rothstein (MVP - VB) said:
If, as you said in one of your other posts, that this...

C.Offset(, 19).Formula = "=(IF(RC[-19]=" & Chng & _
" ,RC[-17],0)+IF(RC[-15]=" & _
Chng & ",RC[-14],0))*RC[-2]"

gives you everything you need except for the quotes around the name, then
change it to this...

C.Offset(, 19).Formula = "=(IF(RC[-19]=""" & Chng & _
""" ,RC[-17],0)+IF(RC[-15]=""" & _
Chng & """,RC[-14],0))*RC[-2]"

Note that I made use of line continuation characters in order to control
how
the newsreader word-wrapped the code lines.

Rick


Thanks for the look Rick. I tried it both ways:
Chng = .Range("F2")

and:
Chng = Range("F2").Value

The results were the same with both methods. So much for that
statement
about this being 'simple' in my original post. Eyes rolling now...

Each time I get:
=(IF(F2=Frank,H2,0)+IF(J2=Frank,K2,0))*W2

What I really want is:
=(IF(F2="Frank",H2,0)+IF(J2="Frank",K2,0))*W2

Any other thought on this???

--
RyGuy


:

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


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--
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sum Column Y at first blank-bottom cell. 6
If sh.Name <> Sheet1 Then 3
Inserting the values of a formula using vb 3
assign formula to variant range of cells 5
A simple if <> nn then Msgbox "No Match" 5
Copy Tab Names from Several Sheets and Copy Last Row from Same She 7
macro to copy rows that contain a cell with a certain number 2
Select and Delete 4

Top