INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Code to go to first blank cell on another sheet

Code to go to first blank cell on another sheet

(OP)
Currently I have basic code to go to another sheet in the current work & code to find the first blank cell on a worksheet. I am trying to combine the two codes, so when selected it goes to the first blank cell on another sheet. Here is the code to go to another sheet it is located on the "TOC" worksheet:

CODE --> VBA

Dim Msg As String, Ans As Variant
     
    Msg = "Would you like to the Vocational Assistance Worksheet?"
     
    Ans = MsgBox(Msg, vbYesNo)
     
    Select Case Ans
         
    Case vbYes
Sheets("VOC_ASST").Select
End Select
End Sub 
Here is the code to find the first blank cell:

CODE --> VBA

Sub Find_Empty_Cell()
'Macro_FIND_NEXT_BLANK_SPACE()
If IsEmpty(ActiveCell.Offset(1)) Then
   ActiveCell.Offset(1).Select
Else
   ActiveCell.End(xlDown).Offset(1).Select
End If
End Sub 
I am trying to find the first blank cell in column A on the "VOC_ASST" worksheet.
If anyone has a better idea of how to accomplish what I am trying to accomplish, I am open to suggestions.

RE: Code to go to first blank cell on another sheet

Hi,

I'd change Sheets("VOC_ASST").Select to...

CODE

'
   With Sheets("VOC_ASST")
      .Activate
      .Cells(1, 1).Select
   End With 
...in order to actually start at A1 rather than whatever cell was active the last time you were on that sheet.

Now on the Find_Empty_Cell. Isn't it possible that, for instance, A1 is empty but A2 is not? So you're looking for an empty cell and assuming that the next cell in the column is empty?

You might consider going from the very last cell in the column, upward...

CODE

'
   With Sheets("VOC_ASST")
      .Cells(.Cells.Rows.Count, 1).End(xlUp).offset(1).Select
   END WITH 

BTW, I think that Select and Activate are way overused. I very rarely use either unless it is to visually present the sheet to the user after everything else is done.
FAQ707-4105: How Can I Make My Code Run Faster?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Code to go to first blank cell on another sheet

(OP)
Skip. A big Keystone Thank You to you for your help. thumbsup2 thumbsup2 I did take your advice & change from select to.... I did try your suggestion for locating the next blank cell, however it went several rows past the last row in the table. I worked on the code & came up with the following code.

CODE --> VBA

Sub Asst()

Dim Msg As String, Ans As Variant
     
    Msg = "Would you like to the Vocational Assistance Worksheet?"
     
    Ans = MsgBox(Msg, vbYesNo)
     
    Select Case Ans
         
    Case vbYes
    'Selects the appropiate worksheet.
With Sheets("VOC_ASST")
      .Activate
      .Cells(1, 1).Select
   End With
   'Finds the next blank cell in the cloumn.
   'If there is no header, change the range to A1
Range("A4").End(xlDown).Offset(1, 0).Select
   End Select
End Sub 

RE: Code to go to first blank cell on another sheet

Quote:

...however it went several rows past the last row in the table.

Are you ABSOLUTELY sure that it went past "the last row in the table?"

I'd wager that there is actually data there! Y'know, a SPACE character is data.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Code to go to first blank cell on another sheet

(OP)
yes. It went down several rows where I had raw data.

RE: Code to go to first blank cell on another sheet

...and on your code...

CODE

Sub Asst()

    Dim Msg As String, Ans As Variant
     
    Msg = "Would you like to go to the Vocational Assistance Worksheet?"
     
    Ans = MsgBox(Msg, vbYesNo)
     
    Select Case Ans
         
        Case vbYes
        'Selects the VOC_ASST worksheet.
            With Sheets("VOC_ASST")
               .Activate
        'Finds the next blank cell in the column.
        'If there is no header, change the range to A1
'what happens to rows 2 & 3???             <<<<<
'Why wouldn't this table have a header???  <<<<<

' this range is on VOC_ASST, is it not?    <<<<<
                .Range("A4").End(xlDown).Offset(1, 0).Select
            End With
    End Select
End Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Code to go to first blank cell on another sheet

(OP)
what happens to rows 2 & 3???
Nothing, this is used to search only 1 column.

Why wouldn't this table have a header???
Must tables do have headers, But I was trying to allow for the rare occasion when there was no header.

This range is on VOC_ASST, is it not?
Correct. It is.

RE: Code to go to first blank cell on another sheet

I said nothing about other columns.

In column 1 what's in rows 2 & 3?

Quote:

yes. It went down several rows where I had raw data.

What? Data below your table? Very unconventional! Not a best and accepter practice.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Code to go to first blank cell on another sheet

(OP)
Agreed. Prior to working with code, I was using formulas. I just adapted what I had started with, rather do over. The old saying "If I had known then, what I know now", I would have done things differently.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close