Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing search feild in middle of seach
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!! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200703/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing search feild in middle of seach
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. "Tina via OfficeKB.com" wrote: 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!! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200703/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing search feild in middle of seach
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing search feild in middle of seach
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 Incidental wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200703/1 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing search feild in middle of seach
So with my current code would I be able to fit something like this in there?
Barb Reinhardt wrote: 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!! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200703/1 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing search feild in middle of seach
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing search feild in middle of seach
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 wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200703/1 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing search feild in middle of seach
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 ? Incidental wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200704/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Feild Searcher | Excel Discussion (Misc queries) | |||
Changing page orientation in middle of spreadsheet | Excel Discussion (Misc queries) | |||
Extract text in middle using Mid and Find or Search | Excel Discussion (Misc queries) | |||
Changing Feild Color via value | Excel Programming | |||
Want to create a search gui on wkst1 to seach data on other wksts | Excel Programming |