Macro that insterts text if columns emptly

[spyldbrat]

I need a macro that will look at column A and if it's blank AND column B is NOT empty, then it will return value of "OFFICE". I need this to go to the last row. I tired to modify existing ones I found on the internet, but I was not successful. Thanks.

 

[mumps]

Try:

Sub fillCell()
    Application.ScreenUpdating = False
    Dim bottomA As Long
    bottomA = Range("A" & Rows.Count).End(xlUp).Row
    Dim rng As Range
    For Each rng In Range("A1:A" & bottomA)
        If rng = "" And rng.Offset(0, 1) <> "" Then
            rng = "OFFICE"
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[JLGWhiz]

Sub t()
Dim c As Range
With ActiveSheet
    For Each c In .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
        If c = "" And c.Offset(, 1) <> "" Then c = "Office"
    Next
End With
End Sub

 

[spyldbrat]

The Macro isn't working right. I think if "Office" is already populated at all within col A it's not putting the text there in the remaining blank cells. It also looks like it's only putting the text on the first blank cell but leaving all other cells in that column blank.

Dim c As Range
With ActiveSheet
For Each c In .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
If c = "" And c.Offset(, 1) <> "" Then c = "Office"
Next
End With
End Sub

 

[mumps]

I tested the macro I suggested on some dummy data and it worked properly. It looks like JLGWhiz's macro does the same thing so it should also work. Can you post a sample of what your data looks like?

 

[spyldbrat]

The state col is where "office" should go.

If I run it as is, it will put office on row 1 only.

State Customer Name Month Actual Hrs Pay Amount Bill Amount
Test Sep-2017 0.25 4.25 127.50
Ny Test Sep-2017 0.25 0.25 5.75
ny Test Sep-2017 0.25 0.25 5.75
pa Test Jul-2017 0.50 11.50
pa Test Aug-2017 0.50 0.50 11.50
Test Sep-2017 0.50 11.50
Test Sep-2017 0.75 17.25
Test Sep-2017 0.75 0.75 17.25
Test Aug-2017 1.00 23.00
Test Sep-2017 1.00 23.00
Test Sep-2017 1.25 1.25 28.75
Test Sep-2017 1.50 34.50
Test Sep-2017 1.50 1.50 34.50
Test Aug-2017 2.50 57.50
Test Sep-2017 15.75 15.75 362.25
Test Sep-2017 16.50 16.50 379.50
Test Sep-2017 18.00 414.00
Test Sep-2017 18.00 18.00 414.00

 

[Fluff]

Try this mod to the code supplied by mumps

Sub fillCell()
    Application.ScreenUpdating = False
    Dim bottomA As Long
    bottomA = Range("[COLOR=#ff0000]B[/COLOR]" & Rows.Count).End(xlUp).Row
    Dim rng As Range
    For Each rng In Range("A1:A" & bottomA)
        If rng = "" And rng.Offset(0, 1) <> "" Then
            rng = "OFFICE"
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

Both codes supplied were looking in the wrong column to find the last used row.

 

[JLGWhiz]

This might be an even better approach.

Sub t()
Dim c As Range, lr As Long
With ActiveSheet
    lr = .Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    For Each c In .Range("A2:A" & lr)
        If c = "" And c.Offset(, 1) <> "" Then c = "Office"
    Next
End With
End Sub

 

[spyldbrat]

It works!!!! Thank you so much!

 

[JLGWhiz]

It works!!!! Thank you so much!

Thanks for the feedback,
Regards, JLG