asked on

Find a cell to use English to VBA?

Here is the english version of what I need in VBA.

Start in A3 of the active sheet, if it is blank, select it, if it is not, skip a column and check again until and blank cell is found.

Example: It does not pick A3 (not blank), next it does not select C3 (its not blank either, however next check E3 is blank, select that one to use.

Saqib Husain

Sub findblank()
If IsEmpty(Range("A3")) Then
Range("A3").Select
ElseIf IsEmpty(Range("B3")) Then
Range("B3").Select
Else
Range("A3").End(xlToRight).Offset(, 1).Select
End If
End Sub

Norie

Perhaps.

Cells(3, Columns.Count).End(xlToLeft).Offset(,1).Select

Open in new window

Or.

Cells(3,1).End(xlToRight).Offset(,1).Select

Open in new window

Saqib Husain

With fewer lines this one does looping

Sub findblank()
Dim cel
For Each cel In Range("3:3")
If IsEmpty(cel) Then cel.Select: Exit Sub
Next cel
End Sub

RWayneH

ASKER

Is this skipping a column? It is not just the next blank... I need a blank column between the checks.

Saqib Husain

Sub findblank()
Dim cel
For Each cel In Range("3:3")
if mod(cel.column,2)=1 then
If IsEmpty(cel) Then cel.Select: Exit Sub
Next cel
Next cel
End Sub

RWayneH

ASKER

if mod(cel.column,2)=1 then

failing

Norie

Do you want to select all the blank cells in row 3?

For Each BlankCell In Rows(3).SpecialCells(xlCellTypeBlanks)
    ' do stuff with BlankCell
    BlankCell.Select
Next BlankCell

Open in new window

Norie

Perhaps.

For I = 1 To Cells(3, Columns.Count).End(xlToLeft).Column Step 2
    If Cells(3, I).Value = "" Then
        Cells(3,I).Select
    End If
Next I

Open in new window

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RWayneH

ASKER

Thanks. -R-