Find the first open cell in a row

S

S.H.

I am trying to create a macro to update a spreadsheet
automatically. My problem is that I want the new data to
go to the first open cell in row 35 every time I run the
macro. What command will find that cell?
Thanks in advance for your help.
S.H.
 
G

Guest

Hi Try This...

Sub GetFirstCell()
Dim x as integer
x = 1
Do Until cells(35,x) = ""
x = x + 1
Loop
Msgbox "First Empty Cell is: " & x
End Sub
 
H

Harald Staff

Hi SH

Try

Sub test()
Dim E As Range
Set E = Rows(35).Find(What:="")
MsgBox E.Address
End Sub
 
A

Alan Beban

Doesn't work if Cell E1 is blank.

Alan Beban

Harald said:
Hi SH

Try

Sub test()
Dim E As Range
Set E = Rows(35).Find(What:="")
MsgBox E.Address
End Sub
Click to expand...
 
D

Dave Peterson

How about:

Option Explicit
Sub test()
Dim E As Range
With Rows(35)
Set E = .Find(What:="", after:=.Cells(.Cells.Count))
End With
If E Is Nothing Then
MsgBox "no blank cells"
Else
MsgBox E.Address
End If
End Sub
 
L

Leo Heuser

Another option:

Sub FindFirstBlank()
'Leo Heuser, 6 Nov 2003
Dim FirstBlank As Range

Set FirstBlank = _
ActiveSheet.Rows(35).SpecialCells(xlCellTypeBlanks).Cells(1, 1)

End Sub


--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.
 
H

Harald Staff

Alan Beban said:
Doesn't work if Cell E1 is blank.
Click to expand...

Or A35 ? <g> Ok, right you are Alan.

Sub test()
Dim E As Range
Set E = Cells(35, 1)
If E.Value <> "" Then Set E = Rows(35).Find(What:="")
MsgBox E.Address
End Sub
 
H

Harald Staff

after:=.Cells(.Cells.Count))

Nice trick Dave. Thanks.

Best wishes Harald
Followup to newsgroup only please
 
S

S.H

In followup to the message yesterday. I appreciate your
help. Two of these work well but I guess I should have
stated my question differently since now I would like to
select the open cell in row 35. Thank you greatly for all
your help.
 
A

Alan Beban

Open cell? What's that?

Alan Beban

S.H said:
In followup to the message yesterday. I appreciate your
help. Two of these work well but I guess I should have
stated my question differently since now I would like to
select the open cell in row 35. Thank you greatly for all
your help.
Click to expand...
 
S

S.H.

I want to go to the first blank cell in row 35 so that I
can insert data into it each week. The reason I do this is
to keep a ongoing 12 week average of the data I report on.
 
P

Paul B

how about this modified code that Dave posted

Option Explicit
Sub test()
'go to the first open cell in row 35
Dim E As Range
With Rows(35)
Set E = .Find(What:="", after:=.Cells(.Cells.Count))
End With
If E Is Nothing Then
MsgBox "no blank cells"
Else

'will show the address
'MsgBox E.Address

'this will select the cell
E.Select
End If
End Sub

--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
S

S.H.

This works great to give me a message stating the blank
cell but, what do I add this statment so my cursur will go
there? I know it is something real easy I just can't get
it to work.
Sub test()
Dim E As Range
With Rows(35)
Set E = .Find(What:="", after:=.Cells(.Cells.Count))
End With
If E Is Nothing Then
E.Select
Else
MsgBox E.Address
End If
End Sub
 
H

Harald Staff

I see. I hope. <g>. Replace
MsgBox E.Address
with
E.Select
or/and something like
E.Value = "I am entering my lyrics"
in any of the provided solutions.
 
Top