I need guidance :) - Xtreme Visual Basic Talk

06-08-2004, 03:00 PM

marckuh

Freshman

Join Date: Sep 2003

Posts: 29

I need guidance :)

I am working on a project that goes through a csv file and finds parts order that did not go through. A successful parts order will only contain "CPC Order Confirm". A bad parts order will not have that string in it except for one case. That case would be if a field was wrong. It will then say "CPC Order Confirm" and then the next entry will be "BAD SITE ID". This is all in one cell....it is the dump of the work log.

The code will search through a csv file(one cell) looking for a string, "CPC Order Confirm". Once it finds the string, it will rename the cell to 'OK'. If it doesn't, it leaves it alone. Then I perform a backwards loop to remove all of the rows with "OK" in them.

Now I have to modify it not only look for "CPC Order Confirm", but also "BAD SITE ID". In the csv file, it will say "CPC Order Confirm", then it will say "BAD SITE ID', if something wrong was with the parts order.

I am just trying to figure out the best way of doing it. What do you guys think?

Code:

Public Sub FindBadTickets1()
Dim strFilenm
'Workbooks.Open FileName:="C:\Remedy Data\OpenAssigned.csv"
  Dim a, Count As Integer
    a = Range("C65536").End(xlUp).Row ' To get the final row with data
nRows = Cells(1, 1).CurrentRegion.Rows.Count

' Define variable to hold a cell returned by the "find" command.
    Dim cell As Range
    
    ' Our input will be in column H. Rows 2 through the end of the data.
    strRange = "H2:H" & nRows
    
    ' Define the input area to be worked with
    For Each cell In Range("H2:H" & nRows)
                
        'Search for "CPC Order Confirm" in the worklog
        x = InStr(1, cell.Value, "CPC Order Confirm ")
                    
            If x = 0 Then
                            
            Else
                'x was not zero. We made the first call.
               cell.Value = "OK"
            End If
                      
          Next
       
 For Count = a To 1 Step -1 ' loop backwards
       Cells(Count, 8).Select
       If ActiveCell.Value = "OK" Then
        ActiveCell.EntireRow.Delete
        End If
        Next

06-09-2004, 12:23 AM

Timbo

Green-Eyed

Super Moderator
* Guru *

Join Date: May 2001

Location: Bangkok, Thailand

Posts: 10,261

This will work, but it will be much quicker if you use the 'Find' method of the Range object. There's lots of examples of different uses of this in this board, and there's more in the '101 FAQ' thread at the top of the stack.

__________________
"He's not the Messiah. He's a very naughty boy!" - Brian's mum

Can't find the answer? >> Try something new!
Become a Professional

06-09-2004, 07:47 AM

asonetuh

Centurion

Join Date: Nov 2002

Posts: 133

Quote:

Originally Posted by marckuh

It will then say "CPC Order Confirm" and then the next entry will be "BAD SITE ID". This is all in one cell....

Now I have to modify it not only look for "CPC Order Confirm", but also "BAD SITE ID". In the csv file, it will say "CPC Order Confirm", then it will say "BAD SITE ID', if something wrong was with the parts order.

If I understand this correctly, you need to modify the posted code so that it will *not* change cells with "CPC Order Confirm" that *also* contain "BAD SITE ID", right? I'm a bit confused where this "BAD SITE ID" shows up - you say "the next entry", but then say "This is all in one cell". Either way, unless you want to switch to something like what Timbo mentioned, you would just need to add another if inside your current 'if x = 0' statement. If one cell contains both pieces of text (like "CPC Order Confirm: BAD SITE ID"), then it might look like this:

Code:

If x = 0 Then
Else
    If InStr(1, cell.Value, "BAD SITE ID") = 0 Then
        'Bad site ID was not found, ok to remove
        cell.Value = "OK"
    End If
End If

If "BAD SITE ID" is the same column next row, then you could use something like Cells(cell.Row + 1, cell.Column).Value to get the row beneath to search for 'bad site ID'.

Some other comments - You define nRows as CurrentRegion.Rows.Count, but then use this *count* of rows as the row index when you append it to your range "H2:H" -- you also get the current region from Cells(1, 1) (or A1) which may or may not have anything to do with the number of rows in column H. Then you use your 'a' variable, defined as the last row in column C, as the row number for column H when you move back up. Unless I'm missing something, it would seem better to define 'a' as the last row in column H, and use this instead of 'nRows'.

Last edited by asonetuh; 06-09-2004 at 07:50 AM. Reason: fixed error 'number of rows in column C' to 'last row in column C'