Case based If then statements in a userform

Question

Scenario 1: If Combobox 2 displays the same text/value currently in the worksheet MRFL Range(A1:A250) and combobox 1 displays the same text/value as in worksheet MFRL Range(B1:B250) then only change the value of column AE in the same row as the combobox 2 value in Worksheet MFGLR.

Scenario 2: If Combobox 2 displays the same text/value currently in the worksheet MRFL Range(A1:A250) and combobox 1 displays a different text then in worksheet MFRL Range(B1:B250) then add the values of combobox1 and combobox2 at the next available row in worksheet MFRL, Combobox1 in column B and Combobox2 in column A. And then only change the value of column AE in the same row as the combobox 2 value in Worksheet MFGLR.

Scenario 3: If Combobox 2 displays a different text/value currently in the worksheet MRFL Range(A1:A250) and combobox 1 displays a different text then in worksheet MFRL Range(B1:B250) then add the values of combobox1 and combobox2 at the next available row in worksheet MFRL, add combobox2 value at the next available row in Column A in the worksheet CT and add the combobox 2 value in the next available row in Column A of worksheet MFGLR Combobox1 in column B and Combobox2 in column A. And then only change the value of column AE in the same row as the combobox 2 value in Worksheet MFGLR.

For Scenarios 1 and 2, I'm having trouble having the code find the same row as combobox2 value and then pasting the textbox1 value 31 columns to the right as that. This is what I have so far.

With Worksheets("MFGLR").Range("a1:a500")
Set C = .Find(ComboBox2.Value, LookIn:=xlValues)
If Not C Is Nothing Then
firstAddress = C.Address
Do
    C.Value = TextBox1.Value
    Set C = .FindNext(C)
Loop While Not C Is Nothing
End If
End With

The scenarios don't seem to be holding true for all the cases
I would like the border of CT to go from the lastrow Column A to Column V
I would like the border of MFRL to go from lastrow Column A to Column E
I would like the border of MFRL to go from lastrow Column A to Column AM
Fill down from the last row in MFGLR column AF to column AH

This is my WIP for all the scenarios with borders and filldown.

Option Explicit
Private Sub CommandButton1_Click()

Dim ColA As New Scripting.Dictionary  'Need Microsoft Scripting Runtime Reference
Dim ColB As New Scripting.Dictionary
Dim LastRow As Long
Dim Criteria1 As Boolean
Dim Criteria2 As Boolean
Dim C As Range



With ThisWorkbook.Sheets("MFRL")
    LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row 'This gets the last row on column A
    For Each C In .Range("A1:A" & LastRow) 'loop through the whole column
    On Error Resume Next
        'If you have duplicated entries this will throw an error
        ColA.Add C.Value, C.Row 'add the values from column A to DictA, also store it's row for later purposes
        ColB.Add C.Offset(0, 1).Value, C.Row 'add the values from column B to DictB, also store it's row for later purposes
    Next C
    'Criterias will give value of True if matched or False if not
    Criteria1 = ColA.Exists(ComboBox2.Value) 'this is getting matched with ColA Dictionary
    Criteria2 = ColB.Exists(ComboBox1.Value) 'this is getting matched with ColB Dictionary
    If Criteria1 And Criteria2 Then 'SCENARIO 1
        .Cells(ColA(ComboBox2.Value), "AE:100") = TextBox1.Value
    ElseIf Criteria1 And Not Criteria2 Then 'SCENARIO 2
        .Cells(LastRow + 1, 1) = ComboBox2.Value
        .Cells(LastRow + 1, 2) = ComboBox1.Value
        .Cells(ColA(ComboBox2.Value), "AE") = TextBox1.Value
    With Worksheets("MFRL")
        .Cells(Rows.Count, "B").End(xlUp).Offset(-1, 1).Resize(, 3).AutoFill .Cells(Rows.Count, "B").End(xlUp).Offset(-1, 1).Resize(, 3).Resize(2)
        .Cells(Rows.Count, "B").End(xlUp).Offset(-1, 1).Resize(, 3).Resize(2).Borders.LineStyle = xlContinuous
        End With
    ElseIf Not Criteria1 And Not Criteria2 Then 'SCENARIO 3
        .Cells(LastRow + 1, 1) = ComboBox2.Value
        .Cells(LastRow + 1, 2) = ComboBox1.Value
        'Add data to the column A next blank row in sheet CT
        LastRow = ThisWorkbook.Sheets("CT").Cells(ThisWorkbook.Sheets("CT").Rows.Count, 1).End(xlUp).Row + 1
        ThisWorkbook.Sheets("CT").Cells(LastRow, 1) = ComboBox2.Value
        ThisWorkbook.Sheets("CT").Cells(Rows.Count, "A").End(xlUp).Offset(-1, 1).Resize(, 21).AutoFill .Cells(Rows.Count, "A").End(xlUp).Offset(-1, 1).Resize(, 21).Resize(2)
        ThisWorkbook.Sheets("CT").Cells(Rows.Count, "A").End(xlUp).Offset(-1, 1).Resize(, 21).Resize(2).Borders.LineStyle = xlContinuous
        'Add data to the column A next blank row in sheet MFGLR
        LastRow = ThisWorkbook.Sheets("MFGL R").Cells(ThisWorkbook.Sheets("MFGLR").Rows.Count, 1).End(xlUp).Row + 1
        ThisWorkbook.Sheets("MFGLR").Cells(LastRow, 1) = ComboBox2.Value
        ThisWorkbook.Sheets("MFGLR").Cells(LastRow, "AE") = TextBox1.Value
        ThisWorkbook.Sheets("MFGLR").Cells(Rows.Count, "AE").End(xlUp).Offset(-1, 1).Resize(, 3).AutoFill .Cells(Rows.Count, "AE").End(xlUp).Offset(-1, 1).Resize(, 3).Resize(2)
        ThisWorkbook.Sheets("MFGLR").Cells(Rows.Count, "A").End(xlUp).Offset(-1, 1).Resize(, 38).Resize(2).Borders.LineStyle = xlContinuous
        ThisWorkbook.Sheets("MFRL").Cells(LastRow, 1) = ComboBox2.Value
        ThisWorkbook.Sheets("MFRL").Cells(LastRow, 2) = ComboBox1.Value
        ThisWorkbook.Sheets("MFRL").Cells(Rows.Count, "B").End(xlUp).Offset(-1, 1).Resize(, 3).AutoFill .Cells(Rows.Count, "B").End(xlUp).Offset(-1, 1).Resize(, 3).Resize(2)
        ThisWorkbook.Sheets("MFRL").Cells(Rows.Count, "B").End(xlUp).Offset(-1, 1).Resize(, 3).Resize(2).Borders.LineStyle = xlContinuous
   End If
 End With
 ActiveWorkbook.RefreshAll
 Unload Me
 End Sub

