Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bad Data Somewhere?
O.k. I've got another question already, hopefully it isn't as silly as my last one which I solved 30 seconds after posting
Anyways, what I've got is a function that shows only the records I haven't found a price for yet by hiding all the records that I do have a price for Sub HideFinishedEntries( If ActiveSheet.UsedRange.Rows.Count Like "??" Then myDigetCount = If ActiveSheet.UsedRange.Rows.Count Like "???" Then myDigetCount = If ActiveSheet.UsedRange.Rows.Count Like "????" Then myDigetCount = myListLength = Right(Str(ActiveSheet.UsedRange.Rows.Count), myDigetCount For Each c In ActiveSheet.Range("C2:C" + myListLength If c.Value < "" And c.Offset([0], [8]).Value < "" Or c.Offset([0], [-1]).Value < "" Or c.Offset([0], [-2]).Value < "" Then c.EntireRow.Hidden = Tru Nex End Su Simple, right? I think so, but I'm confused because on 2 out of the 8 sheets it completes, meaning it does hide all the appropriate entries, but gives me a "Type mismatch error" before ending, and the debugger highlights that massive "IF" line. I don't see anything especially different about these sheets. They contain the same data types in the same columns, but the only thing I can think of is that there must be some kinda evil data on those sheets. Unfortunatly I don't have any idea what would do that, so I don't know what to look for. Any ideas? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bad Data Somewhere?
Sub HideFinishedEntries()
Dim MyListLength as Long MyListLength = ActiveSheet.Usedrange.rows.Count For Each c In ActiveSheet.Range("C2:C" & myListLength) If c.Text < "" And c.Offset(0,8).Text < "" Or _ c.Offset(0, -1).Text < "" Or _ c.Offset(0, -2).Text < "" _ Then c.EntireRow.Hidden = True Next End Sub Should resolve whatever the problem is. Excel will automatically convert from long to a string to concatenate the mylistlength to "C2:C" -- Regards, Tom Ogilvy "The Wonder Thing" wrote in message ... O.k. I've got another question already, hopefully it isn't as silly as my last one which I solved 30 seconds after posting. Anyways, what I've got is a function that shows only the records I haven't found a price for yet by hiding all the records that I do have a price for. Sub HideFinishedEntries() If ActiveSheet.UsedRange.Rows.Count Like "??" Then myDigetCount = 2 If ActiveSheet.UsedRange.Rows.Count Like "???" Then myDigetCount = 3 If ActiveSheet.UsedRange.Rows.Count Like "????" Then myDigetCount = 4 myListLength = Right(Str(ActiveSheet.UsedRange.Rows.Count), myDigetCount) For Each c In ActiveSheet.Range("C2:C" + myListLength) If c.Value < "" And c.Offset([0], [8]).Value < "" Or c.Offset([0], [-1]).Value < "" Or c.Offset([0], [-2]).Value < "" Then c.EntireRow.Hidden = True Next End Sub Simple, right? I think so, but I'm confused because on 2 out of the 8 sheets it completes, meaning it does hide all the appropriate entries, but gives me a "Type mismatch error" before ending, and the debugger highlights that massive "IF" line. I don't see anything especially different about these sheets. They contain the same data types in the same columns, but the only thing I can think of is that there must be some kinda evil data on those sheets. Unfortunatly I don't have any idea what would do that, so I don't know what to look for. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Labels- POssible to show data value and data label together? | Charts and Charting in Excel | |||
Increase Your Business By Data Conversion, Data Format and Data EntryServices in India | Excel Worksheet Functions | |||
Save 20% On Data Conversion and Data Formats Services by Data EntryIndia | Excel Discussion (Misc queries) | |||
Data Entry Online, Data Format, Data Conversion and Data EntryServices through Data Entry Outsourcing | Excel Discussion (Misc queries) | |||
MULTIPLE DATA - How to insert new data into existing data.... | Excel Discussion (Misc queries) |