• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • 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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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