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

Tybward

New Member
Joined
Jul 22, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Tybward

New Member
Joined
Jul 22, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,060
Messages
5,704,041
Members
421,323
Latest member
Exidous

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top