Search Problem

  • Thread starter Thread starter David W
  • Start date Start date
D

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
 
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
 
Back
Top