0

I am working on a project that will filter our SC sales taxes by tax codes. In order to do this, I have to write some codes to get things corrected e.g. the city names. Seeing that sometimes this information is manually put into our system, the spellings can vary causing my codes to not work properly. So, I have created a new worksheet "CITY FIND REPLACE" where there is a "find" column(A) of incorrect spellings and a "replace" column (B) with the way it needs to be so that my codes work correctly. I need it in a table so that each month I do it and find more/different spellings of the cities, I can add them to the search list so that it will catch and change them.

I am having a hard time getting the code I have found to pull from a different worksheet and I am not sure what I am doing wrong. I want it to search through Column H on Sheet1 (RAW DATA) (the # of rows can change each month as well so I just need it to go through the bottom of the data but that can't be a set #) and then compare each cell to Sheet12 (CITY FIND REPLACE) TABLE 1 column A. If they match, change it to Sheet12 (CITY FIND REPLACE) column B.

Note: VBA coding is not my thing. I fumbled through and used stackoverflow to help me on another project and now my work thinks I am good at this... and I am not. I don't understand it. Any help in plain English would be amazing.

Sub FindReplace()

    For i = 2 To 40
        Worksheets("RAW DATA").Range("H:H").Select
        Selection.Replace what:=Worksheets("CITY FIND REPLACE").Cells(i, 1).Value,
        replacement:=Worksheets("CITY FIND REPLACE").Cells(i, 2).Value, lookat:=xlPart,
        searchorder:=xlByRows, MatchCase:=False

    Next

    Worksheets("RAW DATA").Cells(1, 1).Select

End Sub

I am right now getting

RUN TIME ERROR'1004': SELECT METHOD OF RANGE CLASS FAILED.

When I click debug it will highlight the line Worksheets("RAW DATA").Range("H:H").Select.

cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
Rachel Mears
  • 27
  • 6
  • 1
    Is that sheet active when you run the macro? You should in any case read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – SJR Oct 23 '19 at 13:58
  • Yes sheet 1 (raw data) is active when i try to run the macro but it does not work. I read the link you sent and tried to change it to some of those options and none of them worked either. – Rachel Mears Oct 23 '19 at 14:10

1 Answers1

1

I can't see any reason why this wouldn't work.

Sub FindReplace()

For i = 2 To 40
    Worksheets("RAW DATA").Range("H:H").Replace what:=Worksheets("CITY FIND REPLACE").Cells(i, 1).Value, _
                                   replacement:=Worksheets("CITY FIND REPLACE").Cells(i, 2).Value, lookat:=xlPart, _
                                    searchorder:=xlByRows, MatchCase:=False
Next i

End Sub
SJR
  • 22,986
  • 6
  • 18
  • 26
  • this worked except for one....the town of Mullins (must have the s on the end) if someone manually types Mullin....i would like it to find that as an error and replace with Mullins. it didn't do that one. why? – Rachel Mears Oct 23 '19 at 14:26
  • 1
    So Mullin appears in H and then on your other sheet you'd need Mullin in A and Mullins in B. That should work - is that what you've done? – SJR Oct 23 '19 at 14:28
  • yes. but now it is changing "mullins" in column H to "mullinss" ....but like N Charleston....it changes to North Charleston just fine. Same with West Cola changing to West Columbia. those 2 worked great. – Rachel Mears Oct 23 '19 at 14:34
  • 1
    Ah yes well if you have "Mullins" in H and you're looking up part of the cell it will find "Mullin" (everything except the "s") and replace that with "Mullins" so you end up with an extra "s". Make sense? You'll either need an exact match or use regular expressions, or possibly another way though hard to say without seeing any data. – SJR Oct 23 '19 at 14:36
  • nevermind. i figured it out. I then went back to the CITY FIND REPLACE list and added one at the bottom to MULLINSS in column A and MULLINS in column B so that it would change those back in the same run of the macro and it worked! – Rachel Mears Oct 23 '19 at 14:41