B
Brian
Tom Ogilvy kindly helps me with the code to evalute for a report. I now have
a new related problem.
In the following code, the first case statement works, but when I select the
second, if fails with runtime error 424. In debug, it highlights the line
"Set rng = Evaluate(s)" for the second case. The definition popup states
"Cannot jump to "Evaluate" because it is hidden. It actually captures the
first c and places in the Report sheet.
Do I need to reset something? Am complete novice in excel VBA.
Select Case ComboBox1.Value
Case "GSOP_0286"
Set refrange = Sheets("Sheet2").Range("A3:A20")
i = 0
For Each c In refrange
If c.Value = "" Then
End
Else
s = Replace(c.Formula, "=", "")
Set rng = Evaluate(s)
rng.EntireRow.Copy
Sheets("Report").Range("A4") _
.Offset(i, 0) _
.PasteSpecial Paste:= _
xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
i = i + 1
End If
Next c
Case "GSOP_0287"
Set refrange = Sheets("Sheet2").Range("B3:B20")
i = 0
For Each c In refrange
If c.Value = "" Then
End
Else
s = Replace(c.Formula, "=", "")
Set rng = Evaluate(s)
rng.EntireRow.Copy
Sheets("Report").Range("A4") _
.Offset(i, 0) _
.PasteSpecial Paste:= _
xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
i = i + 1
End If
Next c
Thanks,
a new related problem.
In the following code, the first case statement works, but when I select the
second, if fails with runtime error 424. In debug, it highlights the line
"Set rng = Evaluate(s)" for the second case. The definition popup states
"Cannot jump to "Evaluate" because it is hidden. It actually captures the
first c and places in the Report sheet.
Do I need to reset something? Am complete novice in excel VBA.
Select Case ComboBox1.Value
Case "GSOP_0286"
Set refrange = Sheets("Sheet2").Range("A3:A20")
i = 0
For Each c In refrange
If c.Value = "" Then
End
Else
s = Replace(c.Formula, "=", "")
Set rng = Evaluate(s)
rng.EntireRow.Copy
Sheets("Report").Range("A4") _
.Offset(i, 0) _
.PasteSpecial Paste:= _
xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
i = i + 1
End If
Next c
Case "GSOP_0287"
Set refrange = Sheets("Sheet2").Range("B3:B20")
i = 0
For Each c In refrange
If c.Value = "" Then
End
Else
s = Replace(c.Formula, "=", "")
Set rng = Evaluate(s)
rng.EntireRow.Copy
Sheets("Report").Range("A4") _
.Offset(i, 0) _
.PasteSpecial Paste:= _
xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
i = i + 1
End If
Next c
Thanks,