Need help w/ Excel Macro - (IF & AND) 2

[4281967]

I have a spreadsheet that is imported daily into an access database. The spreadsheets columns are always the same, however the number of rows will change daily. The spreadsheet represents invoices from the previous day.

What I need to do is check each row to see if a particular location in the field ordered one of about 28 parts. If so, I need to concatenate an "-A" on the end of the part number.

I started this, however, it doesn't work - I get a '1004' error - and even if it did work, it returns EVERY row. Is there a way to just have the macro check rows that contain data?

Sub test()
'

Dim j

    For j = 1 To Rows.Count


        If Sheets("import").Cells(j + 1, 7).Value = "FK4DNF005000" And
Sheets("import").Cells(j + 1, 11).Value = "8603" Then
           Sheets("import").Cells(j + 1, 7).Value = "FK4DNF005000-A"
        End If

    Next j

End Sub

In the section above, I use Cells(j + 1, 7)
j+1 is to get past the first row (heading).
The 7 and 11 values are for PartNumber and Location (column G and K)

If I could get the IF statement above working properly, I was planning on just using a series of IF's to walk all 28 parts - I am sure there is a more efficient way to do this, but I don't know how... (perhaps use SQL - IF NOT IN (Part#1, Part#2, Part#3, etc.)??

Thanks for any help or suggestions

 

[SkipVought]

Hi,

Just string the parts of interest on the Case. Don't forget the Else condition...

Sub test()
    '
    
    Dim j
    With Sheets("import")
        For j = 1 To Rows.Count
    
            If .Cells(j + 1, 11).Value = "8603" Then
                With .Cells(j + 1, 7)
                    Select Case .Value
                        Case "FK4DNF005000", "part2", "Part3", "Part4"
            
                            .Value = .Value & "-A"
                        Case Else
                            .Value = "???"
                    End Select
                End With
            End If
        Next j
    End With
End Sub

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[4281967]

Skip,
Thanks - that works, but it also errors once it's done. It looks like it loops through the entire spreadsheet (as I added a "match" on the first and last line.) As expected it concatenated the "-A", however it then errors out on this line:

If .Cells(j + 1, 11).Value = "8603" Then

The error is a Run-Time Error 1004

 

[SkipVought]

...but it also errors once it's done

???

What is the value of .Cells(j + 1, 11).Value when this error occurs?

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[perrymans]

Hello 428... (have a name?),

First, I like to delete all of the empty rows first, this will prevent problems later on:

Function DelRows(sFile As String, wks As Excel.Worksheet, appExcel As Excel.Application)

Dim DLSearchRow As Long
For DLSearchRow = appExcel.ActiveSheet.UsedRange.Rows.Count To 1 Step -1
    If Trim(appExcel.Range("A" & DLSearchRow).Value & "") = "" Then
        appExcel.Rows(DLSearchRow).Delete
    End If
Next
   
End Function

Next, I sequence through each row, looking for certain factors. I find what I want, grab the piece I am interested in, then COPY that to a different cell. I do this to associate set ID's for each row BEFORE import (all data in column A). I can provide the code if you would like.

This may be better for you, depending on whether or not there is additional information in same cell, or to maintain the original data.

Now the benefit of deleting empty rows. I get to sequence through the remaining spreadsheet by looking for only rows with something in them with:

appExcel.Range("A1").Select
    
'Start search in row 1
LSearchRow = 1

'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 1

While Len(appExcel.Range("A" & CStr(LSearchRow)).Value) > 0
       IF, SELECT, blah, blah, blah

appExcel.Range("B" & CStr(LSearchRow)).Select
appExcel.Range("B" & CStr(LSearchRow)).Value = strPOLine

LSearchRow = LSearchRow + 1
LCopyToRow = LCopyToRow + 1

Wend

So while the number of characters in a row is greater than 1 (meaning not blank), it performs the operations.

Now I digress, this would be a lot easier once it is in Access. Is there a reason for doing this before import? Are you not importing the whole thing? Even if not needed, it is easier to import the whole thing to a temp table, then use Access to do your magic, then delete the contents of the table.

Also, why are you adding an 'A'? The invoice says:
'FK4DNF005000'
and you change it to one of 28 products called:
'FK4DNF005000-A'

Which is the right part number? Why are the numbers different on the invoice? If your database is storing the part number as:
'FK4DNF005000-A'
then all you need to do is import, find 'FK4DNF005000', then update with the ID field number for part 'FK4DNF005000-A'.

So if:
Tbl_Parts
PartID PartNumber
---------------------
21 FK4DNF005000-A

So you would only need to search for 'FK4DNF005000' then use an update/append to insert the '21'.

Again, more reasons to import first, then manipulate.

Sean.

Sean.

 

[4281967]

Skip,
How can I tell what the value is of .Cells(j + 1, 11).Value when the error occurs?

 

[SkipVought]

When it errors, hit the DEBUG button and then View/Watch Window and insert the fully quallified object...

Sheets("import").Cells(j + 1, 11).Value

The value of j + 1 is the row.



Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[4281967]

Skip,
OK thanks - I figured it out.

Perrymans - thanks - I also found a use for your example.

Thanks,
Patrick