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

What is the difference between W,YM,CR,or CS? Are you changing all of these
to CMM? If so then do a find and replace. Are all of your invoice numbers the
same length? If so you can do a search based on the RIGHT(x digits). I just
don't have a clear understanding of what you look for and under what
circumstances.
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"Tina via OfficeKB.com" wrote:

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

Our vendor send us credit memos that begin with either W, YM,CR or CS I'm
guessing for different material they send have different beginnings. But we
when enter these credit memos in our system they are entered with either CM
or CMM. I wish all of the invoice numbers were the same length but they
aren't this again depends on what the vendor sends us. I thought about doing
a find and replace, but wouldn't work because I'm not sure if YM123 is going
to be CM123 or CMM123 I mean I could research it but that would defeat the
purpose of the macro.


John Bundy wrote:
What is the difference between W,YM,CR,or CS? Are you changing all of these
to CMM? If so then do a find and replace. Are all of your invoice numbers the
same length? If so you can do a search based on the RIGHT(x digits). I just
don't have a clear understanding of what you look for and under what
circumstances.
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
[quoted text clipped - 77 lines]
Next cell1
End Sub

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