Loop Help Please
-
I am trying to do this loop, so it checks if a value entered is present, if not, it deletes the row, however, the process hangs once it finishes, how do i end the statement (i.e. when all records have been checked, display a message box), as I am not sure where to end it (I thought the end if and wend would end the statement, but do I possibly need an else somewhere?), cheers p.s. this is VBA in excel 2003, but I assume its the same principal as VB ---- 'When a cell contains data, perform the IF... While Len(Range("B" & CStr(LFindRow)).Value) > 0 'If the cell does not contain the inputted criteria then delete the row If Range("B" & CStr(LFindRow)).Value <> CustVar Then Rows(CStr(LFindRow)).Delete 'End the IF End If 'End/Loop the while Wend ----
-
I am trying to do this loop, so it checks if a value entered is present, if not, it deletes the row, however, the process hangs once it finishes, how do i end the statement (i.e. when all records have been checked, display a message box), as I am not sure where to end it (I thought the end if and wend would end the statement, but do I possibly need an else somewhere?), cheers p.s. this is VBA in excel 2003, but I assume its the same principal as VB ---- 'When a cell contains data, perform the IF... While Len(Range("B" & CStr(LFindRow)).Value) > 0 'If the cell does not contain the inputted criteria then delete the row If Range("B" & CStr(LFindRow)).Value <> CustVar Then Rows(CStr(LFindRow)).Delete 'End the IF End If 'End/Loop the while Wend ----
Hi, there is a flaw in the logic: when Len is > 0 and the IF fails, the while will loop forever. You should consider a while without if, i.e. AND together the two conditions in the WHILE. :)
Luc Pattyn [Forum Guidelines] [My Articles]
this months tips: - before you ask a question here, search CodeProject, then Google - the quality and detail of your question reflects on the effectiveness of the help you are likely to get - use PRE tags to preserve formatting when showing multi-line code snippets
-
I am trying to do this loop, so it checks if a value entered is present, if not, it deletes the row, however, the process hangs once it finishes, how do i end the statement (i.e. when all records have been checked, display a message box), as I am not sure where to end it (I thought the end if and wend would end the statement, but do I possibly need an else somewhere?), cheers p.s. this is VBA in excel 2003, but I assume its the same principal as VB ---- 'When a cell contains data, perform the IF... While Len(Range("B" & CStr(LFindRow)).Value) > 0 'If the cell does not contain the inputted criteria then delete the row If Range("B" & CStr(LFindRow)).Value <> CustVar Then Rows(CStr(LFindRow)).Delete 'End the IF End If 'End/Loop the while Wend ----
I think your problem is that if there is data in the cell but it doesn't match the value you don't go to the next row. try increasing LFindRow good luck