"Cells" function for sheet 1 not working

[QuiksilverHg]

I'm trying to see if a group of cells (on another sheet) is blank as a condition before continuing. I figured out how to check it correctly when I list the cells as a range, but it will not work with the cells command unless I have the sheet selected. Any ideas?

This is what I would like to do:

If Application.CountBlank(Sheets(1).Range(Cells(55, scopy), Cells(59, scopy))) < 5 Then

Where scopy is a variable which will change depending on which column I need to copy from. I am trying to do this entire program without switching back and forth between sheets. Sheet 5 is selected the entire time.

I've tried breaking it down to make sure I had the formatting right for the cells, but these 'test lines' do not work either:
Sheets(1).Cells(55, scopy).Select
Sheets(1).Range(Cells(55, scopy), Cells(59, scopy)).Select

Again it will work if I have sheet 1 selected, but not while sheet 5 is selected. Anyone know what I'm doing wrong or if there's another way to do this?

 

[Tom Urtis]

Two parts to your question.

First, qualify the Cells reference with the parent sheet, example, instead of this you are using which does not work:

If Application.CountBlank(Sheets(1).Range(Cells(55, scopy), Cells(59, scopy))) < 5 Then

Instead use:

With Sheets(1)
If WorksheetFunction.CountBlank(Sheets(1).Range(.Cells(55, scopy), .Cells(59, scopy))) < 5 Then
MsgBox "Yes, less than 5 cells are blank"
Else
MsgBox "No, not less than 5 are blank"
End If
End With

Note that I am using WorksheetFunction instead of Application which will make your code more efficient when dealing with VBA functions.

The second item is how to go to a specific range on a sheet when executing the code line to do so from another sheet.

Instead of this which is a common mistake and does not work:

Sheets(1).Cells(55, scopy).Select
or
Sheets(1).Range(Cells(55, scopy), Cells(59, scopy)).Select

Utilize GoTo, again, qualifying the Cells object like so:

With Sheets(1)
Application.Goto Range(.Cells(55, scopy), .Cells(59, scopy))
End With

 

[QuiksilverHg]

Tom,

Thank-you for that reply, I will give it a shot.

Could you give me a short explanation on what the "application." portion of the command actually means, and tell me how that contrasts with the "WorksheetFunction." command.

I do much better if I can figure out why I am doing something vice just copying the code.

 

[QuiksilverHg]

I'm concerned with having an open-ended with for the entire if statement.

I am going to insert many lines of code dealing mostly with the main sheet (5) in the if statement. Wouldn't putting the "with sheets(1)" line in there confuse the program when I try to interact with sheet 5?

 

[Tom Urtis]

Application resfers to the highest level object in Excel's object model, that being the Application of Excel itself. There is nothing higher than Application, meaning its collection of methods and properties, and all of their methods and properties, is the largest and hence most extensive for VBA to compile. What you *could* do is to write
Application.WorksheetFunction.CountBlank...
but as long as you are doing that, you could chop off the Application qualifier because VBA would know you are working in Excel unless you specified otherwise such as if you are working with the Word or PowerPoint app, which in this case you are not.

To demonstarte further, go into the VBE, and from the menu bar click Tools > Options > Edit tab and select Auto List Members. Click OK, and then in the Immediate window or in some module, type Application and the dot character and you will see the Intellisense drop-down list for the Application object. Now try doing that with
WorksheetFunction.
and the VBA worksheet functions will appear in the drop-down list, quite a few but not as many for VBA to sift through as it would by using just Application.

You can also look at the Object Browser by hitting F2 in the VBE and seeing the Application object's members, vs other objects' members.

I built a project some years ago using Application for worksheet functions and then changed the code to directly specify WorksheetFunction, and tested the run time difference, which was measurable.

 

[Tom Urtis]

I'm concerned with having an open-ended with for the entire if statement.

I am going to insert many lines of code dealing mostly with the main sheet (5) in the if statement. Wouldn't putting the "with sheets(1)" line in there confuse the program when I try to interact with sheet 5?

Well I don't know because you have not talked about the extent of your macro. If Sheet5 is active when that part of the code is executing then you don't ahve to worry about qualifying it. If neither Sheets(1) nor Sheet5 are active then you can still use a With structure for Sheet5, just don't include Sheet5 in the With structure of Sheets(1) by not placing a dot in front of Sheet5, example, you would not want to do this:

With .Sheet5
yada yada


And you don't need to have an open-ended With in the first place, you could terminate it as I did, and maybe have Boolean variables for True or False thereafter to determine what you will be doing. Hard to answer your concern beyond that because you have not given details about the scope of your code.