Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search
Hi it's me again!!
I had a quick question does anybody have any suggestions on how to run a macro that does the following: I need it to search for 123 (which is in wrkbk 1) but when it searches for it it needs to add a AB123 in front and there are about 47000 numbers to reasearch. Any Ideas? ex. Wrkbk1 Wrkbk2 wrkbk3 wrkbk4 wrkbk5 wrkbk6 wrkbk 7 123 - - - - AB123 - so take it found the match take coulmn a - o and place in wrkbk 1 in column o -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200704/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search
I found this code in the VBA help and made a small change. Not sure if you
are looking for 123 as a number or a string. With Worksheets(1).Cells Set c = .Find(123, LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.Value = "abc123" Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With End Sub "Tina via OfficeKB.com" wrote: Hi it's me again!! I had a quick question does anybody have any suggestions on how to run a macro that does the following: I need it to search for 123 (which is in wrkbk 1) but when it searches for it it needs to add a AB123 in front and there are about 47000 numbers to reasearch. Any Ideas? ex. Wrkbk1 Wrkbk2 wrkbk3 wrkbk4 wrkbk5 wrkbk6 wrkbk 7 123 - - - - AB123 - so take it found the match take coulmn a - o and place in wrkbk 1 in column o -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200704/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search
one way:
Sub test() Dim rngfound As Range Dim i As Long Dim firstaddress As String For i = 1 To Worksheets.Count With Worksheets(i).Range("A1:m500") ' Set rngfound = .Find(What:="123", LookIn:=xlValues, _ lookat:=xlPart) If Not rngfound Is Nothing Then firstaddress = rngfound.Address Do Debug.Print rngfound.Address MsgBox "found at " & Worksheets(i).Name & " " & _ rngfound.Address Set rngfound = .FindNext(rngfound) Loop Until rngfound.Address = firstaddress End If End With Next End Sub -- Gary "Tina via OfficeKB.com" <u27679@uwe wrote in message news:70358f9c96413@uwe... Hi it's me again!! I had a quick question does anybody have any suggestions on how to run a macro that does the following: I need it to search for 123 (which is in wrkbk 1) but when it searches for it it needs to add a AB123 in front and there are about 47000 numbers to reasearch. Any Ideas? ex. Wrkbk1 Wrkbk2 wrkbk3 wrkbk4 wrkbk5 wrkbk6 wrkbk 7 123 - - - - AB123 - so take it found the match take coulmn a - o and place in wrkbk 1 in column o -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200704/1 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search
Would still being doing this?
Sub AoSmithprt2() Const Summaryworkbook = "AOSMITHOpenItemsSpUPDATE.xls" Const MainInvoiceCol = 7 Const MainPasteCol = 20 Const wbkInvoiceCol = 5 Const wbkStartCol = 1 Const wbkEndCol = 14 'this is the first workbooks that has the invoice nubers in column A Set wsh1 = Workbooks(Summaryworkbook).Worksheets(1) 'the code below sets InvoiceRange to contain all the Invoice Numbers 'In column A wsh1.Activate Lastrow = wsh1.Cells(Rows.Count, MainInvoiceCol).End(xlUp).Row Set InvoiceRange = wsh1. _ Range(Cells(1, MainInvoiceCol), Cells(Lastrow, MainInvoiceCol)) 'Now we loop though each of the Invoice Numbers in the 1st workbook For Each cell1 In InvoiceRange InvoiceNumber = cell1.Value 'Now Loop through all the open workbooks For Each wbk1 In Application.Workbooks 'skip the 1st workbook If StrComp(wbk1.Name, Summaryworkbook) < 0 Then With wbk1.Worksheets(1) .Activate 'sets InvoiceRange2 to contain the invoicenumbers in 'column E which is the 10th column Lastrow = .Cells(Rows.Count, wbkInvoiceCol).End(xlUp).Row Set InvoiceRange2 = _ .Range(Cells(1, wbkInvoiceCol), Cells(Lastrow, wbkInvoiceCol)) 'Now loop through all the Invoice Number checking again 'Invoice Number found in 1st workbook For Each Cell2 In InvoiceRange2 'Compare Invoice Numbers If (InvoiceNumber = Cell2.Value) Then 'copy Cells if the Invoice Number matches .Range(Cells(Cell2.Row, wbkStartCol), _ Cells(Cell2.Row, wbkEndCol)).Copy _ Destination:=wsh1.Cells(cell1.Row, MainPasteCol) End If Next Cell2 End With End If Next wbk1 Next cell1 End Sub Gary Keramidas wrote: one way: Sub test() Dim rngfound As Range Dim i As Long Dim firstaddress As String For i = 1 To Worksheets.Count With Worksheets(i).Range("A1:m500") ' Set rngfound = .Find(What:="123", LookIn:=xlValues, _ lookat:=xlPart) If Not rngfound Is Nothing Then firstaddress = rngfound.Address Do Debug.Print rngfound.Address MsgBox "found at " & Worksheets(i).Name & " " & _ rngfound.Address Set rngfound = .FindNext(rngfound) Loop Until rngfound.Address = firstaddress End If End With Next End Sub Hi it's me again!! I had a quick question does anybody have any suggestions on how to run a [quoted text clipped - 9 lines] so take it found the match take coulmn a - o and place in wrkbk 1 in column o -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Functions (search within search result) reply to this please | Excel Worksheet Functions | |||
Search lastname + firstname (search on uppercase) | Excel Programming | |||
How do I search excel spreadsheets using multiple search criteria. | Excel Worksheet Functions | |||
I cant do a search on this forum. Everytime I search, it comes up with zero results | Excel Programming | |||
Create a search Field within a worksheet to search command buttons | Excel Programming |