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 · Answer

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

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

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

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   lt  gt     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  lt  gt     Then              write to adjacent cell             cell Offset 0  1  Value    My Text          End If     Next End Sub

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

Examples related to vba

Examples related to excel