Combobox.Value in Range do else

mar050703 · Jul 27th 2016

Hello

Sorry, if the title of this thread is not accurate.

I have the following code:

Code

Private Sub ComboBox1_Change()
Dim MMod As Range
Dim MFind As Range
Set MMod = Worksheets("Validate").Range("R2:R8")
For Each MFind In MMod
    If MFind.Value = ComboBox1.Value Then
       Sheets("Validate").Range("R1").Select
      Do
        If ActiveCell.Value = ComboBox1.Value = False Then
            ActiveCell.Offset(1, 0).Select
        End If
      Loop Until ActiveCell.Value = ComboBox1.Value
            TextBox1.Value = ActiveCell.Offset(0, 1).Value
    
 
    End If
    
Next MFind
 


End Sub

Display More

What I am trying to do is check if the value of the combobox equals the same as any of the Range MMod, and then find that Value in the Range and pass the value next to it to a textbox, - that bit works, however if the value of the combobox is not in Range MMod, then I want the textbox value to be "Zero".

If I put:

Code

else: textbox1.value = "Zero"

before NextMFind I always return "Zero"

What am I doing wrong please?

mrmmickle1 · Jul 27th 2016

Re: Combobox.Value in Range do else

Maybe try using a Boolean to check if a value matches...once a value matches the "Flag" = True So if the "Flag" = False then you know that no value was found.....

Code

Private Sub ComboBox1_Change()
    Dim MMod As Range
    Dim MFind As Range
    Dim myFlag As Boolean
    
    Set MMod = Worksheets("Validate").Range("R2:R8")
    For Each MFind In MMod
        If MFind.Value = ComboBox1.Value Then
            myFlag = True
            Sheets("Validate").Range("R1").Select
            Do
                If ActiveCell.Value = ComboBox1.Value = False Then
                    ActiveCell.Offset(1, 0).Select
                End If
            Loop Until ActiveCell.Value = ComboBox1.Value
            textbox1.Value = ActiveCell.Offset(0, 1).Value
             
             
        End If
         
    Next MFind
    
    If myFlag = False Then textbox1.Value = "Zero"
    
End Sub

Display More

**norie** · Jul 28th 2016

Re: Combobox.Value in Range do else

Perhaps.

Code

Private Sub ComboBox1_Change()
Dim MMod As Range
Dim Res As Variant


    Set MMod = Worksheets("Validate").Range("R2:R8")


    Res = Application.Match(ComboBox1.Value, MMod, 0)


    If Not IsError(Res) Then
        TextBox1.Value = MMod(Res).Offset(, 1).Value
    Else
        TextBox1.Value = "Zero"
    End If


End Sub

Display More

mar050703 · Aug 1st 2016

Re: Combobox.Value in Range do else

Thanks Norie, your code was perfect, simpler than mine too.

Combobox.Value in Range do else

Participate now!

Share