Referencing a column for later use in a loop in VBA

Question

I am attempting to create a VBA macro to import data from a RAW sheet into an array of sheets' tables using a SUMIFS function. This function needs to loop down the column for each site listed and set the cell's value according to the SUMIFS.

However, am having an issue and I believe it has to do with how I am referencing the column.

The column-finding portion should look for the column to the left of the column containing "Total" in Row 7 and then set preCol equal to that column number.

I am receiving Error 13: Type mismatch on preCol = .Find("Total", After:="OI7", LookIn:=xlValues).Offset(0, -1).Column which makes sense, but I cannot think of a method to find a column and then turn that into an integer based on that column's location.

Any advice or insight is greatly appreciated.

Option Explicit

Sub ImportFile()

    'Select import file
    On Error GoTo err
    Dim importFilePath As String
    Dim fileExplorer As FileDialog
    Set fileExplorer = Application.FileDialog(msoFileDialogFilePicker)

    With fileExplorer
        .AllowMultiSelect = False
        .Filters.Add "Excel Files", "*.xls; *.xlsx; *.xlsm; *.xlsb", 1
        .Show
        If .SelectedItems.Count > 0 Then
            importFilePath = .SelectedItems.Item(1)
        Else
            GoTo err
            MsgBox "Import cancelled."
        End If
    End With

    'Beginning processes
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    'Defining and setting variables
    'Loop variables
    Dim i As Integer
    Dim j As Integer
    Dim s As Integer

    'RAW workbook
    Dim dataFile As Worksheet
        Set dataFile = Workbooks.Open(importFilePath).Sheets("Cons Tx excluding credits")

    'Worksheet variables
    Dim wsBOS As Worksheet
        Set wsBOS = ThisWorkbook.Sheets("FY19 Weekly Boston")
    Dim wsMilford As Worksheet
        Set wsMilford = ThisWorkbook.Sheets("FY19 Weekly Milford")
    Dim wsMansfield As Worksheet
        Set wsMansfield = ThisWorkbook.Sheets("FY19 Weekly Mansfield")
    Dim wsSSH As Worksheet
        Set wsSSH = ThisWorkbook.Sheets("FY19 Weekly SSH")
    Dim wsLP As Worksheet
        Set wsLP = ThisWorkbook.Sheets("FY19 Weekly Libbey Park")

    Dim sheetArray As Variant
        sheetArray = Array(wsBOS, wsMilford, wsMansfield, wsSSH, wsLP)


    'SUMIF function variables
    Dim sumIfRange As Range                             'Quantity
        Set sumIfRange = dataFile.Range("M:M")
    Dim cSiteRange As Range                             'Disease site
        Set cSiteRange = dataFile.Range("AM:AM")
    Dim criteriaSite As Range
    Dim cDeptRange As Range                             'Department
        Set cDeptRange = dataFile.Range("B:B")
    Dim criteriaDept As Range
    Dim cTherapyRange As Range                          'Therapy used
        Set cTherapyRange = dataFile.Range("E:E")
    Dim criteriaTherapy As Range
    Dim c2TherapyRange As Range
        Set c2TherapyRange = dataFile.Range("E:E")
    Dim criteria2Therapy As Range
    Dim cGlandGURange As Range
        Set cGlandGURange = dataFile.Range("AM:AM")
    Dim criteriaGlandGU As Range

    'Insert before column containing "Total"
    Dim f As Range
    Dim firstAddress As String

    For s = LBound(sheetArray) To UBound(sheetArray)
        With sheetArray(s)
            With .Rows(7).SpecialCells(XlCellType.xlCellTypeConstants, xlTextValues)
                Set f = .Find(what:="Total", LookIn:=xlValues, lookat:=xlWhole)
                If Not f Is Nothing Then
                    firstAddress = f.Offset(, 1).Address '<-- offset by one column since f will be shifted one column to the right in subsequent statement
                    Do
                        f.EntireColumn.Insert
                        Set f = .FindNext(f)
                    Loop While f.Address <> firstAddress
                End If
            End With
        End With
    Next s

    Dim preCol As Long
        With Sheets("FY19 Weekly Boston")
            With .Rows(7).SpecialCells(XlCellType.xlCellTypeConstants, xlTextValues)
                preCol = .Find("Total", After:="OI7", LookIn:=xlValues).Offset(0, -1).Column
            End With
        End With

    For s = 1 To UBound(sheetArray)
        With sheetArray(s)
            For i = 8 To 21
                Set criteriaDept = sheetArray(s).Cells("B7")
                Set criteriaSite = sheetArray(s).Cells(i, 2)
                Set criteriaTherapy = sheetArray(s).Cells("C6")
                Set criteria2Therapy = sheetArray(s).Cells("C7")
                    sheetArray.Cells(i, preCol) = Application.WorksheetFunction.SumIfs(sumIfRange, cSiteRange, criteriaSite, cDeptRange, criteriaDept, cTherapyRange, criteriaTherapy) + Application.WorksheetFunction.SumIfs(sumIfRange, cSiteRange, criteriaSite, cDeptRange, criteriaDept, c2TherapyRange, criteria2Therapy)
            Next i
        End With
    Next s

        Set criteriaDept = Nothing
        Set criteriaSite = Nothing
        Set criteriaTherapy = Nothing
        Set criteria2Therapy = Nothing







    'Ending processes
    Application.ScreenUpdating = True
    Application.EnableEvents = True

