Need help with VBA and Find and Replace Function - Can't get it to run

[rip_vw32]

Hello, I am hoping someone can help me with this....


I am trying to do create a macro to substitute part of a formula in column 'S', cells '1-254' on the worksheet “Original_Formula_Sheet" (so the range is 'S1:S254'). The formula essentially has a $503 absolute variable in it, and I need to replace it with a $753 in row 1. I have 254 rows to go through where the initial absolute variable needed to be replaced, but the value to be replaced increments with each subsequent line.


So what I am trying to do is list out all the variables to be changed and list out what they need to be changed to (eg: $503 to $753, $504 to $754,, etc...) on the worksheet "Values", columns 'A' and 'B', rows '1-254' (so the range is 'A1:B254')


When I have a macro built and try to run it I get a "Runtime Error '9': Subscript out of range"


Here is how I am trying to build the macro:

Sub Replace()


	Dim myList, myRange


	Set myList = Worksheets(“Values”).Range("A1:B254")
    
	Set myRange = Worksheets(“Original_Formula_Sheet”).Range("S4:S254")


	For Each cel In myList.Columns(1).Cells


	myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 1).Value, LookAt:=xlWhole


	Next cel


End Sub

The error, when debugging, highlights the “Set myList = Worksheets(“Values”).Range(“A1:B254”)”.


Is there something that I am missing here... I am running Excel 2013

 

[igold]

Hi,

I have not tested your code with any data, but I did copy it into a worksheet.

The code did throw an error on the line that you stated. But I think your error is something minor in your use of the quotes that surround your sheet names.

Try this:

Sub Replace()

    Dim myList, myRange

    Set myList = Worksheets("Values").Range("A1:B254")
    Set myRange = Worksheets("Original_Formula_Sheet").Range("S4:S254")

    For Each cel In myList.Columns(1).Cells
        myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 1).Value, LookAt:=xlWhole
    Next cel
    
End Sub

HTH

igold

 

[rip_vw32]

That is awesome...... it actually ran once I replaced the quotes.... thank you so much for taking a look at this. I truly appreciate it!

 

[igold]

Glad you got it going. Thanks for the feedback.