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
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