Changing search feild in middle of seach

[Tina via OfficeKB.com]

I'm kind of new with writing these macros but I've come across a problem I'm
not sure what to do, what my macro is doing so far is I have one spreadsheet
and it searches for invoices in spreadsheetA on spreadsheets 1 through 9.
Once it finds the invoice number it will copy the entire line and paste it to
spreadsheetA, my problem is some of the invoice numbers have letters infront
(YM, WM, XM) and the invoices numbers in 1 - 9 have CMM infront. ex in
spreadsheetA WM123 will be CMM123 in spreadsheet 1 through 9. Is there a way
to have it search through spreadsheet and when it finds WM123 look for CMM123
instead of WM123?

please help!!

 

[Guest]

Instead of using

if Number = 123 then ... try

If Number like "*123" then

IF you put an asterisk at the beginning, it will return anything that ends
with 123. If you use "*123* it will return anything that contains 123 in
that order.

 

[Incidental]

Hi there

I have to say i'm not entirely sure what you are trying to do with out
seeing an example of your code but you could check to make sure that
you are looking for xlWhole in the LookAt option find. When you
record a macro in excel using find it will automatically set the
LookAt option to xlPart. See the amended macro that i recorded

Cells.Find(What:="cmm123", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

This might be of some help to you if not you would have to paste some
of your code so i could have a look

S

 

[Tina via OfficeKB.com]

Well I haven't written the code I didn't realize that my current code
wouldn't pick up these numbers.

Sub Zurnprt2()

Const SummaryWorkbook = "ZurnOpenItemsspreadsheetXX.xls"
Const MainInvoiceCol = 5
Const MainPasteCol = 14
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

 

[Hinojosa via OfficeKB.com]

So with my current code would I be able to fit something like this in there?

Instead of using

if Number = 123 then ... try

If Number like "*123" then

IF you put an asterisk at the beginning, it will return anything that ends
with 123. If you use "*123* it will return anything that contains 123 in
that order.

I'm kind of new with writing these macros but I've come across a problem I'm
not sure what to do, what my macro is doing so far is I have one spreadsheet

[quoted text clipped - 7 lines]

please help!!

 

[Incidental]

Hi there

I think I have a better idea of what your problem is now. The invoice
numbers in column A of the summery workbook will contain a number like
YM456789 and you want to search for just the number portion of that
reference 456789 with the prefix CMM in front of it like CMM456789 in
the other nine spreadsheets???

If this is the case you can pass the cell reference you want to search
for to a string and then remove the characters you don't want then
search for the remained with the added prefix of CMM.



Sub Zurnprt2()
Const SummaryWorkbook = "ZurnOpenItemsspreadsheetXX.xls"
Const MainInvoiceCol = 5
Const MainPasteCol = 14
Const WbkInvoiceCol = 5
Const WbkStartCol = 1
Const WbkEndCol = 14
'**************** added line below
Dim i As Integer ' IT IS A GOOD IDEA TO ALWAYS DECLARE YOUR VARIABLES

'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
'**************** added 2 lines below
i = Len(InvoiceNumber) 'count the characters in the string
i = i - 2 'Remove 2 from the count to accommodate YM, WM or XM
InvoiceNumber = Right(InvoiceNumber, i) 'counting back from the right
'select only the numbers

'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
'**************** amended line below
If (cell2.Value = "CMM" & InvoiceNumber) Then 'add the
prefix and the number here
'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

End Sub


if you want to test this see if it helps you out, if you still have
trouble reply and i will see what i can think of

S

 

[Tina via OfficeKB.com]

Hey that worked great with the credits!! But it also cut two numbers from
the invoices...is there a way to only run the i=i-2 only if it finds a letter?

 

[Incidental]

Hi again

the code below should sort out your problem

Sub Zurnprt2()
Const SummaryWorkbook = "ZurnOpenItemsspreadsheetXX.xls"
Const MainInvoiceCol = 5
Const MainPasteCol = 14
Const WbkInvoiceCol = 5
Const WbkStartCol = 1
Const WbkEndCol = 14
Dim i As Integer ' IT IS A GOOD IDEA TO ALWAYS DECLARE YOUR VARIABLES
'**************** added line below **********************************
Dim MyChk As Boolean

'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
'**************** code amended here **********************************
MyChk = IsNumeric(InvoiceNumber) 'returns true if invoicenumber is all
numeric
If MyChk = True Then
GoTo line1: 'skip the next step and continue with the rest of the code
Else 'remove the first two characters
i = Len(InvoiceNumber) 'count the characters in the string
i = i - 2 'Remove 2 from the count to accommodate YM, WM or XM
InvoiceNumber = Right(InvoiceNumber, i) 'counting back from the right
End If 'select only the numbers
line1:
'**************** code amended here **********************************
'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 (cell2.Value = "CMM" & InvoiceNumber) Then 'add the
'prefix and the number here
'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


End Sub

I hope this gets you up and running

good luck

Steve

 

[Tina via OfficeKB.com]

hey there the process that you did below worked but how would I change that
so that instead of taking two off spreadsheet 1 to compensate for YM WM. to
take off two in spreadsheets 2 -9 ?