Extract data from cells to userform

[PSkinny]

I have a sheet with job numbers in column A, columns B to F contain extra details for the job number and in the same row as the job number, except for column C which has a number of entries in multiple rows. Each entry can have up to 8 rows in column C. I need to be able to extract the data from row C for the job number and stop at the row before the next job number. The data is extracted to textboxes (SignCode?) on a userform. The code I am using almost does this but it always extracts 8 rows of data. I am sure I have missed something simple but I keep going round in circles and getting nowhere. Hope this makes sense. Any assistance would be appreciated.

'Find the Job Number and extract data to userform
    r = WorksheetFunction.Match(Val(JobNumber), ws1.Columns(1), 0)
    currentrow = r
        Me.Address = ws1.Cells(r, "B")
        Me.RcdDate = ws1.Cells(r, "D")
        Me.DateComp = ws1.Cells(r, "E")
        Me.Installer = ws1.Cells(r, "F")
        Me.SignCode1 = ws1.Cells(r, "C")
        Me.SignCode2 = ws1.Cells(r + 1, "C")
        Me.SignCode3 = ws1.Cells(r + 2, "C")
        Me.SignCode4 = ws1.Cells(r + 3, "C")
        Me.SignCode5 = ws1.Cells(r + 4, "C")
        Me.SignCode6 = ws1.Cells(r + 5, "C")
        Me.SignCode7 = ws1.Cells(r + 6, "C")
        Me.SignCode8 = ws1.Cells(r + 7, "C")

 

[NdNoviceHlp]

"Each entry can have up to 8 rows in column C"
"The code I am using almost does this but it always extracts 8 rows of data"

Me.SignCode1 = ws1.Cells(r, "C")
Me.SignCode2 = ws1.Cells(r + 1, "C")
Me.SignCode3 = ws1.Cells(r + 2, "C")
Me.SignCode4 = ws1.Cells(r + 3, "C")
Me.SignCode5 = ws1.Cells(r + 4, "C")
Me.SignCode6 = ws1.Cells(r + 5, "C")
Me.SignCode7 = ws1.Cells(r + 6, "C")
Me.SignCode8 = ws1.Cells(r + 7, "C")

U get what U code. I really don't understand what U find as wrong with this? I suspect that "I need to be able to extract the data from row C for the job number and stop at the row before the next job number." is what is troubling U as stated. U need to establish where the next job number is in Column A. Assuming cells are blank in "A" between job numbers, something like...

Me.SignCode1 = vbnullstring
Me.SignCode2 =vbnullstring
Me.SignCode3 = vbnullstring
Me.SignCode4 = vbnullstring
Me.SignCode5 = vbnullstring
Me.SignCode6 = vbnullstring
Me.SignCode7 = vbnullstring
Me.SignCode8 = vbnullstring
r = WorksheetFunction.Match(Val(JobNumber), ws1.Columns(1), 0)
With Sheets("ws1")
LastRow = .Range("C" & .Rows.Count).End(xlUp).Row
End With
For cnt = r to Lastrow
If Sheets("ws1").Cells(Cnt, "A") <> vbnullstring then
exit for
end if
next cnt
For cnt2 = r to cnt-1
cnter = cnter + 1
Select case cnter
case1: Me.SignCode1 = ws1.Cells(r, "C")
case2: Me.SignCode2 = ws1.Cells(r + 1, "C")
case3: Me.SignCode3 = ws1.Cells(r + 2, "C")
case4: Me.SignCode4 = ws1.Cells(r + 3, "C")
case5: Me.SignCode5 = ws1.Cells(r + 4, "C")
case6: Me.SignCode6 = ws1.Cells(r + 5, "C")
case7: Me.SignCode7 = ws1.Cells(r + 6, "C")
case8: Me.SignCode8 = ws1.Cells(r + 7, "C")
end select
next cnt2

HTH. Dave

 

[PSkinny]

Thank you for your prompt response NdNoviceHlp, unfortunately your code errors at Case1: with the message "Compile error - Statements and labels invalid between select case and first case."

 

[NdNoviceHlp]

There was a space missing ie case1 vs case 1. Sorry I really didn't want to re-create your userform and didn't post tested code. Here's the tested code. I just used textbox1 for jobnumber so change to suit. Dave

Option Explicit
Sub test()
Dim r As Double, Cnt As Double, Lastrow As Double
Dim Cnt2 As Double, Jobnumber As Double, Cnter As Integer
Jobnumber = UserForm1.TextBox1.Value
'Me.Address = Sheets("ws1").Cells(r, "B")
'Me.RcdDate = Sheets("ws1").Cells(r, "D")
'Me.DateComp = Sheets("ws1").Cells(r, "E")
'Me.Installer = Sheets("ws1").Cells(r, "F")
Me.SignCode1 = vbNullString
Me.SignCode2 = vbNullString
Me.SignCode3 = vbNullString
Me.SignCode4 = vbNullString
Me.SignCode5 = vbNullString
Me.SignCode6 = vbNullString
Me.SignCode7 = vbNullString
Me.SignCode8 = vbNullString
With Sheets("ws1")
Lastrow = .Range("C" & .Rows.Count).End(xlUp).Row
End With
r = Application.WorksheetFunction.Match(CInt(Jobnumber), _
Sheets("ws1").Range("A1:A" & Lastrow), 0)
'currentrow = r
For Cnt = r + 1 To Lastrow
If Sheets("ws1").Cells(Cnt, "A") <> vbNullString Then
Exit For
End If
Next Cnt
For Cnt2 = r To Cnt - 1
Cnter = Cnter + 1
Select Case Cnter
Case 1: Me.SignCode1 = Sheets("ws1").Cells(r, "C")
Case 2: Me.SignCode2 = Sheets("ws1").Cells(r + 1, "C")
Case 3: Me.SignCode3 = Sheets("ws1").Cells(r + 2, "C")
Case 4: Me.SignCode4 = Sheets("ws1").Cells(r + 3, "C")
Case 5: Me.SignCode5 = Sheets("ws1").Cells(r + 4, "C")
Case 6: Me.SignCode6 = Sheets("ws1").Cells(r + 5, "C")
Case 7: Me.SignCode7 = Sheets("ws1").Cells(r + 6, "C")
Case 8: Me.SignCode8 = Sheets("ws1").Cells(r + 7, "C")
End Select
Next Cnt2
End Sub

 

[PSkinny]

Thanks again, I shall give it a go later today. I can't recall using case before so I wasn't sure how they worked but it looks like they are similar to IF statements.

 

[PSkinny]

Thank you NdNoviceHlp for the option of using 'Select case' but in the end I didn't use your answer. I have changed my approach to this problem and I now have it working as I wanted. Instead of the multiple signcodes being placed into their own row, I changed the layout of the sheet and the code so they now all appear on one row, this makes it easier (for me) to manage the entries.

 

[NdNoviceHlp]

Glad U got it resolved. Thanks for posting your outcome. Dave