Search & Replace

J

John Keturi

Trying to get this to work. I want the macro to search a row for a certain
value, then search the column for a certain value, then return a certain
value into the corresponding cell. Thanks
 
N

Norman Jones

Hi John,

Apparently pertinent to your request, Patrick Malloy recently posted the
following:
----------------------------------------------------------
Option Explicit
Sub Test()
FindAndReplace "Marty", "CA", _
Range("MyData"), "New Stuff"

End Sub

Private Sub FindAndReplace(sCol As String, _
sRow As String, _
rTable As Range, _
sNewValue As String)
Dim iCOL As Long
Dim iROW As Long

On Error Resume Next

iCOL = Application.WorksheetFunction.Match( _
sCol, rTable.Rows(1), False)
iROW = Application.WorksheetFunction.Match( _
sRow, rTable.Columns(1), False)

If iCOL * iROW = 0 Then
MsgBox "Not Found"
Else
rTable.Cells(iROW, iCOL) = sNewValue
End If
on error goto 0
End Sub

Patrick Molloy
Microsoft Excel MVP
 
Top