Code to go to first blank cell on another sheet

[Frank Melan]

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:

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:

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.

 

[SkipVought]

Hi,

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

'
   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...

'
   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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Frank Melan]

Skip. A big Keystone Thank You to you for your help. 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.

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

 

[SkipVought]

...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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Frank Melan]

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

 

[SkipVought]

...and on your 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
'[b]what happens to rows 2 & 3???             <<<<<[/b]
'[b]Why wouldn't this table have a header???  <<<<<[/b]

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Frank Melan]

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.

 

[SkipVought]

I said nothing about other columns.

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

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Frank Melan]

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.