Pulling formulas from cells into VBA for modification

[bmckenna]

Hey all,

This is my first post here, and hopefully the first of many more.

Anyways, I have about 40 or so cells that need their cell formula altered, so I pull in the formula, which works, modify it, which works, but when I try to output it to the sheet, no go. I tried dim as string/variant/double, but nothing. I keep getting the 1004 Application-Defined or Object-Defined error. Here's the code:

Sub EditFormula()
Dim x As String
For i = 7 To 49
With ThisWorkbook.Sheets("INTERFACE2")
x = .Cells(i, 2).Formula
.Cells(i, 3) = "=iferror(" + CStr(x) + ", rr)"
End With
Next i
End Sub

Thanks!

 

[pavin]

I assume that your formula "iferror" is a Function;

Try this:

Sub EditFormula()
Dim x As String
For i = 7 To 49
With ThisWorkbook.Sheets("INTERFACE2")
x = .Cells(i, 2).Formula
.Cells(i, 3) = "=iferror(" & CStr(x) & ", rr)"
End With
Next i
End Sub


I guess you meant this formula:

"IF(ISERROR......" If so then use the following

Sub EditFormula()
Dim x As String
For i = 7 To 49
With ThisWorkbook.Sheets("INTERFACE2")
x = .Cells(i, 2).Formula
.Cells(i, 3) = "=IF(ISERROR(" & CStr(x) & "), rr)"
End With
Next i
End Sub

 

[bmckenna]

No go, same error, I did this below:

Sub EditFormula()
Dim x As String
For i = 7 To 49
With ThisWorkbook.Sheets("INTERFACE2")
x = .Cells(i, 2).Formula
y = "=iferror(" & CStr(x) & ", rr)"
Stop
.Cells(i, 3) = y
End With
Next i
End Sub

I look at my locals window when it hits the Stop, and my y is equal to:

: y : "=iferror(=INDIRECT("Data!A"&B$6), rr)" : Variant/String

 

[VoG]

What is rr - a named range?

You'll need to lose the = before INDIRECT

y = "=iferror(" & Right(x, Len(x) - 1) & ", rr)"

 

[bmckenna]

Right you are Peter, any ideas how to pull in that formula without that equals sign before the indirect? Also, the rr will eventually be "", but I will find and replace that later, since VBA already uses the " for itself.

 

[VoG]

See the code I posted.

 

[pavin]

Try this:

Sub EditFormula()
Dim x As String
For i = 7 To 49
With ThisWorkbook.Sheets("INTERFACE2")
x = .Cells(i, 2).Formula
x = Mid(x, 2)
y = "=iferror(" & CStr(x) & ", ""rr"")"
Stop
.Cells(i, 3) = y
End With
Next i
End Sub

 

[bmckenna]

Whoop, embarrassing....thanks again.