VBA is stepping around my for loop without executing it

Question

I'm trying to make a unique ID for each sample in a variable length data set. to do this I want to use part of two strings of data called the Name and Sample Type. I want i to go down each row in the column and take the pieces of each string and put them together, however when I step through the loop it never goes into my loop, only around it. can someone tell me why?

Sheets("Data").Activate
setlastrow = Sheets("Data").Range("b5000").End(xlUp).Row
setlastcol = Sheets("Data").Cells(5, Columns.Count).End(xlToLeft).Column 'this is still assuming that row 5 has the header in it

colname = Rows(5).Find("Name", LookAt:=xlWhole).Column  ' this can be repeated for any other columns we want to asign values to. These variables will make the rest of this much easier
colSampleText = Rows(5).Find("Sample Text", LookAt:=xlWhole).Column

For i = 6 To lastrow
    Sheets("Data").Range(Cells(i, 1)) = workbookfunction.if(workbookfunction.CountIf(Range(Cells(6, colname), Cells(i, colname)), Cells(i, colname)) < 10, "0", "") & workbookfunction.CountIf(Range(Cells(6, colname), Cells(i, colname)), Cells(i, colname) & "-" & Left(Cells(i, colSampleText), 5))
    'this should find the unique identifying infomation for each sample and analyte
Next i

Answer 1

Here is a variation of your code; I've modified your code to set your two columns using Find, loop through each cel in the range(using the current row), set varcnt to count the number of matches, defined the first 5 letters of value in the Sample Text column as str, and used a basic If statement to write the combined the unique ID into the first column.

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Data")
Dim lRow As Long: lRow = ws.Range("b5000").End(xlUp).Row

Dim dataCol As Long: dataCol = ws.Range("A5:J5").Find(What:="Name", LookIn:=xlValues, lookat:=xlWhole).Column
Dim smplTextCol As Long: smplTextCol = ws.Range("A5:J5").Find(What:="Sample Text", LookIn:=xlValues, lookat:=xlWhole).Column

    For Each cel In ws.Range(ws.Cells(6, dataCol), ws.Cells(lRow, dataCol))

        Dim varcnt As Long: varcnt = Application.WorksheetFunction.CountIf(ws.Range(ws.Cells(6, dataCol), ws.Cells(cel.Row, dataCol)), ws.Cells(cel.Row, dataCol).Value)
        Dim str As String: str = Left(ws.Cells(cel.Row, smplTextCol).Value, 5)

        If varcnt < "4" Then
            ws.Cells(cel.Row, 1).Value = "0" & "-" & str

        Else
            ws.Cells(cel.Row, 1).Value = "" & "-" & str
        End If

    Next cel

Answer 2

You have an error with the end part of your For...Next statement.

From the code you have posted, LastRow is not explicitly declared anywhere, so when you run your code, LastRow is created as Type Variant with a default Empty value.

Consider this code:

Sub LoopTest()

Dim DeclaredVariable As Long
Dim i As Long

DeclaredVariable = 10

For i = 1 To UnDeclaredVariable
    Debug.Print i & " UnDeclaredVariable"
Next i

For i = 1 To DeclaredVariable
    Debug.Print i & " DeclaredVariable"
Next i

End Sub

The output in the immidiate window would be:

1 DeclaredVariable
2 DeclaredVariable
3 DeclaredVariable
4 DeclaredVariable
5 DeclaredVariable
6 DeclaredVariable
7 DeclaredVariable
8 DeclaredVariable
9 DeclaredVariable
10 DeclaredVariable

This shows us that the loop for the UnDeclaredVariable has not been entered - AND this is due to the fact the end part of the For...Next loop is Empty (The default value of a Variant data type) so there is no defined end for the loop to iterate to.

NB To be more precise, the issue is that the UnDeclaredVariable has no (numeric) value assigned to it - if you assign a value to a variable that is undeclared it becomes a data type Variant/<Type of data you assigned to it> for example UnDeclaredVariable = 10 makes it a Variant/Intigertype .

The reason why it steps over the loop and doesn't throw an error is because you don't have Option Explicit at the top of your code module (or Tools > Options > "Require Variable Declaration" checked) which means the code can still run with undeclared variables (this includes if you spell a declared variable incorrectly).

If you add Option Explicit to the top of your code module:

Option Explicit

Sub LoopTest()

Dim DeclaredVariable As Long
Dim i As Long

DeclaredVariable = 10

For i = 1 To UnDeclaredVariable
    Debug.Print i & " UnDeclaredVariable"
Next i

For i = 1 To DeclaredVariable
    Debug.Print i & " DeclaredVariable"
Next i

End Sub

You would get the following error:

Compile Error: 
Variable not defined

This is a fantastic example of why Option Explicit is an important declaration to make in all code modules.

Answer 3

There are two major errors in your code - plus a minor one. One is structural. You declare non of the variables you use. It's like saying, "Since I don't know how to drive I might as well close my eyes as we speed along". It's not without logic but does little toward getting you to where you want to go.

The other is in the mix-up between the worksheet function you want VBA to execute and the one you wish to assign to a cell to be executed by Excel. Writing a complex formula to a cell is more difficult than getting VBA to calculate a complex formula. For the method, if you want to create a formula in VBA you should assign it to a string first, like MyFormula = "=COUNTIF(D6:D12, "MyName")" and then, after testing it, assign that string to the cell's Formula property, like Cells(R, ClmName).Formula = MyFormula". In the code below I chose to let VBA do the calculating. Since it isn't entirely clear what you want (faulty code is never a good way to show what you intend!) please revise it. It's easier in VBA than in a worksheet function.

Private Sub Test()

    Dim LastRow As Long
    Dim LastClm As Long
    Dim ClmName As Long                 ' R use "col" for color, "clm" for column
    Dim ClmSampleText As Long
    Dim CountRng As Range
    Dim Output As Variant
    Dim R As Long                       ' R use R for row, C for column

    Sheets("Data").Activate
    LastRow = Sheets("Data").Range("b5000").End(xlUp).Row
    ' this is still assuming that row 5 has the header in it
    LastClm = Sheets("Data").Cells(5, Columns.Count).End(xlToLeft).Column

    ' this can be repeated for any other columns we want to asign values to.
    ' These variables will make the rest of this much easier
    ClmName = Rows(5).Find("Name", LookAt:=xlWhole).Column
    ClmSampleText = Rows(5).Find("Sample Text", LookAt:=xlWhole).Column

    For R = 6 To LastRow
        'this should find the unique identifying infomation for each sample and analyte
        Set CountRng = Range(Cells(6, ClmName), Cells(R, ClmName))
        Output = WorksheetFunction.CountIf(CountRng, Cells(R, ClmName).Value)
        If Output < 10 Then Output = 0
        Cells(R, 1).Value = CStr(Output) & "-" & Left(Cells(R, ClmSampleText).Value, 5)
    Next R
End Sub

The "minor" mistake stems from your lack of understanding of the Cell object. A cell is a Range. It has many properties, like Cell.Row and Cell.Column or Cell.Address, and other properties like Cell.Value or Cell.Formula. The Value property is the default. Therefore Cell is the same as Cell.Value BUT not always. In this example, by not thinking of Cell.Value you also overlooked Cell.Formula, and by placing Cell into a WorksheetFunction you confused VBA as to what you meant, Cell the Value or Cell the Range. With all participants confused the outcome was predictable.

The recommendation is to always write Cell.Value when you mean the cell's value and use Cell alone only if you mean the range.