Vba to find the row number in the within a block of data

[Jim885]

I have the below data set.



I'm trying to transfer a given block of data onto a userform based on finding the value (ticket #) in column A
In the case of ticket# 50503 there are a total of four line items that need to be captured (columns E, F & G) and listed in the respected text boxes on a userform.
The amount of rows varies by each ticket number. Some have two lines, some have 3, 7 8 etc.,

What can I use and how can I find the last row within any given block of data?
Once found, how can a loop be setup for this?

Ticket #

Make

Model

Color

Part#

Qty

Service

Description

Tech

Expected Return

Returned to Shop

Invoice #

Complete or Partial

50500​

Chevy

Camaro

White

101

3

AA3

3 LINE SVC

Brian

3/26/2020

4/9/2020

65231

105

1

AA2

3 LINE SVC

1051.1

1

AB4

3 LINE CLR

50501​

Chevy

Corvette

Cream

101

3

AB8

3 LINE SVC

Matt

3/27/2020

4/9/2020 & 4/9/2020

15440 / 626

105

1

AA2

3 LINE SVC

50502​

Ford

Escape

Blue

101

4

AB2

3 LINE SVC

Chris

3/30/2020

4/9/2020

84805

105

1

AA2

3 LINE SVC

1011.1

1

215z

3 LINE CLR

1051.1

1

314z

3 LINE CLR

1051.2

1

11sz

3 LINE CLR

50503​

Toyota

Camry

Silver

101

1

AB2

3 LINE SVC

Steve

3/31/2020

4/9/2020

010810

1011.1

1

215z

3 LINE CLR

1051.1

1

314z

3 LINE CLR

1051.2

1

11z

3 LINE CLR

50504​

Ford

F-150

Black

1011

5

z2z

2 LINE SVC

Alex

4/1/2020

4/9/2020

515008

1012

1

z3z

2 LINE SVC

1013

3

z4z

2 LINE SVC

1014

2

z6z

2 LINE SVC

211

4

3sz

CLR

50505​

Dodge

Challenger

Silver

212

4

3sw

CLR

Charlie

4/2/2020

4/9/2020

00156

213

2

AW1

CLR

214

2

Aw6

CLR

215

1

z9z

MXA X

1051

2

zc5

MK4

50506​

Chevy

Malibu

White

1052

1

zc6

RGw

Brian

4/3/2020

4/9/2020

10510

1053

1

zc7

Feg

1161

2

zd6

RTG

50507​

BMW

640i

Red

1162

2

zd7

GXR

Steve

4/7/2020

4/11/2020

3215

1052

1

zdQ

GSE

50508​

BMW

440i

White

1011

3

z2z

2 LINE SVC

Jerry

4/8/2020

4/11/2020

12131

215

2

z9z

FEM

1051

1

zc5

PLG

1161

4

zd6

RWE

1162

2

zd7

ETW

107

6

ZC4

SSR

 

[petertenthije]

Are the numbers always sequential?

if so, you could use the matchformula to get the start row. You could then use the same formula to get the end row by searching for the next ticket in the sequence and deducting one.


edited: not find but match formula.

 

[Fluff]

For the first part, how about

Sub Jim()
   Dim Fnd As Range, Rng As Range
   Dim Ticket As Long
   
   Ticket = 50508
   Set Fnd = Range("A:A").Find(Ticket, , , xlWhole, , , , , False)
   If Fnd Is Nothing Then Exit Sub
   If Fnd.Offset(1).Value = "" Then
      Set Rng = Range(Fnd, Fnd.End(xlDown).Offset(-1))
   Else
      Set Rng = Fnd
   End If
   Set Rng = Rng.Offset(, 4).SpecialCells(xlConstants)
   Rng.Select
End Sub

 

[Jim885]

Yes, the numbers are sequential.

Could you provide an example of the matchformula? I don't know it.
Thank you.

 

[petertenthije]

Yes, the numbers are sequential.

