Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Problem
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 -- Regards, Tom Ogilvy "David W" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data search problem | Excel Discussion (Misc queries) | |||
having a problem with IF and SEARCH | Excel Discussion (Misc queries) | |||
Xl search problem | Excel Discussion (Misc queries) | |||
Problem with search | Excel Worksheet Functions | |||
Problem with search | Excel Worksheet Functions |