2 below first entry in row
 

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?

--
David

2 below first entry in row
 

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

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=26154

2 below first entry in row
 

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.


---
Regards,
Norman



"David" wrote in message
...
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?

--
David

2 below first entry in row
 

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


David wrote:

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?

2 below first entry in row
 

David wrote:

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

2 below first entry in row
 

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

2 below first entry in row
 

Alan Beban wrote in message ...

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.

---
David