VBA loop to take value if field is blank

user_16493952 0
Viewed 52

Need some solution in VBA ->If the blank value in column A then takes value from column B.

I wrote some code, but I don't have any idea why this is not working.

dim LastR as Long
LastR = Worksheets("Sheet1").Range("BU" & Worksheets("Sheet1").Rows.Count).End(xlUp).Row
dim i as long
For i = LastR To 2 Step -1
    If IsEmpty(Cells(i, "a")) Then Cells(i, "a").Value = Cells(i, "b").Value
Next i
vba excel
2 Answers

You should check if the value is empty.

See two examples:

Dim LastR As Long
LastR = Worksheets("Sheet1").Range("BU" & Worksheets("Sheet1").Rows.Count).End(xlUp).Row
Dim i As Long
For i = LastR To 2 Step -1
    'If Sheets("Sheet1").Cells(i, "a") = "" Then Sheets("Sheet1").Cells(i, 1).Value = Cells(i, 2).Value
    If IsEmpty(Sheets("Sheet1").Cells(i, "a").Value) = True Then Sheets("Sheet1").Cells(i, 1).Value = Cells(i, 2).Value
Next i
user_101110120

Loop Through the Cells of a Column

  • All three versions do the same and are about equally efficient.
Option Explicit

Sub FillEmptiesConstants()
    
    Const wsName As String = "Sheet1" ' Worksheet Name
    Const fRow As Long = 2 ' First Row
    Const lrCol As String = "BU" ' Last Row Column
    Const lCol As String = "A" ' Lookup Column
    Const dCol As String = "A" ' Destination Column
    Const sCol As String = "B" ' Source Column
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, lrCol).End(xlUp).Row
    If lRow < fRow Then Exit Sub
    
    Dim r As Long
    For r = fRow To lRow
        If IsEmpty(ws.Cells(r, lCol)) Then
            ws.Cells(r, dCol).Value = ws.Cells(r, sCol).Value
        End If
    Next r
    
End Sub

Sub FillEmptiesSimple()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
    Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, "BU").End(xlUp).Row
    If lRow < 2 Then Exit Sub
    
    Dim r As Long
    For r = 2 To lRow
        If IsEmpty(ws.Cells(r, "A")) Then
            ws.Cells(r, "A").Value = ws.Cells(r, "B").Value
        End If
    Next r
    
End Sub

Sub FillEmptiesSimpleWith()

    With ThisWorkbook.Worksheets("Sheet1")

        Dim lRow As Long: lRow = .Cells(.Rows.Count, "BU").End(xlUp).Row
        If lRow < 2 Then Exit Sub

        Dim r As Long
        For r = 2 To lRow
            If IsEmpty(.Cells(r, "A")) Then
                .Cells(r, "A").Value = .Cells(r, "B").Value
            End If
        Next r

    End With

End Sub
user_98140690
Related