I'm having an issue with my Macro I have had some help so far but it seems to
be getting more complicated, what it does so far it will look for an invoice
number on spreadsheet A and the invoice number will be on spreadsheet 1 - 9,
now where the problem lies is mixed in with those invoice numbers also are
Credit Memos these start with either W,YM,CR,or CS. these are the way they
are sent to us, now in our system they are entered either CM or CMM.

The person that helped me had brillant idea to do something along the lines
of i=i -2 (see marco below) to take away the YM, and replace it with CMM
which is Brillant!! but where the problem:
1. I need it to search for CMM and CM
2. the i = i -2 is also taking away 2 numbers from the invoices and
pulling in results that aren't
correct (ex 1234 the macro will search for 34)

Here is what I need YM1234 I need it to search for CM1234 and CMM1234.

My idea (which I'm not sure how to do or if it's possible) is to do something
along the lines when it search through column E (which is where the
invoices/Creditmemos are) if it finds a letter move to the right one until if
finds a number then replace the letters with CMM and search through workbooks
1 - 9, if it doesn't find a match then try CM and search through workbooks 1 -
9. If no match is found then move to the next line. BUT if it finds just
numbers search for a match... is this possible?? heres what i have so far...

Sub Zurnprt3()
Const SummaryWorkbook = "ZurnOpenItemsspreadsheet.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 - 1 '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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200703/1