If a cell in column A is not blank then column B equals

[uvela]

Hello,

I'm having trouble with something that seems pretty straight forward.

I have a worksheet with Dates and Blank cells in column A and would like to put "Open" if column A has a Date and "Closed" if column A has a Blank in column B.

I'm using the following code but am getting a 400 error.

Sub openclose()

    Dim rng As Range
    Dim i As Long

    'Set the range in column A you want to loop through
    Set rng = Range("Z1:Z" & LastRow2)
    For Each cell In rng
        'test if cell is empty
        If cell.Value <> "" Then
            'write to adjacent cell
            cell.Offset(0, -1).Value = "Closed"
        Else
            cell.Offset(0, -1).Value = "Open"
        End If
    Next

End Sub

Any help or suggestions would be appreciated.

Thanks.

 

[gordsky]

is there code you haven't posted? You dim i as long but then its not referred to in the code.
You also have a reference to lastrow2 but dont appear to have defined it anywhere

 

[johnnyL]

Set rng = Range("Z1:Z" & LastRow2)

Your code is checking column Z

 

[uvela]

is there code you haven't posted? You dim i as long but then its not referred to in the code.
You also have a reference to lastrow2 but dont appear to have defined it anywhe

Apologize, the dim i was leftover code.
Lastrow 2 is defined as

Lastrow2 = Range(Rows.count, "Z").End(xlUp).row

Set rng = Range("Z1:Z" & LastRow2)

Your code is checking column Z

Yes, apologize for that, when I mentioned column A and B I meant column Z and X respectively.

 

[Joe4]

Lastrow2 = Range(Rows.count, "Z").End(xlUp).row

That is not valid code.

It should be:

Lastrow2 = Cells(Rows.Count, "Z").End(xlUp).Row

or

Lastrow2 = Range("Z" & Rows.Count).End(xlUp).Row

 

[johnnyL]

Apologize, the dim i was leftover code.
Lastrow 2 is defined as

Lastrow2 = Range(Rows.count, "Z").End(xlUp).row

Also, do you have the 'Lastrow2=' in a different subroutine?

 

[Joe4]

Also, do you have the 'Lastrow2=' in a different subroutine?

To add on to what Johnny asked, if "LastRow2" is being set somewhere else in another procedure, unless you have declared "LastRow2" to be a global variable and already run the code to set its value before running this code you posted, it won't work.

If it is NOT a global variable. you should define it and do the calculation in this procedure you posted BEFORE you try using it in the code.

 

[uvela]

Thank you for your replies it helped me troubleshoot the issue.

 

[Joe4]

You are welcome.
Glad we could help!