Paste Relative Formula in All Cells in Row Containing "XYZ"

[Tybward]

I have a file that references Bing API lookups through webcalls. When loading the sheet, sporadically, many of the calls work fine, and others throw out an error code and need to be refreshed.

Re-pasting the formula to all cells again causes even some of the cells that were just loaded fine, to bug out and show an error.

What I would like to do is create a VBA Macro that when a button is clicked, VBA copies a single cell reference, and pastes it down a range ONLY to the cells that resulted in an error, in order to refresh them.

I have learned that doing this twice gets rid of all the errors and makes the API calls correct.

I can also add the "iferror" to all of the formulas so that the ones that need to be refreshed can be set to either a particular number or phrase if that makes it simpler.


This is what I have put together so far:


Sub ReplaceErrors()

Dim c As Range
Dim firstAddress As String

With Worksheets(1).Range("A1:A500")
Set c = .Find(ErrorPhrase, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.formula = range("d1").formula
Set c = .FindNext(c)
Loop While Not c Is Nothing
End If
End With

End Sub



The only problem with this is that it pastes the hard coded formula from cell "D1" instead of the relative formula. I have tried to switchi this with a copy/paste, but havn't managed to get it in there correctly.

Any help is much appreciated!

Thanks,

-Ty

 

[Tybward]

I have a file that references Bing API lookups through webcalls. When loading the sheet, sporadically, many of the calls work fine, and others throw out an error code and need to be refreshed.

Re-pasting the formula to all cells again causes even some of the cells that were just loaded fine, to bug out and show an error.

What I would like to do is create a VBA Macro that when a button is clicked, VBA copies a single cell reference, and pastes it down a range ONLY to the cells that resulted in an error, in order to refresh them.

I have learned that doing this twice gets rid of all the errors and makes the API calls correct.

I can also add the "iferror" to all of the formulas so that the ones that need to be refreshed can be set to either a particular number or phrase if that makes it simpler.


This is what I have put together so far:


Sub ReplaceErrors()

Dim c As Range
Dim firstAddress As String

With Worksheets(1).Range("A1:A500")
Set c = .Find(ErrorPhrase, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.formula = range("d1").formula
Set c = .FindNext(c)
Loop While Not c Is Nothing
End If
End With

End Sub



The only problem with this is that it pastes the hard coded formula from cell "D1" instead of the relative formula. I have tried to switchi this with a copy/paste, but havn't managed to get it in there correctly.

Any help is much appreciated!

Thanks,

-Ty

I have just realized that this vba is a loop, and is actually very time consuming to go through each of the cells individually and update them. If there was any to have the VBA code select all cells containing the errors and paste the reletive formula in them at the same time, that would be much better. It would be the same thing as the F5 selecting all cells with errors and pasting a formula in them.