VBA - If a cell in column A is not blank the column B equals

Question

I m looking for some code that will look at Column A and as long as the cell in Column A is not blank  then the corresponding cell in Column B will equal a specific value   So if Cell A1  lt      then Cell B1 Value    MyText  And repeat until a cell in Column A is blank or empty   To add a little more clarification  I have looked through the various loop questions asked and answered here   They were somewhat helpful   However  I m unclear on how to get the loop to go through Column A to verify that each cell in Column A isn t blank AND in the corresponding cell in Column B  add some text that I specify   Also  this will need to be part of a VBA macro and not part of a cell formula such as  IF

User · Accepted Answer

If you really want a vba solution you can loop through a range like this:

Sub Check()
    Dim dat As Variant
    Dim rng As Range
    Dim i As Long

    Set rng = Range("A1:A100")
    dat = rng
    For i = LBound(dat, 1) To UBound(dat, 1)
        If dat(i, 1) <> "" Then
            rng(i, 2).Value = "My Text"
        End If
    Next
End Sub

*EDIT*

Instead of using varients you can just loop through the range like this:

Sub Check()
    Dim rng As Range
    Dim i As Long

    'Set the range in column A you want to loop through
    Set rng = Range("A1:A100")
    For Each cell In rng
        'test if cell is empty
        If cell.Value <> "" Then
            'write to adjacent cell
            cell.Offset(0, 1).Value = "My Text"
        End If
    Next
End Sub

User · Answer

A simpler way to do this would be   Sub populateB    For Each Cel in Range  A1 A100       If Cel value  lt  gt     Then Cel Offset 0  1  value    Your Text  Next  End Sub

User · Answer

Another way  Using Formulas in VBA   I guess this is the shortest VBA code as well   Sub Sample       Dim ws As Worksheet     Dim lRow As Long      Set ws   ThisWorkbook Sheets  Sheet1        With ws         lRow    Range  A   amp   Rows Count  End xlUp  Row           Range  B1 B   amp  lRow  Formula     If A1 lt  gt        My Text                   Range  B1 B   amp  lRow  Value    Range  B1 B   amp  lRow  Value     End With End Sub

User · Answer

Use the function IF     IF   logical test  value if true  value if false

[vba] VBA - If a cell in column A is not blank the column B equals

Examples related to vba

Examples related to excel