Here is the code for the form...some comments are to describe what I was doing and some are for things that I want to add or delete.
Option Explicit
Dim Saved As Boolean
Dim ThisRow As Range
Dim rngInfo As Range
Dim rngNext As Range
Private Sub Cleardata()
'Remove all data from the controls
Dim C As MSForms.Control
For Each C In Me.Controls
If C.Tag <> "" Then C = ""
Next
Saved = True
End Sub
Private Sub LoadData()
'Load the data from current row into the controls
Dim C As MSForms.Control
For Each C In Me.Controls
If C.Tag <> "" Then
C = Range(C.Tag & ThisRow.Row)
End If
Next
Saved = True
End Sub
Private Sub ModelBox_Change()
Saved = True
End Sub
Private Sub QuantityBox_Change()
Saved = True
End Sub
Private Sub SizeBox_Change()
Saved = True
End Sub
Private Sub CommandButton2_Click()
Unload Me
'* Application.Goto Worksheets("INFO").Cells(2, "A"), Scroll:=True -> will delete
End Sub
Private Sub CommandButton3_Click()
Dim rModel As Range, rSize As Range, rModel1 As Range, rSize1 As Range
Dim resModel As Variant, resSize As Variant
Dim sModel As String, sSize As String, Quantity As Long
Dim Lsize As Variant
Dim Length As Range
Dim rBrand As Range, rInsul As Range, rBrand1 As Range, rInsul1 As Range
Dim resBrand As Variant, resInsul As Variant
Dim sBrand As String, sInsul As String
Dim LInsul As Variant
Dim Kind As Range
Dim emptyColumn, i As Integer
Set Length = Nothing
Set Kind = Nothing
With Worksheets("INFO")
Select Case Me.CommandButton3.Caption
Case "Get Info"
'Set variable to entire range from A2 to bottom of data in column A
Set rngInfo = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
Case "Next Info"
If rngNext Is Nothing Then
Me.CommandButton3.Caption = "Get Info"
MsgBox "No more data"
Exit Sub
End If
If rngNext.Address = .Cells(.Rows.Count, "A").End(xlUp).Address Then
Me.CommandButton3.Caption = "Get Info"
MsgBox "No more data"
Exit Sub
Else
'Set rngInfo variable to the next cell past last found to bottom of data
Set rngInfo = .Range(rngNext.Offset(1, 0), .Cells(.Rows.Count, "A").End(xlUp))
End If
End Select
End With
For Each rngNext In rngInfo
'* Do <- Need this to go with Loop until rngNext = 0 -> want to use this, don't know where to place it correctly
If rngNext.Value > 0 Then
Me.QuantityBox.Value = rngNext.Value
If Not IsError(rngNext.Offset(0, 1).Value) Then
Me.ModelBox = rngNext.Offset(0, 1).Value
Else
Me.ModelBox = "Error"
End If
If Not IsError(rngNext.Offset(0, 2).Value) Then
Me.SizeBox = rngNext.Offset(0, 2).Value
Else
Me.SizeBox = "Error"
End If
If Not IsError(rngNext.Offset(0, 3).Value) Then
Me.InsulationBox = rngNext.Offset(0, 3).Value
Else
Me.InsulationBox = "Error"
End If
With Worksheets("Sheet3")
Set rModel = .Range("C5", .Range("C5").End(xlToRight))
Set rSize = .Range("B7", .Range("B7").End(xlDown))
End With
sModel = rngNext.Offset(0, 1).Value
sSize = rngNext.Offset(0, 2).Value
Select Case ModelBox.Value
Case "TFS", "ESV", "TQP", "TQS", "MDV", "LHK", "LSC", "EXV", "FLS", "EDV"
With Worksheets("Sheet2")
Set rBrand = .Range("B3")
Set rInsul = .Range("A4", .Range("A4").End(xlDown))
End With
Case "LMHS", "QFC", "QFV", "KQFS", "KQFP", "KLPS", "KLPP", "LMHD", "LMHDT"
With Worksheets("Sheet2")
Set rBrand = .Range("C3")
Set rInsul = .Range("A4", .Range("A4").End(xlDown))
End With
Case "SDV", "RRV", "DSV", "DDV", "DDC", "FPC", "IDV", "FPV"
With Worksheets("Sheet2")
Set rBrand = .Range("E3")
Set rInsul = .Range("A4", .Range("A4").End(xlDown))
End With
Case "35E", "35L", "35M", "35N", "42K", "45J", "45K", "45M", "45N", "45Q", "45R"
With Worksheets("Sheet2")
Set rBrand = .Range("D3")
Set rInsul = .Range("A4", .Range("A4").End(xlDown))
End With
End Select
sBrand = rBrand.Value
sInsul = rngNext.Offset(0, 3).Value
Me.CommandButton3.Caption = "Next Info"
If IsNumeric(sSize) Then
Lsize = CLng(sSize)
Else
Lsize = sSize
End If
If IsNumeric(sInsul) Then
LInsul = CLng(sInsul)
Else
LInsul = sInsul
End If
Quantity = CLng(QuantityBox.Value)
resModel = Application.Match(sModel, rModel, 0)
resSize = Application.Match(Lsize, rSize, 0)
resBrand = Application.Match(sBrand, rBrand, 0)
resInsul = Application.Match(LInsul, rInsul, 0)
If IsError(resModel) Or IsError(resSize) Then
MsgBox "Not Found"
'* Next rngNext -> don't think this will work, would like it to move on to next line if error
ElseIf IsError(resBrand) Or IsError(resInsul) Then
MsgBox "Not Found"
'* Next rngNext -> don't think this will work, would like it to move on to next line if error
Else
Debug.Print resModel, resSize
Debug.Print resBrand, resInsul
Set rModel1 = rModel(resModel)
Set rSize1 = rSize(resSize)
Set Length = Intersect(rModel1.EntireColumn, rSize1.EntireRow)
Set rBrand1 = rBrand(resBrand)
Set rInsul1 = rInsul(resInsul)
Set Kind = Intersect(rBrand1.EntireColumn, rInsul1.EntireRow)
End If
Worksheets("Sheet1").Activate
i = 1
Do
emptyColumn = WorksheetFunction.CountA(Range("A:ZZ")) + i
Cells(i, emptyColumn).Value = Quantity * Length.Value
Cells(i + 1, emptyColumn).Value = Kind.Value
Cells(i + 2, emptyColumn).Value = QuantityBox.Value & " " & ModelBox.Value
i = i + 1
Loop Until i = 2
Exit For
'* Loop Until rngNext = 0 -> would like to use this, don't know where to put it correctly
End If
Next rngNext
End Sub
Private Sub UserForm_Initialize()
Me.CommandButton3.Caption = "Get Info"
End Sub
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
