Hi, I'm using this code to get data from multiple rows with the same criteria into 1 textbox, but what it is doing is only taking the last row of that criteria and putting it into the textbox. how do I change it so that the correct number of rows of data is reflected in the textbox?
Private Sub ComboBox2_Change()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim iRow As Long
Set ws1 = ThisWorkbook.Sheets("Case Log")
Set ws2 = ThisWorkbook.Sheets("Print page")
TextBox18.Text = ""
TextBox8.Text = ""
TextBox9.Text = ""
With ws2.Range("A5:C100")
.ClearContents
End With
iRow = ws2.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
wsLR = ws1.Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To wsLR
If ws1.Cells(x, 1) = ComboBox2.Value Then
ws2.Cells(3, 3) = ComboBox2.Value
ws2.Cells(4, 3) = CDate(ws1.Cells(x, 7))
With TextBox18
.Text = "General Comments:" & vbCr & ws1.Cells(x, 10).Text & vbCr & vbCr & "Sample / Type / Marking / Mortuary / Date & Time Received" & vbCr & ws1.Cells(x, 2).Text & " " & ws1.Cells(x, 3).Text & " " & ws1.Cells(x, 4).Text & " " & ws1.Cells(x, 5).Text & " " & ws1.Cells(x, 7).Text & " " & ws1.Cells(x, 8).Text & vbCr
ws2.Cells(iRow + 1, 3) = ws1.Cells(x, 2).Text
ws2.Cells(iRow + 2, 3) = ws1.Cells(x, 4).Text
ws2.Cells(iRow + 3, 3) = CDate(ws1.Cells(x, 7))
iRow = iRow + 4
End With
With TextBox8
.Text = ws1.Cells(x, 6).Text
End With
With TextBox9
.Text = ws1.Cells(x, 9).Text
End With
End If
Next x
If ComboBox2.Value = "" Then
ws2.Range("A:B").Value = ""
Else
ws2.Range("A1").Value = "DNA Profiling Laboratory, HSA"
ws2.Range("A3").Value = "Case No"
ws2.Range("B3").Value = ":"
ws2.Range("A4").Value = "Date Created"
ws2.Range("B4").Value = ":"
End If
If ws2.Range("C6") = "" Then
ws2.Range("A5:B8").Value = ""
Else
ws2.Range("A5").Value = "DNA Profiling Laboratory, HSA"
ws2.Range("A6").Value = "Sample No"
ws2.Range("B6").Value = ":"
ws2.Range("A7").Value = "Marking"
ws2.Range("B7").Value = ":"
ws2.Range("A8").Value = "Received Date"
ws2.Range("B8").Value = ":"
End If
If ws2.Range("C10") = "" Then
ws2.Range("A9:B12").Value = ""
Else
ws2.Range("A9").Value = "DNA Profiling Laboratory, HSA"
ws2.Range("A10").Value = "Sample No"
ws2.Range("B10").Value = ":"
ws2.Range("A11").Value = "Marking"
ws2.Range("B11").Value = ":"
ws2.Range("A12").Value = "Received Date"
ws2.Range("B12").Value = ":"
End If
End Sub
Bookmarks