Search Row & Column

[John Keturi]

I've written some code to Search Row 1 for the word "Marty", then to search
down Column A for the word "CA", then to replace the correlating Cell
contents with "OK". It seems to search and run, but it does not replace the
Cell.Value="OK". Any suggestions?

 

[Kieran]

I is difficult to comment without the code.

Could you post the relevant portions for review

 

[Patrick Molloy]

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