Excel Find Replace from different WorkSheet 1

[waubain]

I am trying to Find and Replace values in a column from a different Worksheet within the same Workbook. If I have the command button on the same worksheet as the data then this works.

Sub cmdFindReplaceLocation_Click()
 
    Columns("C:C").Select
    Selection.Replace What:="5WS*", Replacement:="5WS", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

End Sub

I really want the command button on a different worksheet from the data. I have tried multiple attempts but still get a Run-time error '1004': Select method or Range class failed on the line "Columns("C:C").Select
I am guessing I am not seting the range correctly

Sub cmdFindReplaceLocation_Click()
'
' Recorded macro
'

    Sheets("STEWARDSHIP").Select
    Columns("C:C").Select
    Selection.Replace What:="IC/4WMICU*", Replacement:="4WMICU", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

End Sub

I have also tried variations along this line and fails at the same line:

Sub cmdFindReplaceLocation_Click()

    Dim wks as Worksheet
    
    Set wks = Worksheets("STEWARDSHIP")
    With wks
       Columns("C:C").Select
       Selection.Replace What:="IC/4WMICU*", Replacement:="4WMICU", LookAt:=xlPart, _
       SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
       ReplaceFormat:=False
    End With
End Sub

Thank you for any help.



You don't know what you don't know...

 

[SkipVought]

hi,

Your WITH block will do NOTHING without [highlight]referencing[/highlight] the WITH statement OBJECT.....

Sub cmdFindReplaceLocation_Click()
    
    With Worksheets("STEWARDSHIP")
       with [highlight].[/highlight]Columns("C:C")
          [highlight].[/highlight]Replace What:="IC/4WMICU*", Replacement:="4WMICU", LookAt:=xlPart, _
           SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
           ReplaceFormat:=False
       end with
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[waubain]

Skip,
Works perfectly. I did try .Column, but not using With in front. I understand what you are saying, but would have never come up with that on my own.
Thank you.


You don't know what you don't know...

 

[SkipVought]

It really could have been done like this as well...

Sub cmdFindReplaceLocation_Click()
    
    With Worksheets("STEWARDSHIP")
       .Columns("C:C").Replace What:="IC/4WMICU*", Replacement:="4WMICU", LookAt:=xlPart, _
           SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
           ReplaceFormat:=False
    End With
End Sub

BTW, there was really no good reason to SET the wks object. Hence the Worksheet Object is used directly in the With statement. If the Worksheet Object were referenced multiple times, then it makes sense.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

With just one object you don't need With at all:

Sub cmdFindReplaceLocation_Click()
    
Worksheets("STEWARDSHIP").Columns("C:C").Replace What:="IC/4WMICU*", _      Replacement:="4WMICU", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Sub

Have fun.

---- Andy

 

[SkipVought]

Yes. It's my myopia Opie!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[waubain]

There is not just 1 find and replace, there are actually 39 different find and replace statements. I simplified the example for the sake of brievity, so what Skip wrote the first time worked best in my case. Part B: it would seem more efficient use 2 arrays to loop through. One array for the Find and one for the Replace. This will be a headache on a different day. For right now it is functional and that was today's goal.

Thank you for everyone's advice. I am a hospital pharmacist. With the help I have received on this forum learning VBA, my boss has created a new position for me with a significant portion being devoted to automation of MS Office applications within our department. The side effect is the word is getting out to other departments.



You don't know what you don't know...