CODEWITHSUNDEEP
vbaexcel
Daniel Strong
Daniel Strong

Reputation: 213

VBA ElseIf with multiple ranges

I have a file with data in A - U and W - AA. I am trying to write code that will copy cells from the first range A - U and once it encounters a blank cell in A, it looks to the second range W - AA. The first part of the code is working, but I cannot seem to figure out how to call the second range.

Sub Test()

    Dim rowCount2 As Long, shtSrc As Worksheet
    Dim shtDest As Worksheet
    Dim rng2 As Range
    Dim rng3 As Range
    Dim currentRow As Long


    Set shtSrc = Sheets("Data")
    Set shtDest = Sheets("Audit")

    rowCount2 = shtSrc.Cells(Rows.Count, "A").End(xlUp).Row

    Set rng2 = shtSrc.Range("A1:A" & rowCount2)
    Set rng3 = shtSrc.Range("W1:W" & rowCount2)

    currentRow = 2

    For Each cell2 In rng2.Cells
        If cell2.Value <> "" Then
            shtDest.Range("B" & currentRow).Value2 = cell2.Value2
            shtDest.Range("C" & currentRow).Value2 = cell2.Offset(0, 1).Value2
            shtDest.Range("G" & currentRow).Value2 = cell2.Offset(0, 2).Value2

            currentRow = currentRow + 1

        ElseIf cell2.Value = "" Then

            shtDest.Range("B" & currentRow).Value2 = cell2.Value2
            shtDest.Range("C" & currentRow).Value2 = cell2.Offset(0, 1)

            currentRow = currentRow + 1

        End If
    Next cell2

End Sub

Upvotes: 0

Views: 192

Answers (1)

OpiesDad
OpiesDad

Reputation: 3435

You could just use the offset function to get to the appropriate columns:

Dim RangeDiff As Integer
...
Set rng2 = ...
Set rng3 = ...
RangeDiff = Rng3.Column - rng2.column
...

ElseIf cell2.Value = "" Then

        shtDest.Range("B" & currentRow).Value2 = cell2.Offset(0, RangeDiff).Value2
        shtDest.Range("C" & currentRow).Value2 = cell2.Offset(0, RangeDiff + 1)

...

EDIT per new understanding of requirements:

...
For Each cell2 In rng2.Cells
    If cell2.Value <> "" Then
        shtDest.Range("B" & currentRow).Value2 = cell2.Value2
        shtDest.Range("C" & currentRow).Value2 = cell2.Offset(0, 1).Value2
        shtDest.Range("G" & currentRow).Value2 = cell2.Offset(0, 2).Value2

        currentRow = currentRow + 1

    ElseIf cell2.Value = "" Then

        Exit For
    End If
Next cell2

For Each cell3 In rng3.Cells
    If cell3.Value <> "" Then
        shtDest.Range("B" & currentRow).Value2 = cell3.Value2
        shtDest.Range("C" & currentRow).Value2 = cell3.Offset(0, 1).Value2
        shtDest.Range("G" & currentRow).Value2 = cell3.Offset(0, 2).Value2

        currentRow = currentRow + 1

    ElseIf cell3.Value = "" Then

        Exit For
    End If
Next cell3

...

Upvotes: 1

Related Questions