Search Problem

[David W]

Got this to work on the active sheet but I cant get it to work on the rest,
any ideals?
Private Sub CommandButton19_Click()
Dim MyValue
Dim NewValue
Dim wksh As Worksheet
MyValue = InputBox("Enter search value", "Search Value",
Sheets("setup").[a11])
NewValue = InputBox("Enter replace value", "Replace Value")
If NewValue <> False Then
Range("b11") = NewValue
End If
For i = 1 To 12
SName = Choose(i, "jan", "feb", "march", "april", "may", "june", "july",
"aug", "sept", "oct", "nov", "dec")
Set wksh = Worksheets(SName)
Search.Range("n10:n170").Value
If Cells.Value = MyValue Then
cell.Value = Range("b11").Value
End If
Next

End Sub

what it is doing is looking at a cells value and then it looks for that
value in the other sheets and changes that value to the new value
Still Learning
David

 

[Tom Ogilvy]

You actually don't do any searching in your current code:

Private Sub CommandButton19_Click()
Dim MyValue
Dim NewValue
Dim wksh As Worksheet
MyValue = InputBox("Enter search value", "Search Value",
Sheets("setup").[a11])
NewValue = InputBox("Enter replace value", "Replace Value")
If NewValue <> False Then
Sheets("Setup").Range("b11") = NewValue
End If
For i = 1 To 12
SName = Choose(i, "jan", "feb", "march", "april", "may", _
"june", "july", "aug", "sept", "oct", "nov", "dec")
Set wksh = Worksheets(SName)
' the next line should raise an error so comment it out
'Search.Range("n10:n170").Value
for each cell in wksh.Range("N10:N170")
If Cell.Value = MyValue Then
cell.Value = NewValue
' or
'cell.Value = Sheets("Setup").Range("b11").Value
End If
Next cell
Next i
End Sub

You still may have a problem because 123 <> "123"

It is unclear what you search and replace values should be and what is
stored in the cells. You newvalue and myvalue are going to be strings -
since that is what a VBA inputbox returns. If the user will be typing in a
number, you might need to convert them

if isnumeric(myValue) then
myvalue = cdbl(myValue)
end if

if isnumeric(NewValue) then
newValue = cdbl(NewValue)
End if