PDA

View Full Version : [SOLVED] Macro for Find & Replace



cLYDE
05-26-2005, 12:23 AM
How do you automate the find and replace in a Macro ? I am trying to tell the macro to pick up " Find - Letter say C " from a cell within the worksheet and " Replace It " with - Letter say D " from a cell with in the worksheet. I need to change the column that the macro collects data depending on the week of the year. Any Help greatly appreciated. I have just completed a 2day VBA EXCEL course.

Bob Phillips
05-26-2005, 01:33 AM
Here is a simple example



Sub Test()
Dim iLastRow As Long
Dim iWeek As Long
Dim rng As Range
Dim cell As Range
iWeek = ISOWeeknum(Date)
iLastRow = Cells(Rows.Count, iWeek).End(xlUp).Row
Set rng = Cells(1, iWeek).Resize(iLastRow)
Set cell = rng.Find("C")
If Not cell Is Nothing Then
MsgBox "Found at " & cell.Address
Else
MsgBox "Not found"
End If
End Sub

Public Function IsoWeekNum2(d1 As Date) As Integer
' Daniel Maher
Dim d2 As Long
d2 = DateSerial(Year(d1 - Weekday(d1 - 1) + 4), 1, 3)
IsoWeekNum2 = Int((d1 - d2 + Weekday(d2) + 5) / 7)
End Function

johnske
05-26-2005, 02:51 AM
Hi cLYDE,

You can also try this (it both finds & replaces)

Sub ReplaceIt()
'set your own range below
Range("A1:20").Replace _
What:="C", Replacement:="D", _
SearchOrder:=xlByColumns, _
MatchCase:=True
End Sub

Regards,
John

cLYDE
05-26-2005, 03:04 AM
Hi John,

That is great. How do I get What "c" to be picked up from a cell within the spreadsheet.
Say cell A1 contains the value to find and A2 CONTAINS THE VALUE TO REPLACE with.

Sorry for all the questions.

johnske
05-26-2005, 03:16 AM
Sub ReplaceIt()
'set your own range below
Range("B1:D20").Replace _
What:=[A1], Replacement:=[A2], _
SearchOrder:=xlByColumns, _
MatchCase:=True
End Sub
[A1] is shortcut notation for Range("A1"). (You can also use 'Value' if you prefer, but value is the default so it's not required in this case)

HTH,
John

cLYDE
05-26-2005, 03:45 AM
Hi John,

It keeps comming up with after replace " Not Optional"

Here is the code


Sub Replaceit()
'
' Replacement_targets Macro
' Macro recorded 26/05/2005 by dicksonc
'
' Keyboard Shortcut: Ctrl+r

Range("c6:i51").Replace
what = [b53]
replacement = [b56]
searchorder = XLbycolums
MatchCase = True

johnske
05-26-2005, 03:56 AM
Hi, yes, that's because you've removed the commas, colons and underscores. The whole thing is a 'one-liner', the underscores simply put it onto several lines to make it more readable. Try this


Sub Replaceit()
' Replacement_targets Macro
' Macro recorded 26/05/2005 by dicksonc
' Keyboard Shortcut: Ctrl+r
Range("c6:i51").Replace _
what:=[b53], _
replacement:=[b56], _
searchorder:=XLbycolums, _
MatchCase:=True
End Sub

This is the same as (but you'll have to scroll right a lot now):


Sub Replaceit()
' Replacement_targets Macro
' Macro recorded 26/05/2005 by dicksonc
' Keyboard Shortcut: Ctrl+r
Range("c6:i51").Replace what:=[b53], replacement:=[b56], searchorder:=XLbycolums, MatchCase:=True
End Sub

Regards,
John

cLYDE
05-26-2005, 04:09 AM
John,

What should the replacement read ??

Tks,

Clyde

cLYDE
05-26-2005, 04:10 AM
Sorry What should the replacementformat read

johnske
05-26-2005, 04:20 AM
Sorry What should the replacementformat read

?? Not sure what you mean here ?? As your code is now, it's looking through the values in all the cells in the range c6 to i51 for the value that's contained in the cell b53 and (if it's found) replacing it with the value contained in cell b56.

John :)

cLYDE
05-26-2005, 04:20 AM
Range("c6:i51").Replace [b53], [b56], XLbycolums, True this does not run what I am I doing wrong. Sorry to be a pain

cLYDE
05-26-2005, 04:25 AM
it comes up with Run-time error '9'
subscript out of range

johnske
05-26-2005, 04:30 AM
Here it is in full, don't change anything, just copy and paste into your code module and see if it runs (ps: I just found an 'n' missing from xlByColumns in the code you pasted before)


Sub Replaceit1()
Application.ScreenUpdating = False
Range("c6:i51").Replace _
what:=Range("b53").Value, _
replacement:=Range("b56").Value, _
searchorder:=xlByColumns, _
MatchCase:=True
End Sub

cLYDE
05-26-2005, 05:10 AM
Hi John,

It works !!!!!!!!! Great Thank you so much for your help and understanding.

Rgs,

Clyde

johnske
05-26-2005, 05:33 AM
Hi Clyde,

Glad to be able to help. So can you mark this solved now?

Regards,
John :)

cLYDE
05-26-2005, 05:38 AM
Hi John,

Just a thought. Will the macro all work on that one sheet with in the workbook or am I at risk that it will preform the macro on all the sheets ??

Rgs,

Clyde

johnske
05-26-2005, 05:44 AM
Hi John,

Just a thought. Will the macro all work on that one sheet with in the workbook or am I at risk that it will preform the macro on all the sheets ??

Rgs,

Clyde


No risk. It will only works with the active sheet (the one that you can see). It will only work on the other sheets when they are the active (visible) sheet :thumb

Regards,
John