How to go through each cell in table-CodePudding

I have a table that is used to schedule individuals for one of three specific tasks. I'm trying to work with VBA to automate creating appointments in outlook once the table has been filled out. The attached image shows a section of the table (I've abbreviated the individuals names, it would typically have their full name).

Assignment Table

Currently I'm trying to loop through each column (day) and look at each row (tech). If there is an assignment in the row it adds the tech name to an array. After looping through each row in the column it creates an appointment for all of the techs in each array.

Sub CreateAppointments_Click()

    Dim tech1(5) As String
    Dim techD(5) As String
    Dim tech2(5) As String

    Application.ScreenUpdating = False
    techRow = 6
    tbl = ActiveSheet.ListObjects(1)
    Set rng = ActiveSheet.Range(tbl)
    For Each Column In rng.Columns
        apntDay = apntDay   1
        For Each Row In Column
            techRow = techRow   1
            If Row = 1 Then
                tech1(tech1Int) = ActiveSheet.Cells(1, techRow).Value
                tech1Int = tech1Int   1
            End If
            If Row = 2 Then
                tech2(tech2Int) = ActiveSheet.Cells(1, techRow).Value
                tech2Int = tech2Int   1
            End If
            If Row = D Then
                techD(techDInt) = ActiveSheet.Cells(1, techRow).Value
                techDInt = techDInt   1
            End If
        Next Row
        Call createOutlookAppointment(tech1, tech2, techD, apntDay)
    Next Column
  
    Application.ScreenUpdating = True
    
End Sub

I must not be understanding the loop properly because I get a "Type: Mismatch" error at

If Row = 1 Then

What am I doing wrong? Is there a completely different/better way to handle this?

CodePudding user response：

As user10186832 mentioned, your variable "Row" is actually a range object.
I'm not sure if you declared your variables earlier and just didn't show that part, but it's always good practice to declare all variables AND to start your sub with an Option Explicit statement as seen below.
For the sake of clarity, I renamed variable Row to Cel as we will need to compare: If Cel.Row = 2 then... which makes more sense than If Row.Row = 2 then...
Also, since we've declared Cel to be a range object at the beginning, there's less chance of us mistaking it for a long value

Option Explicit
Sub CreateAppointments_Click()

    Dim tech1(5) As String
    Dim techD(5) As String
    Dim tech2(5) As String
    Dim tech1Int As Long
    Dim tech2Int As Long
    Dim techDInt As Long
    Dim TechRow As Long
    Dim ApntDay As Long
    Dim D As Long
    Dim Tbl As Object
    Dim Rng As Range    ' Total Range
    Dim Col As Range    ' Each column range
    Dim Cel As Range    ' Individual cell (For each cell in column)

    Application.ScreenUpdating = False
    
    TechRow = 6
    Set Tbl = ActiveSheet.ListObjects("TechTable")
    Set Rng = Tbl.Range
    For Each Col In Rng.Columns
        ApntDay = ApntDay   1
        For Each Cel In Col
            TechRow = TechRow   1
            If Cel.Row = 1 Then
                tech1(tech1Int) = ActiveSheet.Cells(1, TechRow).Value
                tech1Int = tech1Int   1
            End If
            If Cel.Row = 2 Then
                tech2(tech2Int) = ActiveSheet.Cells(1, TechRow).Value
                tech2Int = tech2Int   1
            End If
            If Cel.Row = D Then
                techD(techDInt) = ActiveSheet.Cells(1, TechRow).Value
                techDInt = techDInt   1
            End If
        Next Cel
        Call createOutlookAppointment(tech1, tech2, techD, ApntDay)
    Next Col
  
    Application.ScreenUpdating = True
    
End Sub

CodePudding user response：

Thank you Cameron Critchlow and user10186832 for pointing me in the right direction. I ended up figuring it out. In short I needed to do the following:

For Each Col In Rng.Columns
    For Each Cel in Col.Rows
        If Cel.Value = 1 Then