arraysexcelvbafunctionloops

Pass array through loop in function


I am trying to use a function with an array for pointers, but I cannot seem to loop through my array, as it always returns a mismatch. I tried the myDel variable as long and debug.print, but that didn't work either. The purpose of this function is to delete everything from a starting point, except the offset columns from the starting point, which are in the array.

ClearInvoice Sheet1.Range("A18"), Array(2,9,10,11,12)


Public Function ClearInvoice(SPoint As Range, arrCol As Variant)
'Delete specific contents
Dim myDel As Variant
Dim lrSP, intDel As Integer
lrSP = SPoint.End(xlDown).Row
For intDel = 0 To 12
    For Each myDel In arrCol
        'Debug.Print myDel
        If myDel = intDel Then
            'do nothing
        Else
            ActiveSheet.Range(Cells(SPoint.Row, SPoint.Offset(0, intDel).Column), Cells(lrSP, SPoint.Offset(0, intDel).Column)).Select
            Selection.ClearContents
        End If
    Next
Next
End Function

Solution

  • This is the mod function:

    Public Function ClearInvoice(SPoint As Range, arrCol As Variant)
    'Delete specific contents
    Dim lrSP, intDel, myDel As Integer
    lrSP = SPoint.End(xlDown).Row
    For intDel = 0 To 12
        nodel = False
        For myDel = LBound(arrCol) To UBound(arrCol)  'checks include array
            Debug.Print myDel
            If intDel = arrCol(myDel) Then
                nodel = True   'if include
                Exit For
            End If
                'do nothing
        Next myDel
        If Not nodel Then
                ActiveSheet.Range(Cells(SPoint.Row, SPoint.Offset(0, intDel).Column), Cells(lrSP, SPoint.Offset(0, intDel).Column)).Select
                Selection.ClearContents
        End If
        'Next
    Next
    End Function
    
    
    

    The main problem was that you compare the index and not the array(index) in your loop. This is what needed: arrCol(myDel).
    The other was that the inner For loop has to check first the whole array, and after the result delete or not the column in intDel