err:
    Exit Sub

End Sub

I made the change, but still no luck. It continues to not loop through the SUMIFS function. — CATSandCATSandCATS, Mar 06 '19 at 17:23
You should always check that your search term is found before accessing its properties such as column. If it's not found your code will error. — SJR, Mar 06 '19 at 17:24
You'll have to check your code, step through and so on, as we don't have enough to go on. In SUMIFS your ranges must be the same size. — SJR, Mar 06 '19 at 17:25
I am not sure what you mean. But the code is not throwing an error. It runs through just fine, except the SUMIFS function simply does not appear do be doing anything. It just selects the column to the left of "Total" and then ends. — CATSandCATSandCATS, Mar 06 '19 at 17:28
I'm not sure you can use just a `String` reference in `After:="OI7"`... Also, do you have `On Error Resume Next` in the code? If so, it's possible there's an error occurring that you're not seeing. — BigBen, Mar 06 '19 at 17:28
@SJR, I updated the post with the entire code. @BigBen, I have `On Error Exit Sub`. That might be it. Let me check. — CATSandCATSandCATS, Mar 06 '19 at 17:31
Now you've posted the rest of your code I guess as @BigBen says it is hitting an error. Comment out the OE line to see what's going on. — SJR, Mar 06 '19 at 17:31
Does that even compile? `Cells("B7")` is not valid syntax. `Range("B7")` would be correct. Also - I'd be very careful about inserting columns while in that `Find()` loop. — Tim Williams, Mar 06 '19 at 17:33
Good call. Now I am getting an error on ` With Sheets("FY19 Weekly Boston")` under `Dim preCol`. — CATSandCATSandCATS, Mar 06 '19 at 17:35
FYI "getting an error" is not a useful description of what goes wrong when you run your code. Likely you're getting that error because you've not specified which workbook to look in. You should always fully-qualify all Range/Cells/Sheets references so there's no ambiguity and your code should never rely on a certain workbook/worksheet being active when it runs. https://stackoverflow.com/questions/28439376/what-is-the-default-scope-of-worksheets-and-cells-and-range/28439984#28439984 — Tim Williams, Mar 06 '19 at 17:37
@Tim Williams, you were absolutely right about not specifying the workbook. Thank you. However, the troubles continue. I am now recieving runtime error 13: type mismatch on `preCol = .Find("Total", After:="OI7", LookIn:=xlValues).Offset(0, -1).Column`. — CATSandCATSandCATS, Mar 06 '19 at 17:41
Still receiving a type mismatch error. Does .Find not return an integer? How might I craft this so I receive an integer based on column location? — CATSandCATSandCATS, Mar 06 '19 at 17:48
I would start with SJR's comment about about checking the result of Find() before you try to access (eg) `Column`. Split that line up into two parts: one to find the cell and then another to get the column. And to be on the safe side swap all your Integer types to Long. — Tim Williams, Mar 06 '19 at 17:50
Also I just noticed the inner With on your Find - I deleted my earlier correction. — Tim Williams, Mar 06 '19 at 18:01
What a learning curve this is turning out to be. I guess `sheetArray.Cells(i, preCol)` should be `sheetArray(s).Cells(i, preCol)` ? — SJR, Mar 06 '19 at 18:19
@SJR: Oh yah. There are so many errors with this code. I am brand new to VBA and coding in general. I am trying to learn on the job. I really appreciate your help! — CATSandCATSandCATS, Mar 06 '19 at 18:23

score 1 · Accepted Answer · answered Mar 06 '19 at 18:06

Something like this:

Dim f As Range, preCol As Long
With ActiveSheet.Rows(7)
        'Range() below is *relative* to the With range
        Set f = .Find("Total", After:=.Range("OI1"), LookIn:=xlValues)
        If Not f Is Nothing Then
            preCol = f.Column - 1
        Else
            'handle missing column header
        End If
End With
Debug.Print preCol

Referencing a column for later use in a loop in VBA

1 Answers1