I keep getting Type Mismatch, setting column type to Integer

Per suggestions, I set the columns I'm using to a Const reference. This works as long as I don't declare a column as an Integer or Long. Integer or Long returns a "compile Error, type mismatch."

When I comment out any Integer or Long columns (Plt_PltNum, Proj_1stPlate, and Proj_LastPlate) and just reference the range in the loop, the code does run, although it does not return the information I need.

I suspect that VB isn't treating the integer columns as integers, hence the >= and <= conditions don't really work.

Sub ProjectPlateLoops()

    'define workbooks and worksheets
    Dim wb As Workbook, ws As Worksheet, PlateSheet As Worksheet, ProjSheet As Worksheet
    Dim pltNum As Integer
    Dim lrProj As Long, lrPlate As Long, i As Long, j As Long
    Dim Farm As String, Batch As String
    Dim projcode
    Dim pltprojcode
    
    Set wb = ActiveWorkbook 'or ActiveWorkbook?
    Set ws = ActiveSheet
    Set PlateSheet = wb.Worksheets("Plate_Analysis")
    Set ProjSheet = wb.Worksheets("Project_Analysis")
    
    'last rows in Project Analysis/Plate sheets (call separate function for this)
    lrProj = LastOccupiedRow(ProjSheet)
    lrPlate = LastOccupiedRow(PlateSheet)
    
    'column constants
    ' Plate Sheet
    'Const Plt_PltNum As Long = "E" ' plate number on PltSheet
    Const Plt_ProjID_Col As String = "F" ' proj code on PltSheet
    Const Plt_Batch_Col As String = "G" ' batch on PltSheet
    Const Plt_Farm_Col As String = "H" ' farm name on PltShee
    ' Proj Sheet
    Const Proj_ProjID_Col As String = "F" ' proj code on ProjSheet
    Const Proj_Farm_Col As String = "D" ' farm name on ProjSheet
    Const Proj_Batch_Col As String = "E" ' batch on ProjSheet
    'Const Proj_1stPlate As Integer = "J" ' first plate # in batch from ProjSheet
    'Const Proj_LastPlate As Integer = "L" ' last plate # in batch from ProjSheet

    
    ' Create Loop; if 3 conditions are met, then print Batch and Farm from Project sheet to Plate sheet
    ' Condition 1: pltprojcode = projcode
    ' Condition 2 & 3: pltNum >= firstplatenum AND <= lastplatenum
    For i = 3 To lrPlate
        pltprojcode = PlateSheet.Cells(i, Plt_ProjID_Col).Value2
          Debug.Print "i: " & (i) & ", " & "Plate ProjCode from PlateSheet: " & (pltprojcode)
        pltNum = PlateSheet.Cells(i, "E").Value2
          Debug.Print "i: " & (i) & ", " & "Plate Number from PlateSheet: " & (pltNum)
        For j = 3 To lrProj
            If ProjSheet.Cells(j, Proj_ProjID_Col).Value2 = pltprojcode Then
            projcode = ProjSheet.Cells(j, Proj_ProjID_Col).Value2
                Debug.Print "ProjCode from ProjSheet: "; j & ", " & (projcode)
              Farm = ProjSheet.Cells(j, Proj_Farm_Col).Value2
                Debug.Print "j: " & (j) & ", " & "Farm: " & (Farm)
              Batch = ProjSheet.Cells(j, Proj_Batch_Col).Value2
                Debug.Print "j: " & (j) & ", " & "Batch: " & (Batch)
              On Error Resume Next
              End If
                If pltNum >= ProjSheet.Cells(i, "J").Value2 And _
                pltNum <= ProjSheet.Cells(i, "L").Value2 Then
                PlateSheet.Cells(i, Plt_Farm_Col).Value2 = Farm 'farm
                PlateSheet.Cells(i, Plt_Batch_Col).Value2 = Batch 'batch
            On Error Resume Next
           End If
        Next j
    Next i

End Sub

Function LastOccupiedRow(ws As Worksheet) As Long
    Dim f As Range
    Set f = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    If Not f Is Nothing Then LastOccupiedRow = f.Row
End Function

Interestingly, if I run the code now, the only Debug.Print values that show up are in the "i" loop. If I comment out the i loop Debug.Print, the "j" debug values will print, but only the same lines over and over again, as if only one line of data actually matches all the criteria.

Answers

It looks like you're encountering several issues in your VBA code. Let's address them one by one:

Column Constants with Integer or Long Data Types: You're correct that declaring column constants with Integer or Long data types and then assigning them string values will result in a type mismatch error. You should declare them as String instead.
Loop Variables: In your inner loop (For j = 3 To lrProj), you're using the loop variable j to access cells in the ProjSheet, but you're mistakenly using i instead of j in some places. Make sure to use the correct loop variable.
Condition for Plate Number Range: You're checking the plate number range condition using ProjSheet.Cells(i, "J").Value2 and ProjSheet.Cells(i, "L").Value2, where i refers to the loop variable of the outer loop. It should be j to correspond to the inner loop.

Here's the corrected version of your code:

For i = 3 To lrPlate
    pltprojcode = PlateSheet.Cells(i, Plt_ProjID_Col).Value2
    pltNum = PlateSheet.Cells(i, "E").Value2
    For j = 3 To lrProj
        If ProjSheet.Cells(j, Proj_ProjID_Col).Value2 = pltprojcode Then
            projcode = ProjSheet.Cells(j, Proj_ProjID_Col).Value2
            Farm = ProjSheet.Cells(j, Proj_Farm_Col).Value2
            Batch = ProjSheet.Cells(j, Proj_Batch_Col).Value2
            Exit For ' Exit the inner loop once a match is found
        End If
    Next j
    If pltNum >= ProjSheet.Cells(j, "J").Value2 And _
       pltNum <= ProjSheet.Cells(j, "L").Value2 Then
        PlateSheet.Cells(i, Plt_Farm_Col).Value2 = Farm 'farm
        PlateSheet.Cells(i, Plt_Batch_Col).Value2 = Batch 'batch
    End If
Next i

This code should properly loop through the rows in both sheets, match the project code, and set the farm and batch values on the plate sheet based on the plate number range condition. Make sure to adjust any other parts of your code as needed.

I keep getting Type Mismatch, setting column type to Integer

Hot

Latest