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

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

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

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