• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

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.
0
RWayneH
Asked:
RWayneH
  • 4
  • 3
  • 3
1 Solution
 
Saqib Husain, SyedEngineerCommented:
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
0
 
NorieVBA ExpertCommented:
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

0
 
Saqib Husain, SyedEngineerCommented:
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
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
RWayneHAuthor Commented:
Is this skipping a column?  It is not just the next blank...  I need a blank column between the checks.
0
 
Saqib Husain, SyedEngineerCommented:
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
0
 
RWayneHAuthor Commented:
if mod(cel.column,2)=1 then

failing
0
 
NorieVBA ExpertCommented:
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

0
 
NorieVBA ExpertCommented:
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

0
 
Saqib Husain, SyedEngineerCommented:
Sorry that was excel format. This is the VBA format for mod

Sub findblank()
Dim cel
    For Each cel In Range("3:3")
    If cel.Column Mod 2 = 1 Then
        If IsEmpty(cel) Then cel.Select: Exit Sub
    End If
    Next cel
End Sub
0
 
RWayneHAuthor Commented:
Thanks. -R-
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now