Could you provide an example of the matchformula? I don't know it.
Thank you.

This should work. But it does require a seperate worksheet for calculations.

The source data (your table) is listed in worksheet Sheet2.
The calculation sheet is called CalculationSheet (no points for originality, I know)

Sub Macro1()

'Go to calculation sheet
    Sheets("CalculationSheet").Select


' Determine first and last ticket numbers
    Range("A6").FormulaR1C1 = "First ticket"
    Range("A7").FormulaR1C1 = "Last ticket"

    Range("B6").FormulaR1C1 = "=MIN(Sheet2!C[-1])"
    Range("B7").FormulaR1C1 = "=MAX(Sheet2!C[-1])"

    firstticket = Range("CalculationSheet!B6").Value
    lastticket = Range("CalculationSheet!B7").Value
    Numberoftickets = Range("CalculationSheet!B8").Value


Dim i As Long
For i = firstticket To lastticket


' Determine data range to be selected
    Sheets("CalculationSheet").Select
    Range("A1").FormulaR1C1 = "Search value"
    Range("A2").FormulaR1C1 = "First row"
    Range("A3").FormulaR1C1 = "Last row"

    Range("B1").FormulaR1C1 = i
    Range("B2").FormulaR1C1 = "=MATCH(R1C2,Sheet2!C1,0)"
    Range("B3").FormulaR1C1 = "=IF(R[-2]C=R[4]C,COUNTA(Sheet2!C[3]),MATCH(R1C2+1,Sheet2!C1,0)-1)"

    firstrow = Range("CalculationSheet!B2").Value
    lastrow = Range("CalculationSheet!B3").Value


' Select data
    Sheets("Sheet2").Select

    firstrow = Range("CalculationSheet!B2").Value
    lastrow = Range("CalculationSheet!B3").Value

    Range("E" & firstrow & ":H" & lastrow).Select


' TYPE HERE THE CODE FOR WHAT YOU WANT TO DO WITH THE SELECTED DATA


Next i

End Sub

 

[Jim885]

Peter,

Thank you very much. However, going to another sheet is not an option.
I need to stay within the module and keep the code short.

Fluff,

I'm working with your code, but don's see how it finds the last row in the block.

Once the last row is found in the block, I want to loop to the last row within the block and copy cell values onto the userform.

Perhaps what I have so far you, or someone can correct so it does what I need?

Since some tickets may have just one line item, my thought is to insert first Ticket Item row onto the userform. Then (if next row below ticket number is blank) loop for rest of the block and load those lines (Item #'s, Descriptions, and Quantitiy line items) onto Userform

   Me.txtPcMk1 = ws3.Cells(aRow, 7).Value
Me.txtItem1 = ws3.Cells(aRow, 10).Value
   Me.txtQS1 = ws3.Cells(aRow, 8).Value

   Dim Fnd As range, Rng As range
Dim Ticket As Long

Ticket = Me.txtTicket.Value
Set Fnd = range("C:C").Find(Ticket, , , xlWhole, , , , , False)
Set Rng = Fnd
' Set Rng = range(Fnd, Fnd.End(xlDown).Offset(-1))
If Fnd.Offset(1).Value = "" Then
For i = 2 To Last Row in block ' <------------------------------------ This is wat I need
Me.Controls("txtPcMk" & i) = ws3.Cells(aRow + 1, 7).Value
Me.Controls("txtItem" & i) = ws3.Cells(aRow + 1, 10).Value
Me.Controls("txtQS" & i) = ws3.Cells(aRow + 1, 8).Value
aRow = aRow +1
    Next i

 

[Fluff]

In your op the ticket is in col A & you said you wanted the value from columns E:G, your code is not doing that.
What column is the ticket number in & what columns should go in the textboxes?

 

[Jim885]

Fluff,
Thank you. I see my error

Thank you!

 

[Fluff]

Does this mean that you have sorted it?

 

[Jim885]

Yes, you gave me the solution I needed.

Thanks Fluff.
Much appreciated.