Solved

Find a cell to use  English to VBA?

Posted on 2014-04-22
10
242 Views
Last Modified: 2014-04-24
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
Comment
Question by:RWayneH
  • 4
  • 3
  • 3
10 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40016057
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
 
LVL 33

Expert Comment

by:Norie
ID: 40016061
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40016062
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
 

Author Comment

by:RWayneH
ID: 40016092
Is this skipping a column?  It is not just the next blank...  I need a blank column between the checks.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40016102
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:RWayneH
ID: 40016116
if mod(cel.column,2)=1 then

failing
0
 
LVL 33

Expert Comment

by:Norie
ID: 40016120
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
 
LVL 33

Expert Comment

by:Norie
ID: 40016123
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
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 40016124
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
 

Author Closing Comment

by:RWayneH
ID: 40020425
Thanks. -R-
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now