deltabravo
New Member
- Joined
- Oct 9, 2016
- Messages
- 5
I have used the following code on a userform for 3 comboboxes, I have 16 similar comboboxes,
---------------------------------
Sub Fault_ComboBox_SSFRID1A()
Dim lastrow As Integer
Dim SearchRange As Range
Dim FindRow As Range
'If the is an error continue with code
On Error Resume Next
lastrow = ThisWorkbook.Sheets("Engineering Data").Cells(Rows.Count, 1).End(xlUp).Row
Set SearchRange = ThisWorkbook.Sheets("Engineering Data").Range("A1:A" & lastrow)
Set FindRow = SearchRange.Find(faultsfrm.ie1sfrida.Value, LookIn:=xlValues, LookAt:=xlWhole)
'MsgBox FindRow.Row
With ThisWorkbook.Sheets("Engineering Data")
faultsfrm.ie1sscida.Value = .Range("B" & FindRow.Row).Value
faultsfrm.ie1typea.Value = .Range("F" & FindRow.Row).Value
faultsfrm.ie1desca.Value = .Range("G" & FindRow.Row).Value
faultsfrm.ie1suba.Value = .Range("H" & FindRow.Row).Value
faultsfrm.ie1classa.Value = .Range("AE" & FindRow.Row).Value
faultsfrm.ie1pfda.Value = .Range("AH" & FindRow.Row).Value
End With
On Error GoTo 0
End Sub
------------------------------------
Sub Fault_ComboBox_SSFRID1B()
Dim lastrow As Integer
Dim SearchRange As Range
Dim FindRow As Range
On Error Resume Next
lastrow = ThisWorkbook.Sheets("Engineering Data").Cells(Rows.Count, 1).End(xlUp).Row
Set SearchRange = ThisWorkbook.Sheets("Engineering Data").Range("A1:A" & lastrow)
Set FindRow = SearchRange.Find(faultsfrm.ie1sfridb.Value, LookIn:=xlValues, LookAt:=xlWhole)
'MsgBox FindRow.Row
With ThisWorkbook.Sheets("Engineering Data")
faultsfrm.ie1sscidb.Value = .Range("B" & FindRow.Row).Value
faultsfrm.ie1typeb.Value = .Range("F" & FindRow.Row).Value
faultsfrm.ie1descb.Value = .Range("G" & FindRow.Row).Value
faultsfrm.ie1subb.Value = .Range("H" & FindRow.Row).Value
faultsfrm.ie1classb.Value = .Range("AE" & FindRow.Row).Value
faultsfrm.ie1pfdb.Value = .Range("AH" & FindRow.Row).Value
End With
On Error GoTo 0
End Sub
---------------------------------------
Sub Fault_ComboBox_SSFRID2A()
Dim lastrow As Integer
Dim SearchRange As Range
Dim FindRow As Range
On Error Resume Next
lastrow = ThisWorkbook.Sheets("Engineering Data").Cells(Rows.Count, 1).End(xlUp).Row
Set SearchRange = ThisWorkbook.Sheets("Engineering Data").Range("A1:A" & lastrow)
Set FindRow = SearchRange.Find(faultsfrm.ie2sfrida.Value, LookIn:=xlValues, LookAt:=xlWhole)
'MsgBox FindRow.Row
With ThisWorkbook.Sheets("Engineering Data")
faultsfrm.ie2sscida.Value = .Range("B" & FindRow.Row).Value
faultsfrm.ie2typea.Value = .Range("F" & FindRow.Row).Value
faultsfrm.ie2desca.Value = .Range("G" & FindRow.Row).Value
faultsfrm.ie2suba.Value = .Range("H" & FindRow.Row).Value
faultsfrm.ie2classa.Value = .Range("AE" & FindRow.Row).Value
faultsfrm.ie2pfda.Value = .Range("AH" & FindRow.Row).Value
End With
On Error GoTo 0
End Sub
SSFRID1A,
SSFRID1B,
SSFRID1C etc. Its there a way to write the code once and use it for each cbox rather that creating a new sub for each.
---------------------------------
Sub Fault_ComboBox_SSFRID1A()
Dim lastrow As Integer
Dim SearchRange As Range
Dim FindRow As Range
'If the is an error continue with code
On Error Resume Next
lastrow = ThisWorkbook.Sheets("Engineering Data").Cells(Rows.Count, 1).End(xlUp).Row
Set SearchRange = ThisWorkbook.Sheets("Engineering Data").Range("A1:A" & lastrow)
Set FindRow = SearchRange.Find(faultsfrm.ie1sfrida.Value, LookIn:=xlValues, LookAt:=xlWhole)
'MsgBox FindRow.Row
With ThisWorkbook.Sheets("Engineering Data")
faultsfrm.ie1sscida.Value = .Range("B" & FindRow.Row).Value
faultsfrm.ie1typea.Value = .Range("F" & FindRow.Row).Value
faultsfrm.ie1desca.Value = .Range("G" & FindRow.Row).Value
faultsfrm.ie1suba.Value = .Range("H" & FindRow.Row).Value
faultsfrm.ie1classa.Value = .Range("AE" & FindRow.Row).Value
faultsfrm.ie1pfda.Value = .Range("AH" & FindRow.Row).Value
End With
On Error GoTo 0
End Sub
------------------------------------
Sub Fault_ComboBox_SSFRID1B()
Dim lastrow As Integer
Dim SearchRange As Range
Dim FindRow As Range
On Error Resume Next
lastrow = ThisWorkbook.Sheets("Engineering Data").Cells(Rows.Count, 1).End(xlUp).Row
Set SearchRange = ThisWorkbook.Sheets("Engineering Data").Range("A1:A" & lastrow)
Set FindRow = SearchRange.Find(faultsfrm.ie1sfridb.Value, LookIn:=xlValues, LookAt:=xlWhole)
'MsgBox FindRow.Row
With ThisWorkbook.Sheets("Engineering Data")
faultsfrm.ie1sscidb.Value = .Range("B" & FindRow.Row).Value
faultsfrm.ie1typeb.Value = .Range("F" & FindRow.Row).Value
faultsfrm.ie1descb.Value = .Range("G" & FindRow.Row).Value
faultsfrm.ie1subb.Value = .Range("H" & FindRow.Row).Value
faultsfrm.ie1classb.Value = .Range("AE" & FindRow.Row).Value
faultsfrm.ie1pfdb.Value = .Range("AH" & FindRow.Row).Value
End With
On Error GoTo 0
End Sub
---------------------------------------
Sub Fault_ComboBox_SSFRID2A()
Dim lastrow As Integer
Dim SearchRange As Range
Dim FindRow As Range
On Error Resume Next
lastrow = ThisWorkbook.Sheets("Engineering Data").Cells(Rows.Count, 1).End(xlUp).Row
Set SearchRange = ThisWorkbook.Sheets("Engineering Data").Range("A1:A" & lastrow)
Set FindRow = SearchRange.Find(faultsfrm.ie2sfrida.Value, LookIn:=xlValues, LookAt:=xlWhole)
'MsgBox FindRow.Row
With ThisWorkbook.Sheets("Engineering Data")
faultsfrm.ie2sscida.Value = .Range("B" & FindRow.Row).Value
faultsfrm.ie2typea.Value = .Range("F" & FindRow.Row).Value
faultsfrm.ie2desca.Value = .Range("G" & FindRow.Row).Value
faultsfrm.ie2suba.Value = .Range("H" & FindRow.Row).Value
faultsfrm.ie2classa.Value = .Range("AE" & FindRow.Row).Value
faultsfrm.ie2pfda.Value = .Range("AH" & FindRow.Row).Value
End With
On Error GoTo 0
End Sub