Application.WorksheetFunction.Countif not working

Question

The line of code in bold green is not working. As you can see, I have checks trying to troubleshoot the issue using ACTIVIATE, SELECT and MSGBOX to make sure I am looking at the correct range. Other than the specific range indicated, this line of code is copied and pasted into this routine and it works in the other routine that I copied it from. I have not completed the coding in the routine because I cannot get the lastcol to work. The range is formatted as TEXT. I tested it with the NUMBER format but it did not change anything.

I am trying to find the last column with data in order to add one to get to the first blank column. In the screenshot below the code, C3:L3 has data and M3:AZ3 are blank.

RESULTS: <br><br>lastcol = 0 (immediately after the application.worksheetfunction.countif)<br><br>lastcol = 1 (coding: lastcol = lastcol + 1)

Private Sub CommandButton1_Click() <br> Dim lastcol As Integer <br> Dim module As Integer <br> Dim k As Integer <br> Dim rng As Range <br> <br> Unload Me <br> Worksheets("Ch_Quiz").Activate <br>'Determine first blank column left to right in range <br> <br> Worksheets("Ch_Quiz").Range("C3:AZ3").Select <br> lastcol = Application.WorksheetFunction.CountIf(Range("C3:AZ3"), ">=0") 'Last column with data <br> MsgBox lastcol <br> lastcol = lastcol + 1 'First blank column <br> MsgBox lastcol <br> <br>'Determine which optionbuttion was selected <br> For k = 1 To 7 <br> If Controls("obModule" & k).Value = True Then <br> module = k <br> k = 7 <br> End If <br> Next k <br> MsgBox module <br> <br>'Place data <br> Set rng = Worksheets("Ch_Quiz").Range("B3") <br> <br>'More coding to be done here <br> <br> <br> <br> <br>theend: <br>End Sub

Answer 1

In your screenshot, the last non-blank column in row 3 is column L, that is the 12th column. So the result 12 is correct.

If you still have problems:

Could you create a stripped-down copy of the workbook demonstrating the problem (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

Answer 2

Sorry for taking so long to get back. Got busy. This formula renders the same answer as lastcol = Range("BA3").End(xltoLeft).Column does.

lastcol should be 10, but it equals 12 instead. I have selected all the "empty cells in row 3 to be sure there is nothing in it. I also tried the "<>0" that you suggested and the answer was 49.

Answer 3

Does this work?

lastcol = Range("B3").End(xlToRight).Column

Answer 4

For some reason, lastcol = 12 when it should equal 10. I have deleted any unseen characters from the cells after column L and it still chooses 12.

Is there a way to look at it from left to right?

Answer 5

The condition ">=0" will only count numeric values.

Try "<>" as condition, or alternatively

lastcol = Range("BA3").End(xlToLeft).Column