2 below first entry in row

[David]

Been trying to solve this on my own, but can't get it right.

Row 3 contains numbers. I want a line that selects the cell 2 rows under
the first number (location varies month to month) in that row.

This does it for *last* number:

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

How to modify?

 

[Simon Lloyd]

Not entirely sure what you want but you could try :-

Range("A1").select
xlDown

or something like this, the xlDown will find the next empty cell i
that row.

Simo

 

[Norman Jones]

Hi David,

You cpuld use the SpecialCells method:

Sub test()
Dim rng As Range
On Error Resume Next
Set rng = Rows(3).SpecialCells(xlCellTypeConstants, _
xlNumbers)(1, 3)
On Error GoTo 0

If Not rng Is Nothing Then
MsgBox rng.Address
Else
' Do something else
End If
End Sub

If your numbers are formulae, change xlCellTypeConstants to
xlCellTypeFormulas. If your numbers could be constants or formulae, you
would need to build a union range.

 

[keepITcool]

3rd option
works with numbers AND text, and is reasonably fast

example..
FirstNonBlank(3).Offset(2,0).select


Function FirstNonBlank(rownum As Long) As Range
'Note: works on activesheet only
With Rows(rownum)
If IsEmpty(.Cells(1)) And Application.CountA(.Cells) <> 0 Then
Set FirstNonBlank = .Cells(1).End(xlToRight)
Else
Set FirstNonBlank = .Cells(1)
End If
End With
End Function


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Alan Beban]

Been trying to solve this on my own, but can't get it right.

Row 3 contains numbers. I want a line that selects the cell 2 rows under
the first number (location varies month to month) in that row.

This does it for *last* number:

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

How to modify?

Range("3:3").Find("*", Range("iv3"))(3, 1)

Alan Beban

 

[David]

Thanks to all who replied.
Since I like *simple* and there will always be at least one number to the
left of G3, I played with the results of recording Ctrl+LeftArrow and this
does what I want:

Range("G3").End(xlToLeft).Offset(2, 0).Select

 

[David Turner]

Range("3:3").Find("*", Range("iv3"))(3, 1)

At first I couldn't get this to work, getting an 'Expected: =' Compile
error. On a hunch, added .Select to it and it works fine. Thanks. Now
I have a choice.