You compare a single string (ComboBox2.Value) with a whole range of cells? What is the idea? — FunThomas
I mean to say if Combobox2.value is = to any cell in that range then...@FunThomas — Tmacjoshua
I would suggest then store both columns in 2 dictionaries. then check using the property .Existsfrom the dictionary if the Combobox2.Value is inside the dictionaries, if so then your code. — Damian
@Damian not exactly sure how to do that, shouldn't the if statement I have work though? — Tmacjoshua
No, you can't compare a single value to a whole range like this, you need to either loop through the range or as I said, store the ranges into dictionaries and check if your values exists. Also your last line, the one printing the value to the cell doesn't work either. You can't use sheet.range.offset(0, 31) it should be Sheets("SheetName").Range("A1").Offset(0, 31) — Damian

Damian Damian · Accepted Answer · 2019-05-28T18:35:47

If I understood all your scenarios ok this should do it, otherwise, please feel free to amend the code:

Option Explicit 'always get this, it forces you to declare all your variables
Sub Test()

    Dim ColA As New Scripting.Dictionary  'Need Microsoft Scripting Runtime Reference
    Dim ColB As New Scripting.Dictionary
    Dim LastRow As Long
    Dim Criteria1 As Boolean
    Dim Criteria2 As Boolean
    Dim C As Range


    With ThisWorkbook.Sheets("MFGLR")
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row 'This gets the last row on column A
        For Each C In .Range("A1:A" & LastRow) 'loop through the whole column
            'If you have duplicated entries this will throw an error
            ColA.Add C.Value, C.Row 'add the values from column A to DictA, also store it's row for later purposes
            ColB.Add C.Offset(0, 1).Value, C.Row 'add the values from column B to DictB, also store it's row for later purposes
        Next C
        'Criterias will give value of True if matched or False if not
        Criteria1 = ColA.Exists(Combobox2.Value) 'this is getting matched with ColA Dictionary
        Criteria2 = ColB.Exists(Combobox1.Value) 'this is getting matched with ColB Dictionary
        If Criteria1 And Criteria2 Then 'SCENARIO 1
            .Cells(ColA(Combobox2.Value), "AE") = Combobox2.Value
        ElseIf Criteria1 And Not Criteria2 Then 'SCENARIO 2
            .Cells(LastRow + 1, 1) = Combobox2.Value
            .Cells(LastRow + 1, 2) = Combobox1.Value
        ElseIf Not Criteria1 And Not Criteria2 Then 'SCENARIO 3
            .Cells(LastRow + 1, 1) = Combobox2.Value
            .Cells(LastRow + 1, 2) = Combobox1.Value
            'Add data to the column A next blank row in sheet CT
            LastRow = ThisWorkbook.Sheets("CT").Cells(ThisWorkbook.Sheets("CT").Rows.Count, 1).End(xlUp).Row + 1
            ThisWorkbook.Sheets("CT").Cells(LastRow, 1) = Combobox2.Value
            'Add data to the column A next blank row in sheet MFGLR
            LastRow = ThisWorkbook.Sheets("MFGLR ").Cells(ThisWorkbook.Sheets("MFGLR ").Rows.Count, 1).End(xlUp).Row + 1
            ThisWorkbook.Sheets("MFGLR").Cells(LastRow, 1) = Combobox2.Value
            ThisWorkbook.Sheets("MFGLR").Cells(LastRow, 2) = Combobox1.Value
            ThisWorkbook.Sheets("MFGLR").Cells(LastRow + 1, "AE") = Combobox2.Value
        End If
    End With

End Sub

Case based If then statements in a userform

1 Answers