CODEWITHSUNDEEP
excelvbaexcel-formula
Loïc Dubois
Loïc Dubois

Reputation: 152

Excel use column letter in function VBA

I am trying to create a VBA function that calls on another function for each column. The idea is that for every element of thisRange (it will all be elements of the same line), I check that all the elements in that the number of PassableSteps is equal to that of non empty cells. My issue is that the call of CountPassableSteps which takes a Range as Argument does not work and returns a #VALUE! error. I have a feeling that this is an easy fix but no idea how to do it. The problem is probably coming from the calculation of NbNotEmpty.

Public Function countPassableSteps(thisRange As Range) As Long
    Application.Volatile
    Dim lColorCounter As Long
    Dim rngCell As Range
    For Each rngCell In thisRange
        If Not IsEmpty(rngCell.Value) Then
            myVar = Application.WorksheetFunction.VLookup(rngCell.Value, Worksheets(1).Range("A2:C302"), 3, False)
            If myVar = True Then
                lColorCounter = lColorCounter + 1
            End If
        End If
    Next
    countPassableSteps = lColorCounter
End Function


Public Function countPassableTests(thisRange As Range) As Long
    Application.Volatile
    Dim lColorCounter As Long
    Dim rngCell As Range
    For Each rngCell In thisRange
        If Not IsEmpty(rngCell.Value) Then
            Dim ColumnNumber As Long
            Dim ColumnLetter As String
            ColumnNumber = rngCell.column
            ColumnLetter = Split(Cells(1, ColumnNumber).Address, "$")(1)
            Dim NbStepsPassable As Long
            NbStepsPassable = countPassableSteps(Range("ColumnLetter & 2: ColumnLetter & 105"))
            Dim NbNotEmpty As Long
            NbNotEmpty = WorksheetFunction.CountA(Range("ColumnLetter & 2: ColumnLetter & 105"))
            If NbStepsPassable = NbNotEmpty Then lColorCounter = lColorCounter + 1
        End If
    Next
    countPassableTests = lColorCounter
End Function


Public Function countEmptyTests(thisRange As Range) As Long
    Application.Volatile
    Dim lColorCounter As Long
    Dim rngCell As Range
    For Each rngCell In thisRange
        If Not IsEmpty(rngCell.Value) Then
            Dim NbNotEmpty As Long
            NbNotEmpty = WorksheetFunction.CountA(Range("ColumnLetter & 2: ColumnLetter & 105"))
            If NbNotEmpty = 0 Then lColorCounter = lColorCounter + 1
        End If
    Next
    countEmptyTests = lColorCounter
End Function

Thanks in advance!

Upvotes: 0

Views: 50

Answers (1)

norie
norie

Reputation: 9867

ColumnLetter shouldn't be inside the quotes.

So something like this,

Range("ColumnLetter & 2: ColumnLetter & 105")

should look like this.

Range(ColumnLetter & "2:" & ColumnLetter & "105")

By the way, you could do this without ColumnLetter by using Intersect.

Intersect(rngCell.EntireColumn, Range("2:105"))

Upvotes: 2

Related Questions