CODEWITHSUNDEEP
vbaexcelexcel-2016
alowflyingpig
alowflyingpig

Reputation: 738

VBA Excel 2016 Loop through multiple ranges return offset value

Problem:

There are values in column K & L, depending if the cell/s have a value (number) I want to return an offset value =RC[-4]

The following works fine:

K4 has a value, L4 has a value, do nothing.
K5 has a value, L5 doesn't have a value, value = =RC[-4]

I run into problems when L has been override with a number (which is allowed) but the VBA still overrides that number when the macro runs. Eg:

Say =RC[-4] equals 20 If K4 has a value and L4 is 10, skip this cell. Currently the VBA will override the value in L4 to 20

Looking at it from another angle:
If K4 <> "" And L4 = "" Then "=RC[-4]" Else skip/next cells (K5/L5, K6/L6, etc etc)

This is my wanted output but my research and knowledge is lacking...

Sub AccrualValue3()   
    Dim rng As Range
    Dim Exrng As Range

    Last_Row = Range("H" & Rows.Count).End(xlUp).Row - 1

    Set rng = Range("K4:K" & Last_Row)
    Set Exrng = Range("L4:L" & Last_Row)

    For Each cell In rng
        If cell.Value <> "" Then
            For Each cell2 In Exrng
                If cell2.Value = "" Then
                    cell.Offset(0, 1).Value = "=RC[-4]"
                Else
                    cell.Offset(0, 1).Value = ""
                End If
            Next
        End If
    Next
End Sub

Upvotes: 1

Views: 1338

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57693

It's easier to use a For … To loop just counting the row numbers. Also you don't need a second loop for that.

Option Explicit

Sub AccrualValue3()
    Dim LastRow As Long
    LastRow = Range("H" & Rows.Count).End(xlUp).Row - 1

    Dim iRow As Long
    For iRow = 4 To LastRow
        If Cells(iRow, "K").Value <> "" And Cells(iRow, "L").Value = "" Then
            Cells(iRow, "L").Value = Cells(iRow, "L").Offset(ColumnOffset:=-4).Value
        End If
    Next iRow
End Sub

Alternatively you can select all empty cells in column L with .SpecialCells(xlCellTypeBlanks) and check the column K only for these cells. This should be faster if you have many rows, because it only checks rows where column L is empty but not every row.

Sub AccrualValue3ALTERNATIVE()
    Dim LastRow As Long
    LastRow = Range("H" & Rows.Count).End(xlUp).Row - 1

    Dim EmptyCellsInColumnL As Range
    Set EmptyCellsInColumnL = Range("L4:L" & LastRow).SpecialCells(xlCellTypeBlanks)

    Dim Cell As Range
    For Each Cell In EmptyCellsInColumnL
        If Cell.Offset(ColumnOffset:=-1).Value <> "" Then
            Cell.Value = Cell.Offset(ColumnOffset:=-4).Value
        End If
    Next Cell
End Sub

Note that subtracting 1 from the last used row 

LastRow = Range("H" & Rows.Count).End(xlUp).Row - 1

leaves the last used row unprocessed.

Upvotes: 1

Related Questions