Qfyilyi

I'm having some trouble with the following activity: I need to pass a selection of 5 different drivers (STIMA, STIMA_SEDE, LOTTO, STORICO, UNIFORME) done in a form to a variable in VBA and search the corresponding column in my worksheet.

So far I tried two ways, unfortunately without results:

First way:

    Private Sub UserForm_Initialize()



Dim rngDriver  As Range

Dim ws As Worksheet



Set ws = Worksheets("Data validation")



For Each rngDriver In ws.Range("Drivers")

    Me.cbDriver.AddItem rngDriver.Value

Next rngDriver



End Sub



Private Sub OKbtn_Click()

Dim destValues() As Variant, driverValues() As Variant, totalDriver As Double

    Dim totalValue As Double, offsetDriverCol As Integer, deltaPerc() As Double

    Dim i As Long, j As Long, k As Long, decimalPart As Double, driverRange As Range

    Dim driverCol As Integer, modCol As Integer, DestRange As Range

...

 driverCol = 2

    Do While Cells(23, driverCol).Value <> frmDistribuisci.cbDriver.Value

        driverCol = driverCol + 1

    Loop

Second way:

...

If frmDistribuisci.cbDriver.Value Is LOTTO Then

        driverCol = 2

        Do While Cells(23, driverCol).Value <> "Lotto"

            driverCol = driverCol + 1

        Loop

        End If

        'Identificazione colonna driver Stima

        If frmDistribuisci.cbDriver.Value Is STIMA Then

        driverCol = 2

        Do While Cells(23, driverCol).Value <> "Stima"

            driverCol = driverCol + 1

        Loop

        End If

        'Identificazione colonna driver Stima da sede

        If frmDistribuisci.cbDriver.Value Is STIMA_SEDE Then

        driverCol = 2

        Do While Cells(23, driverCol).Value <> "Stima da sede"

            driverCol = driverCol + 1

        Loop

        End If

        'Identificazione colonna driver Storico

        If frmDistribuisci.cbDriver.Value Is STORICO Then

        driverCol = 2

        Do While Cells(23, driverCol).Value <> "Storico"

            driverCol = driverCol + 1

        Loop

        End If

Debugging this code it seems to skip the If-steps as well as the first solution, so I'm stuck...

Has anyone an idea of how to do this? It would be highly appreciated, thanks in advance!

This is no valid syntax for a value comparison:

If frmDistribuisci.cbDriver.Value Is LOTTO Then

It should be If … = … Then and LOTTO is considered as a type/class (because of the Is operator, which is used for something else entirely), not as the intended string. String literals must be delimited with double-quotes:

If frmDistribuisci.cbDriver.Value = "LOTTO" Then

That should work as intended.