Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help, i can't figure out 2 "And" operators!
i got code from http://www.mvps.org/dmcritchie/excel/delempty.htm to be able to delete a row based on the same specific value in another col. i "attempted" to modify the code to work for my purpose but i've only been working w/ VBA for a few months so i'm clueless as to what to do exactly. the original code is this: Sub Delete_rows_based_on_ColA_ColB() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range, rng As Range, i As Long Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues) For i = rng.Count To 1 Step -1 If LCase(rng(i).Value) = "standard" _ And LCase(rng(i).Offset(0, 1).Value) = "card" _ Then rng(i).EntireRow.Delete Next i Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub and mine is this: Sub DelRowOn_ColsBCF() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range, rng As Range, i As Long Set rng = Columns("B").SpecialCells(xlConstants, xlTextValues) For i = rng.Count To 1 Step -1 If LCase(rng(i).Value) = "Customer Relations" _ And LCase(rng(i).Offset(0, 1).Value) = "[NULL]" _ And LCase(rng(i).Offset(0, 3).Value) = "[NULL]" _ Then rng(i).EntireRow.Delete Next i Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub what i'm attempting to do is to delete rows in which colB = "Customer Relations", colC = "[NULL]" and colF = "[NULL]" i'd appreciate any help, ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help, i can't figure out 2 "And" operators!
I think that this:
If LCase(rng(i).Value) = "Customer Relations" _ And LCase(rng(i).Offset(0, 1).Value) = "[NULL]" _ And LCase(rng(i).Offset(0, 3).Value) = "[NULL]" _ needs to be If LCase(rng(i).Value) = "customer relations" _ And LCase(rng(i).Offset(0, 1).Value) = "[null]" _ And LCase(rng(i).Offset(0, 4).Value) = "[null]" _ Since you're changing the value to lowercase -- LCase() -- you need to make sure that you're comparing the values to their lowercase equivalents. Also, column F is 4 columns offset from B, not 3. -- HTH, Dianne In , aapp81 typed: i got code from http://www.mvps.org/dmcritchie/excel/delempty.htm to be able to delete a row based on the same specific value in another col. i "attempted" to modify the code to work for my purpose but i've only been working w/ VBA for a few months so i'm clueless as to what to do exactly. the original code is this: Sub Delete_rows_based_on_ColA_ColB() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range, rng As Range, i As Long Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues) For i = rng.Count To 1 Step -1 If LCase(rng(i).Value) = "standard" _ And LCase(rng(i).Offset(0, 1).Value) = "card" _ Then rng(i).EntireRow.Delete Next i Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub and mine is this: Sub DelRowOn_ColsBCF() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range, rng As Range, i As Long Set rng = Columns("B").SpecialCells(xlConstants, xlTextValues) For i = rng.Count To 1 Step -1 If LCase(rng(i).Value) = "Customer Relations" _ And LCase(rng(i).Offset(0, 1).Value) = "[NULL]" _ And LCase(rng(i).Offset(0, 3).Value) = "[NULL]" _ Then rng(i).EntireRow.Delete Next i Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub what i'm attempting to do is to delete rows in which colB = "Customer Relations", colC = "[NULL]" and colF = "[NULL]" i'd appreciate any help, ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help, i can't figure out 2 "And" operators!
unless the cells actually contain the text string [NULL] you will never
pass that test. if you convert the test cell to lowercase, it can never equal "Customer Relations" (test should be "customer relations") rng(i) will always be an offset from the upper left corner of the range. for example (from the immediate window:) set rng = Range("A1,A5,A10") ? rng(1).Address $A$1 ? rng(2).Address $A$2 Since you are using special cells, this will probably not be what you want: if the actually do contain the text string [NULL] Sub DelRowOn_ColsBCF() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range, rng As Range, i As Long, rng1 as Range Set rng = Columns("B").SpecialCells(xlConstants, xlTextValues) For each cell in rng If LCase(cell.Value) = "customer relations" _ And Ucase(cell.Offset(0, 1).Value) = "[NULL]" _ And Ucase(cell.Offset(0, 3).Value) = "[NULL]" _ Then if rng1 is Nothing then set rng1 = cell else set rng1 = union(cell,rng1) end if End if Next cell if not rng1 is nothing then _ rng1.Entirerow.Delete Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub -- Regards, Tom Ogilvy "aapp81" wrote in message ... i got code from http://www.mvps.org/dmcritchie/excel/delempty.htm to beSub DelRowOn_ColsBCF() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range, rng As Range, i As Long, rng1 as Range Set rng = Columns("B").SpecialCells(xlConstants, xlTextValues) For each cell in rng If LCase(cell.Value) = "customer relations" _ And isempty(cell.Offset(0, 1).Value) _ And isempty(cell.Offset(0, 3).Value) _ Then if rng1 is Nothing then set rng1 = cell else set rng1 = union(cell,rng1) end if End if Next cell if not rng1 is nothing then _ rng1.Entirerow.Delete Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub able to delete a row based on the same specific value in another col. i "attempted" to modify the code to work for my purpose but i've only been working w/ VBA for a few months so i'm clueless as to what to do exactly. the original code is this: Sub Delete_rows_based_on_ColA_ColB() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range, rng As Range, i As Long Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues) For i = rng.Count To 1 Step -1 If LCase(rng(i).Value) = "standard" _ And LCase(rng(i).Offset(0, 1).Value) = "card" _ Then rng(i).EntireRow.Delete Next i Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub and mine is this: Sub DelRowOn_ColsBCF() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range, rng As Range, i As Long Set rng = Columns("B").SpecialCells(xlConstants, xlTextValues) For i = rng.Count To 1 Step -1 If LCase(rng(i).Value) = "Customer Relations" _ And LCase(rng(i).Offset(0, 1).Value) = "[NULL]" _ And LCase(rng(i).Offset(0, 3).Value) = "[NULL]" _ Then rng(i).EntireRow.Delete Next i Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub what i'm attempting to do is to delete rows in which colB = "Customer Relations", colC = "[NULL]" and colF = "[NULL]" i'd appreciate any help, ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help, i can't figure out 2 "And" operators!
hey! thank you very much! i actually did that w/ minimal failure! :D ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help, i can't figure out 2 "And" operators!
hey, thanks a lot, it actually worked great ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help, i can't figure out 2 "And" operators!
Column F is offset(0,4) from Column B as James correctly reported out -
however, the original code isn't spot on with that correction for the case reasons already pointed out as a minimum. In fact, I don't think Dave's code (the original original) is correct if specialcells produces a non-contiguous range. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... unless the cells actually contain the text string [NULL] you will never pass that test. if you convert the test cell to lowercase, it can never equal "Customer Relations" (test should be "customer relations") rng(i) will always be an offset from the upper left corner of the range. for example (from the immediate window:) set rng = Range("A1,A5,A10") ? rng(1).Address $A$1 ? rng(2).Address $A$2 Since you are using special cells, this will probably not be what you want: if the actually do contain the text string [NULL] Sub DelRowOn_ColsBCF() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range, rng As Range, i As Long, rng1 as Range Set rng = Columns("B").SpecialCells(xlConstants, xlTextValues) For each cell in rng If LCase(cell.Value) = "customer relations" _ And Ucase(cell.Offset(0, 1).Value) = "[NULL]" _ And Ucase(cell.Offset(0, 3).Value) = "[NULL]" _ Then if rng1 is Nothing then set rng1 = cell else set rng1 = union(cell,rng1) end if End if Next cell if not rng1 is nothing then _ rng1.Entirerow.Delete Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub -- Regards, Tom Ogilvy "aapp81" wrote in message ... i got code from http://www.mvps.org/dmcritchie/excel/delempty.htm to beSub DelRowOn_ColsBCF() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range, rng As Range, i As Long, rng1 as Range Set rng = Columns("B").SpecialCells(xlConstants, xlTextValues) For each cell in rng If LCase(cell.Value) = "customer relations" _ And isempty(cell.Offset(0, 1).Value) _ And isempty(cell.Offset(0, 3).Value) _ Then if rng1 is Nothing then set rng1 = cell else set rng1 = union(cell,rng1) end if End if Next cell if not rng1 is nothing then _ rng1.Entirerow.Delete Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub able to delete a row based on the same specific value in another col. i "attempted" to modify the code to work for my purpose but i've only been working w/ VBA for a few months so i'm clueless as to what to do exactly. the original code is this: Sub Delete_rows_based_on_ColA_ColB() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range, rng As Range, i As Long Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues) For i = rng.Count To 1 Step -1 If LCase(rng(i).Value) = "standard" _ And LCase(rng(i).Offset(0, 1).Value) = "card" _ Then rng(i).EntireRow.Delete Next i Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub and mine is this: Sub DelRowOn_ColsBCF() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range, rng As Range, i As Long Set rng = Columns("B").SpecialCells(xlConstants, xlTextValues) For i = rng.Count To 1 Step -1 If LCase(rng(i).Value) = "Customer Relations" _ And LCase(rng(i).Offset(0, 1).Value) = "[NULL]" _ And LCase(rng(i).Offset(0, 3).Value) = "[NULL]" _ Then rng(i).EntireRow.Delete Next i Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub what i'm attempting to do is to delete rows in which colB = "Customer Relations", colC = "[NULL]" and colF = "[NULL]" i'd appreciate any help, ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help, i can't figure out 2 "And" operators!
this works: its what i had originally except "Customer R..." is not "cu... r... and i left "[NULL]" as UCase and put the offset to 4... other than that, everything was fine in my original code. thanks for everyone's help! Sub DelRowOn_ColsBCFmain() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range, rng As Range, i As Long Set rng = Columns("B").SpecialCells(xlConstants, xlTextValues) For i = rng.Count To 1 Step -1 If LCase(rng(i).Value) = "customer relations" _ And UCase(rng(i).Offset(0, 1).Value) = "[NULL]" _ And UCase(rng(i).Offset(0, 4).Value) = "[NULL]" _ Then rng(i).EntireRow.Delete Next i Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Su ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help, i can't figure out 2 "And" operators!
If special cells produces a contiguous range, then it will work. If not,
you are only fooling yourself. It is unwise to use flawed code, it will eventually jump up and bite you. Suit yourself. -- Regards, Tom Ogilvy "aapp81" wrote in message ... this works: its what i had originally except "Customer R..." is not "cu... r..." and i left "[NULL]" as UCase and put the offset to 4... other than that, everything was fine in my original code. thanks for everyone's help! Sub DelRowOn_ColsBCFmain() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range, rng As Range, i As Long Set rng = Columns("B").SpecialCells(xlConstants, xlTextValues) For i = rng.Count To 1 Step -1 If LCase(rng(i).Value) = "customer relations" _ And UCase(rng(i).Offset(0, 1).Value) = "[NULL]" _ And UCase(rng(i).Offset(0, 4).Value) = "[NULL]" _ Then rng(i).EntireRow.Delete Next i Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help, i can't figure out 2 "And" operators!
no, please, don't take it the wrong way, i'm not saying your code is "worse" in my opinion... i'm just a beginner at this so i don't know much at all... if your code has something that corrects any errors that may arise then i wouldn't even be able to see the difference - i AM new to this... i tried your code, it worked great, the only reason i said the other code works is b/c i was familiar w/ its structure, that's all... i didn't mean to come off the wrong way... ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Formula to figure an average on a range of cells with an "if" form | Excel Worksheet Functions | |||
Embedding operators, IF(x="up",,<)IF.. | Excel Discussion (Misc queries) | |||
How do I "Sum sales figure for a finacial month to currenct week"? | Excel Worksheet Functions | |||
Can I automaticly figure "cost" and sale price in Excel? | Excel Worksheet Functions |