CODEWITHSUNDEEP
vbaexcel
Frethy
Frethy

Reputation: 35

VBA Check Row For Value Then Another Value and Paste If True

I have a list of parts numbers with short descriptions and long descriptions. The list is formatted in such a way that in the first column there is a part number. In the second column there is a description code. In the third column there is a description. The description is either the name, short description, or long description based on the preceding description code.

See screenshot example: Original Dataset

As you can see, some of the parts have all three descriptions and some of them don't.

I'm trying to take the data from sheet 1 and paste the information into sheet 2 that has the consolidated and proper row structure ie. part number, name, short, long.

See screenshot example: New Dataset

Here is some code I've been working with. I feel like I'm close or at least on the right track but it definitely isn't working and currently throwing a next without for error.

Dim i As Long

For i = 1 To Rows.Count
With ActiveWorkbook
.Sheets("Parts List").Range("A1").Select
    If .Sheets("Parts List").Cells(i, 1).Value = .Sheets("Product Description").Cells(i, 1) Then
    If .Sheets("Product Description").Cells(i, 2).Value = "DES" Then
    .Sheets("Parts List").Cells(i, 2).Value = .Sheets("Product Description").Cells(i, 2).Value
    ElseIf .Sheets("Product Description").Cells(i, 2).Value = "EXT" Then
    .Sheets("Parts List").Cells(i, 5).Value = .Sheets("Product Description").Cells(i, 2).Value
    ElseIf .Sheets("Product Description").Cells(i, 2).Value = "MKT" Then
    .Sheets("Parts List").Cells(i, 3).Value = .Sheets("Product Description").Cells(i, 2).Value
End If
Next i
End With

Any and all help would be greatly appreciated. I'm really just trying to get it to loop through this one sheet and extract things and put them on another sheet. Sounds easy enough.

Upvotes: 2

Views: 1741

Answers (1)

YowE3K
YowE3K

Reputation: 23974

I would make a few minor changes to your code, mainly to keep a counter of which row you are writing to on the destination sheet, plus quite a few cosmetic changes:

Dim srcRow As Long
Dim dstRow As Long

Dim srcWs As Worksheet
Dim dstWs As Worksheet

Set srcWs = ActiveWorkbook.Worksheets("Product Description")
Set dstWs = ActiveWorkbook.Worksheets("Parts List")
dstRow = 9  'Initially point to the header row

'Only do your For loop for cells that contain a product code, rather than
'for the 1 million rows in the worksheet
For srcRow = 1 To srcWs.Range("A" & srcWs.Rows.Count).End(xlUp).Row
    '.Sheets("Parts List").Range("A1").Select  'Not needed
    If dstWs.Cells(dstRow, "A").Value <> srcWs.Cells(srcRow, "A") Then
        'Increment destination row
        dstRow = dstRow + 1
        'Store part number
        dstWs.Cells(dstRow, "A").Value = srcWs.Cells(srcRow, "A").Value
    End If
    'Store other data
    Select Case srcWs.Cells(srcRow, "B").Value
        Case "DES"
            dstWs.Cells(dstRow, "B").Value = srcWs.Cells(srcRow, "C").Value
        Case "EXT"
            dstWs.Cells(dstRow, "E").Value = srcWs.Cells(srcRow, "C").Value
        Case "MKT"
            dstWs.Cells(dstRow, "C").Value = srcWs.Cells(srcRow, "C").Value
    End Select
Next

The "Next without a For" error that you were getting was due to an unmatched If statement (your first If statement had no corresponding End If) and your With block started inside your For loop but finished after the loop ended. That type of error is easily picked up by always / consistently indenting your code. Below is what your original code looked like once it was indented:

Dim i As Long

For i = 1 To Rows.Count
    With ActiveWorkbook
        .Sheets("Parts List").Range("A1").Select
        If .Sheets("Parts List").Cells(i, 1).Value = .Sheets("Product Description").Cells(i, 1) Then
            If .Sheets("Product Description").Cells(i, 2).Value = "DES" Then
                .Sheets("Parts List").Cells(i, 2).Value = .Sheets("Product Description").Cells(i, 2).Value
            ElseIf .Sheets("Product Description").Cells(i, 2).Value = "EXT" Then
                .Sheets("Parts List").Cells(i, 5).Value = .Sheets("Product Description").Cells(i, 2).Value
            ElseIf .Sheets("Product Description").Cells(i, 2).Value = "MKT" Then
                .Sheets("Parts List").Cells(i, 3).Value = .Sheets("Product Description").Cells(i, 2).Value
            End If
        'Notice that the Next i is not lined up with the For i
        Next i
    End With
'Notice that we haven't ended up back at the left - so we must be missing
'the end of some sort of "block"

Upvotes: 1

Related Questions