Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too Many Line continuations?? VB Macro
I have a 10,000 line worksheet that I have constructed a macro for an it works great. I had another request to create another one lookin for different data. My original had this as a query component (lookin for sales reps): Dim i As Long Dim iStart As Long Dim iEnd As Long iStart = 1 iEnd = Range("B65536").End(xlUp).Row For i = iStart To iEnd If Range("B" & i).Value < "Claude Mossian" And _ Range("B" & i).Value < "Craig Kowal" And _ Range("B" & i).Value < "Dave Wester" And _ Range("B" & i).Value < "David Stone" And _ Range("B" & i).Value < "Elaine Ober" And _ Range("B" & i).Value < "House Account INDiana" And _ Range("B" & i).Value < "House Account Indiana Lab" And _ Range("B" & i).Value < "Jill Campbell" And _ Range("B" & i).Value < "Lana Compo" And _ Range("B" & i).Value < "Steve Horvath" Then Range("B" & i).Value = "" End If Next the new data criteria are numbers, so I modified the old macro an entered it like this: Dim i As Long Dim iStart As Long Dim iEnd As Long iStart = 1 iEnd = Range("C65536").End(xlUp).Row For i = iStart To iEnd If Range("C" & i).Value < "21012" And _ Range("C" & i).Value < "21056" And _ Range("C" & i).Value < "22053" And _ Range("C" & i).Value < "23801" And _ Range("C" & i).Value < "24028" And _ Range("C" & i).Value < "24087" And _ Range("C" & i).Value < "24091" And _ Range("C" & i).Value < "24095" And _ Range("C" & i).Value < "24114" And _ Range("C" & i).Value < "24189" And _ Range("C" & i).Value < "25233" And _ Range("C" & i).Value < "25316" And _ Range("C" & i).Value < "30402" And _ Range("C" & i).Value < "34209" And _ Range("C" & i).Value < "38417" And _ Range("C" & i).Value < "38820" And _ Range("C" & i).Value < "39206" And _ Range("C" & i).Value < "39802" And _ Range("C" & i).Value < "39803" And _ Range("C" & i).Value < "39856" And _ Range("C" & i).Value < "42022" And _ Range("C" & i).Value < "45894" And _ Range("C" & i).Value < "69428" And _ Range("C" & i).Value < "70393" And _ Range("C" & i).Value < "331961" Then Range("C" & i).Value = "" End If Next I have 40 customer numbers to query, but now every time I try to ad another line, I get "too many line continuations" Any help is appreciated. Mike Kingsle -- mkingsle ----------------------------------------------------------------------- mkingsley's Profile: http://www.excelforum.com/member.php...nfo&userid=429 View this thread: http://www.excelforum.com/showthread.php?threadid=37774 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too Many Line continuations?? VB Macro
I would store the account numbers in another worksheet (perhaps hidden),
then use Dim rCell As Range Dim rCriteria As Range Application.ScreenUpdating = False With Sheets("Hidden Criteria Sheet") Set rCriteria = .Range("A1:A" & _ .Range("A" & Rows.Count).End(xlUp).Row) End With For Each rCell In Range("C1:C" & _ Range("C" & Rows.Count).End(xlUp).Row) If Not Application.CountIf(rCriteria, rCell.Value) Then _ rCell.ClearContents Next rCell Application.ScreenUpdating = True This allows you to add or delete criteria without ever having to change the macro. In article , mkingsley wrote: I have a 10,000 line worksheet that I have constructed a macro for and it works great. I had another request to create another one looking for different data. My original had this as a query component (looking for sales reps): Dim i As Long Dim iStart As Long Dim iEnd As Long iStart = 1 iEnd = Range("B65536").End(xlUp).Row For i = iStart To iEnd If Range("B" & i).Value < "Claude Mossian" And _ Range("B" & i).Value < "Craig Kowal" And _ Range("B" & i).Value < "Dave Wester" And _ Range("B" & i).Value < "David Stone" And _ Range("B" & i).Value < "Elaine Ober" And _ Range("B" & i).Value < "House Account INDiana" And _ Range("B" & i).Value < "House Account Indiana Lab" And _ Range("B" & i).Value < "Jill Campbell" And _ Range("B" & i).Value < "Lana Compo" And _ Range("B" & i).Value < "Steve Horvath" Then Range("B" & i).Value = "" End If Next the new data criteria are numbers, so I modified the old macro and entered it like this: Dim i As Long Dim iStart As Long Dim iEnd As Long iStart = 1 iEnd = Range("C65536").End(xlUp).Row For i = iStart To iEnd If Range("C" & i).Value < "21012" And _ Range("C" & i).Value < "21056" And _ Range("C" & i).Value < "22053" And _ Range("C" & i).Value < "23801" And _ Range("C" & i).Value < "24028" And _ Range("C" & i).Value < "24087" And _ Range("C" & i).Value < "24091" And _ Range("C" & i).Value < "24095" And _ Range("C" & i).Value < "24114" And _ Range("C" & i).Value < "24189" And _ Range("C" & i).Value < "25233" And _ Range("C" & i).Value < "25316" And _ Range("C" & i).Value < "30402" And _ Range("C" & i).Value < "34209" And _ Range("C" & i).Value < "38417" And _ Range("C" & i).Value < "38820" And _ Range("C" & i).Value < "39206" And _ Range("C" & i).Value < "39802" And _ Range("C" & i).Value < "39803" And _ Range("C" & i).Value < "39856" And _ Range("C" & i).Value < "42022" And _ Range("C" & i).Value < "45894" And _ Range("C" & i).Value < "69428" And _ Range("C" & i).Value < "70393" And _ Range("C" & i).Value < "331961" Then Range("C" & i).Value = "" End If Next I have 40 customer numbers to query, but now every time I try to add another line, I get "too many line continuations" Any help is appreciated. Mike Kingsley |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too Many Line continuations?? VB Macro
Mike,
Simply break up your check into nested IF statements: For i = istart To iend If Range("C" & i).Value < "21012" And _ Range("C" & i).Value < "21056" And _ Range("C" & i).Value < "22053" And _ Range("C" & i).Value < "23801" And _ Range("C" & i).Value < "24028" And _ Range("C" & i).Value < "24087" And _ Range("C" & i).Value < "24091" And _ Range("C" & i).Value < "24095" And _ Range("C" & i).Value < "24114" And _ Range("C" & i).Value < "24189" And _ Range("C" & i).Value < "25233" And _ Range("C" & i).Value < "25316" And _ Range("C" & i).Value < "30402" Then If Range("C" & i).Value < "34209" And _ Range("C" & i).Value < "38417" And _ Range("C" & i).Value < "38820" And _ Range("C" & i).Value < "39206" And _ Range("C" & i).Value < "39802" And _ Range("C" & i).Value < "39803" And _ Range("C" & i).Value < "39856" And _ Range("C" & i).Value < "42022" And _ Range("C" & i).Value < "45894" And _ Range("C" & i).Value < "69428" And _ Range("C" & i).Value < "70393" And _ Range("C" & i).Value < "331961" Then Range("C" & i).Value = "" End If End If Next OR - my preference for ease of maintenance: Sub BetterCheck() Dim myArray As Variant Dim i As Long Dim iStart As Long Dim iEnd As Long iStart = 1 iEnd = Range("C65536").End(xlUp).Row myArray = Array("21012", "21056", "22053", "23801", "24028", _ "24087", "24091", "24095", "24114", "24189", _ "25233", "25316", "30402", "34209", "38417", _ "38820", "39206", "39802", "39803", "39856", _ "42022", "45894", "69428", "70393", "331961") For i = istart To iend If IsError(Application.Match(CStr(Range("C" & i).Value), _ myArray, False)) Then Range("C" & i).Value = "" End If Next i End Sub HTH, Bernie MS Excel MVP "mkingsley" wrote in message ... I have a 10,000 line worksheet that I have constructed a macro for and it works great. I had another request to create another one looking for different data. My original had this as a query component (looking for sales reps): Dim i As Long Dim iStart As Long Dim iEnd As Long iStart = 1 iEnd = Range("B65536").End(xlUp).Row For i = iStart To iEnd If Range("B" & i).Value < "Claude Mossian" And _ Range("B" & i).Value < "Craig Kowal" And _ Range("B" & i).Value < "Dave Wester" And _ Range("B" & i).Value < "David Stone" And _ Range("B" & i).Value < "Elaine Ober" And _ Range("B" & i).Value < "House Account INDiana" And _ Range("B" & i).Value < "House Account Indiana Lab" And _ Range("B" & i).Value < "Jill Campbell" And _ Range("B" & i).Value < "Lana Compo" And _ Range("B" & i).Value < "Steve Horvath" Then Range("B" & i).Value = "" End If Next the new data criteria are numbers, so I modified the old macro and entered it like this: Dim i As Long Dim iStart As Long Dim iEnd As Long iStart = 1 iEnd = Range("C65536").End(xlUp).Row For i = iStart To iEnd If Range("C" & i).Value < "21012" And _ Range("C" & i).Value < "21056" And _ Range("C" & i).Value < "22053" And _ Range("C" & i).Value < "23801" And _ Range("C" & i).Value < "24028" And _ Range("C" & i).Value < "24087" And _ Range("C" & i).Value < "24091" And _ Range("C" & i).Value < "24095" And _ Range("C" & i).Value < "24114" And _ Range("C" & i).Value < "24189" And _ Range("C" & i).Value < "25233" And _ Range("C" & i).Value < "25316" And _ Range("C" & i).Value < "30402" And _ Range("C" & i).Value < "34209" And _ Range("C" & i).Value < "38417" And _ Range("C" & i).Value < "38820" And _ Range("C" & i).Value < "39206" And _ Range("C" & i).Value < "39802" And _ Range("C" & i).Value < "39803" And _ Range("C" & i).Value < "39856" And _ Range("C" & i).Value < "42022" And _ Range("C" & i).Value < "45894" And _ Range("C" & i).Value < "69428" And _ Range("C" & i).Value < "70393" And _ Range("C" & i).Value < "331961" Then Range("C" & i).Value = "" End If Next I have 40 customer numbers to query, but now every time I try to add another line, I get "too many line continuations" Any help is appreciated. Mike Kingsley -- mkingsley ------------------------------------------------------------------------ mkingsley's Profile: http://www.excelforum.com/member.php...fo&userid=4291 View this thread: http://www.excelforum.com/showthread...hreadid=377746 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too Many Line continuations?? VB Macro
Hi,
Perhaps hold the (exlusion) comparison data in a list- use named Range ? - and use MATCH function to check if the entry is to be excluded i.e.found in MATCH. If not , set your field to "", else move to next row. OR add more than one item per line If Range("C" & i).Value < "21012" AND Range("C" & i).Value < "21056" And _ Range("C" & i).Value < "22053" And Range("C" & i).Value < "23801" And _ Range("C" & i).Value < "24028" And _ HTH "mkingsley" wrote: I have a 10,000 line worksheet that I have constructed a macro for and it works great. I had another request to create another one looking for different data. My original had this as a query component (looking for sales reps): Dim i As Long Dim iStart As Long Dim iEnd As Long iStart = 1 iEnd = Range("B65536").End(xlUp).Row For i = iStart To iEnd If Range("B" & i).Value < "Claude Mossian" And _ Range("B" & i).Value < "Craig Kowal" And _ Range("B" & i).Value < "Dave Wester" And _ Range("B" & i).Value < "David Stone" And _ Range("B" & i).Value < "Elaine Ober" And _ Range("B" & i).Value < "House Account INDiana" And _ Range("B" & i).Value < "House Account Indiana Lab" And _ Range("B" & i).Value < "Jill Campbell" And _ Range("B" & i).Value < "Lana Compo" And _ Range("B" & i).Value < "Steve Horvath" Then Range("B" & i).Value = "" End If Next the new data criteria are numbers, so I modified the old macro and entered it like this: Dim i As Long Dim iStart As Long Dim iEnd As Long iStart = 1 iEnd = Range("C65536").End(xlUp).Row For i = iStart To iEnd If Range("C" & i).Value < "21012" And _ Range("C" & i).Value < "21056" And _ Range("C" & i).Value < "22053" And _ Range("C" & i).Value < "23801" And _ Range("C" & i).Value < "24028" And _ Range("C" & i).Value < "24087" And _ Range("C" & i).Value < "24091" And _ Range("C" & i).Value < "24095" And _ Range("C" & i).Value < "24114" And _ Range("C" & i).Value < "24189" And _ Range("C" & i).Value < "25233" And _ Range("C" & i).Value < "25316" And _ Range("C" & i).Value < "30402" And _ Range("C" & i).Value < "34209" And _ Range("C" & i).Value < "38417" And _ Range("C" & i).Value < "38820" And _ Range("C" & i).Value < "39206" And _ Range("C" & i).Value < "39802" And _ Range("C" & i).Value < "39803" And _ Range("C" & i).Value < "39856" And _ Range("C" & i).Value < "42022" And _ Range("C" & i).Value < "45894" And _ Range("C" & i).Value < "69428" And _ Range("C" & i).Value < "70393" And _ Range("C" & i).Value < "331961" Then Range("C" & i).Value = "" End If Next I have 40 customer numbers to query, but now every time I try to add another line, I get "too many line continuations" Any help is appreciated. Mike Kingsley -- mkingsley ------------------------------------------------------------------------ mkingsley's Profile: http://www.excelforum.com/member.php...fo&userid=4291 View this thread: http://www.excelforum.com/showthread...hreadid=377746 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too Many Line continuations?? VB Macro
Yet another approach:
For i = iStart To iEnd Select Case Range("C" & i).Value Case "21012", "21056","22053", "23801", "24028" 'Do Nothing Case "24087", "24091", "24095","24114", "24189" 'Do Nothing Case "25233" , "25316", "30402", "34209", "38417" 'Do Nothing Case "38820" , "39206", "38902", "39803", "39856" 'Do Nothing Case "42022" , "45894", "69428", "70393", "331961" 'Do Nothing Case Else ' Value doesn't equal anything in our list Range("C" & i).Value = "" End Select Next i If you run into a problem because you are comparing numbers to strings (depending on your data), you can either 1) remove the quotation marks around the Case values or 2) use - "Select Case Cstr(Range("C" & ).Value)" - instead. HTH, -- George Nicholson Remove 'Junk' from return address. "mkingsley" wrote in message ... I have a 10,000 line worksheet that I have constructed a macro for and it works great. I had another request to create another one looking for different data. My original had this as a query component (looking for sales reps): Dim i As Long Dim iStart As Long Dim iEnd As Long iStart = 1 iEnd = Range("B65536").End(xlUp).Row For i = iStart To iEnd If Range("B" & i).Value < "Claude Mossian" And _ Range("B" & i).Value < "Craig Kowal" And _ Range("B" & i).Value < "Dave Wester" And _ Range("B" & i).Value < "David Stone" And _ Range("B" & i).Value < "Elaine Ober" And _ Range("B" & i).Value < "House Account INDiana" And _ Range("B" & i).Value < "House Account Indiana Lab" And _ Range("B" & i).Value < "Jill Campbell" And _ Range("B" & i).Value < "Lana Compo" And _ Range("B" & i).Value < "Steve Horvath" Then Range("B" & i).Value = "" End If Next the new data criteria are numbers, so I modified the old macro and entered it like this: Dim i As Long Dim iStart As Long Dim iEnd As Long iStart = 1 iEnd = Range("C65536").End(xlUp).Row For i = iStart To iEnd If Range("C" & i).Value < "21012" And _ Range("C" & i).Value < "21056" And _ Range("C" & i).Value < "22053" And _ Range("C" & i).Value < "23801" And _ Range("C" & i).Value < "24028" And _ Range("C" & i).Value < "24087" And _ Range("C" & i).Value < "24091" And _ Range("C" & i).Value < "24095" And _ Range("C" & i).Value < "24114" And _ Range("C" & i).Value < "24189" And _ Range("C" & i).Value < "25233" And _ Range("C" & i).Value < "25316" And _ Range("C" & i).Value < "30402" And _ Range("C" & i).Value < "34209" And _ Range("C" & i).Value < "38417" And _ Range("C" & i).Value < "38820" And _ Range("C" & i).Value < "39206" And _ Range("C" & i).Value < "39802" And _ Range("C" & i).Value < "39803" And _ Range("C" & i).Value < "39856" And _ Range("C" & i).Value < "42022" And _ Range("C" & i).Value < "45894" And _ Range("C" & i).Value < "69428" And _ Range("C" & i).Value < "70393" And _ Range("C" & i).Value < "331961" Then Range("C" & i).Value = "" End If Next I have 40 customer numbers to query, but now every time I try to add another line, I get "too many line continuations" Any help is appreciated. Mike Kingsley -- mkingsley ------------------------------------------------------------------------ mkingsley's Profile: http://www.excelforum.com/member.php...fo&userid=4291 View this thread: http://www.excelforum.com/showthread...hreadid=377746 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too Many Line continuations?? VB Macro
A really simple way:
Dim sCriteria As String sCriteria = "21056 22053 23801 24028 24087 24091 24095 24114 " _ & "24189 25233 25316 30402 34209 38417 38820 39206 39802 " _ & "39803 39856 42022 45894 69428 70393 331961" If InStr(sCriteria, Range("B" & i).Value) = 1 Then Range("B" & i).ClearContents End If The only caveat is if you have one value that is a string subset of another - e.g. 2505 and 25056, then the cell with 2505 would also be cleared. To resolve this, you could delimit the string values above with ";", making sure you have a trailing ; on the last value. The Instr(0 would change to: If InStr(sCriteria, Range("B" & i).Value & ";") = 1 Then |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too Many Line continuations?? VB Macro
Typo corrections:
21056 22053 23801 24028 24087 24091 24095 24114 " _ & "24189 25233 25316 30402 34209 38417 38820 39206 39802 " _ & "39803 39856 42022 45894 69428 70393 331961 " Should read: 21056 22053 23801 24028 24087 24091 24095 24114 " _ & "24189 25233 25316 30402 34209 38417 38820 39206 39802 " _ & "39803 39856 42022 45894 69428 70393 331961" And, Instr(0 should be Instr() . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too Many Line continuations?? VB Macro
For tracking many names, I prefer something along this line. Make a Table
of your data, and give it a range name (ie "Tbl". This should make it easy to keep updated. Then, use Data Validation. Adjust for your own range. Sub Demo() Dim rng As Range With [A1:A20].Validation .Delete .Add _ Type:=xlValidateList, _ Formula1:="=Tbl" For Each rng In .Parent.Cells If rng.Validation.Value Then rng.ClearContents Next .Delete ' Delete Validation End With End Sub HTH :) -- Dana DeLouis Win XP & Office 2003 "mkingsley" wrote in message ... I have a 10,000 line worksheet that I have constructed a macro for and it works great. I had another request to create another one looking for different data. My original had this as a query component (looking for sales reps): Dim i As Long Dim iStart As Long Dim iEnd As Long iStart = 1 iEnd = Range("B65536").End(xlUp).Row For i = iStart To iEnd If Range("B" & i).Value < "Claude Mossian" And _ Range("B" & i).Value < "Craig Kowal" And _ Range("B" & i).Value < "Dave Wester" And _ Range("B" & i).Value < "David Stone" And _ Range("B" & i).Value < "Elaine Ober" And _ Range("B" & i).Value < "House Account INDiana" And _ Range("B" & i).Value < "House Account Indiana Lab" And _ Range("B" & i).Value < "Jill Campbell" And _ Range("B" & i).Value < "Lana Compo" And _ Range("B" & i).Value < "Steve Horvath" Then Range("B" & i).Value = "" End If Next the new data criteria are numbers, so I modified the old macro and entered it like this: Dim i As Long Dim iStart As Long Dim iEnd As Long iStart = 1 iEnd = Range("C65536").End(xlUp).Row For i = iStart To iEnd If Range("C" & i).Value < "21012" And _ Range("C" & i).Value < "21056" And _ Range("C" & i).Value < "22053" And _ Range("C" & i).Value < "23801" And _ Range("C" & i).Value < "24028" And _ Range("C" & i).Value < "24087" And _ Range("C" & i).Value < "24091" And _ Range("C" & i).Value < "24095" And _ Range("C" & i).Value < "24114" And _ Range("C" & i).Value < "24189" And _ Range("C" & i).Value < "25233" And _ Range("C" & i).Value < "25316" And _ Range("C" & i).Value < "30402" And _ Range("C" & i).Value < "34209" And _ Range("C" & i).Value < "38417" And _ Range("C" & i).Value < "38820" And _ Range("C" & i).Value < "39206" And _ Range("C" & i).Value < "39802" And _ Range("C" & i).Value < "39803" And _ Range("C" & i).Value < "39856" And _ Range("C" & i).Value < "42022" And _ Range("C" & i).Value < "45894" And _ Range("C" & i).Value < "69428" And _ Range("C" & i).Value < "70393" And _ Range("C" & i).Value < "331961" Then Range("C" & i).Value = "" End If Next I have 40 customer numbers to query, but now every time I try to add another line, I get "too many line continuations" Any help is appreciated. Mike Kingsley -- mkingsley ------------------------------------------------------------------------ mkingsley's Profile: http://www.excelforum.com/member.php...fo&userid=4291 View this thread: http://www.excelforum.com/showthread...hreadid=377746 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too Many Line continuations?? VB Macro
If the criteria list may change over time, I would definitely use one
of the other suggestions such as Dana's. I just threw my suggestion out there because there are a lot of times that solutions get over-engineered when a quick and dirty (and succinct) solution will suffice. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Too Many Line continuations | Excel Discussion (Misc queries) | |||
Macro code to put series name next to individual line in line grap | Charts and Charting in Excel | |||
How to convert a dotted line to a solid line in a line graph | Charts and Charting in Excel | |||
Macro problem on, Yellowed line - previous line or next line. | Excel Programming | |||
Macro convert CSV -to- XLS without parsing line by line | Excel Programming |