I have a spreadsheet that looks something like this:

Item Fac Type Oper# Seq# Description
AA11 10 R 10 1 FT
AA11 10 R 10 2 108
AA11 10 R 10 3 110
AA11 10 R 10 4 112
AA11 10 R 10 5 FT
AA11 10 R 10 6 144
AA11 10 R 10 7 146
AA11 10 R 10 8 148

The spreadsheet has about 8,000 rows and is one of many I will be getting
that will need to be reformatted. What I need it to look like is this:

Item Fac Type Oper# Routing Description
AA11 10 R 10 WID 108.00-110.00-112.00
AA11 10 R 10 LEN 144.00-146.00-148.00

My brain seems to be stuck on how to begin...how do I define the data in the
spreadsheet as a recordset so VB will know what cells to use as input data ?

So far, all the VB coding I have done has had external data as the
recordset, this one has part of the Excel spreadsheet....Please help !

Thanks !

Your example gives no indication what you want to do. How do you determine
the routing? I see how you're getting the description, but is .00 always
added to the previous description #'s. Also, what do you do with the FT in
the first record? You need to provide more specifics and I think I can help
you.

"Eric @ BP-EVV" wrote:

I have a spreadsheet that looks something like this:

Item Fac Type Oper# Seq# Description
AA11 10 R 10 1 FT
AA11 10 R 10 2 108
AA11 10 R 10 3 110
AA11 10 R 10 4 112
AA11 10 R 10 5 FT
AA11 10 R 10 6 144
AA11 10 R 10 7 146
AA11 10 R 10 8 148

The spreadsheet has about 8,000 rows and is one of many I will be getting
that will need to be reformatted. What I need it to look like is this:

Item Fac Type Oper# Routing Description
AA11 10 R 10 WID 108.00-110.00-112.00
AA11 10 R 10 LEN 144.00-146.00-148.00

My brain seems to be stuck on how to begin...how do I define the data in the
spreadsheet as a recordset so VB will know what cells to use as input data ?

So far, all the VB coding I have done has had external data as the
recordset, this one has part of the Excel spreadsheet....Please help !

Thanks !

There are specific things that need to happen for each seq# value...the
record is only important if the seq# is 2 - 4, 6 - 8, 14 - 16, & 18 -
20...all other records can simply be ignored. The routing is likewise driven
by the seq#...there are specific values for each of the 4 ranges of seq#. I
can handle that part of the code with a select...case statement. Basically
I'm trying to write a routine that will take the descriptions from the
previously mentioned seq# ranges and concatenate them into one description,
with each portion being formatted to xxx.xx and separated by a "-" as the
field this data will ultimately be uploaded into has a limit of 20 characters.

I'm so used to using an external data source that I seem to be stuck on how
to define to VB the range of cells in the current spreadsheet that contain
the data I need to manipulate. This is really the only assistance I need.

Thanks in advance !


"Mike H." wrote:

Your example gives no indication what you want to do. How do you determine
the routing? I see how you're getting the description, but is .00 always
added to the previous description #'s. Also, what do you do with the FT in
the first record? You need to provide more specifics and I think I can help
you.

"Eric @ BP-EVV" wrote:

I have a spreadsheet that looks something like this:

Item Fac Type Oper# Seq# Description
AA11 10 R 10 1 FT
AA11 10 R 10 2 108
AA11 10 R 10 3 110
AA11 10 R 10 4 112
AA11 10 R 10 5 FT
AA11 10 R 10 6 144
AA11 10 R 10 7 146
AA11 10 R 10 8 148

The spreadsheet has about 8,000 rows and is one of many I will be getting
that will need to be reformatted. What I need it to look like is this:

Item Fac Type Oper# Routing Description
AA11 10 R 10 WID 108.00-110.00-112.00
AA11 10 R 10 LEN 144.00-146.00-148.00

My brain seems to be stuck on how to begin...how do I define the data in the
spreadsheet as a recordset so VB will know what cells to use as input data ?

So far, all the VB coding I have done has had external data as the
recordset, this one has part of the Excel spreadsheet....Please help !

Thanks !

This will work. It assumes data starts on row 2 of sheet1 and you are to
place the data in sheet2. Modify as needed.

Sub NewDo()
Dim Item As Variant
Dim Fac As Variant
Dim TheType As Variant
Dim Oper As Variant
Dim StartSeq As String
Dim X As Double
Dim Y As Double

X = 2
Do While True
If Cells(X, 1).Value = Empty Then Exit Do
If Cells(X, 6) = "FT" And Len(StartSeq) < 1 Then
Let Item = Cells(X, 1).Value
Let Fac = Cells(X, 2).Value
Let TheType = Cells(X, 3).Value
Let Oper = Cells(X, 4).Value
If Cells(X, 6).Value < "FT" Then
Let StartSeq = Cells(X, 6).Value & ".00"
Else
Let StartSeq = "START"
End If
Else
If Cells(X, 6).Value < "FT" Then
If Len(StartSeq) = 5 Then
Let StartSeq = StartSeq & Cells(X, 6).Value & ".00"
Else
Let StartSeq = StartSeq & "-" & Cells(X, 6).Value & ".00"
End If
End If
End If
If Cells(X, 6).Value = "FT" And Len(StartSeq) 5 Then
Sheets("Sheet2").Select
Y = Y + 1
nextcell = nextcell + 1
Cells(nextcell, 1).Value = Item
Cells(nextcell, 2).Value = Fac
Cells(nextcell, 3).Value = TheType
Cells(nextcell, 4).Value = Oper
Cells(nextcell, 6).Value = Right(StartSeq, Len(StartSeq) - 5)
Sheets("Sheet1").Select
StartSeq = Empty
X = X - 1
End If
X = X + 1
Loop


End Sub

"Eric @ BP-EVV" wrote:

There are specific things that need to happen for each seq# value...the
record is only important if the seq# is 2 - 4, 6 - 8, 14 - 16, & 18 -
20...all other records can simply be ignored. The routing is likewise driven
by the seq#...there are specific values for each of the 4 ranges of seq#. I
can handle that part of the code with a select...case statement. Basically
I'm trying to write a routine that will take the descriptions from the
previously mentioned seq# ranges and concatenate them into one description,
with each portion being formatted to xxx.xx and separated by a "-" as the
field this data will ultimately be uploaded into has a limit of 20 characters.

I'm so used to using an external data source that I seem to be stuck on how
to define to VB the range of cells in the current spreadsheet that contain
the data I need to manipulate. This is really the only assistance I need.

Thanks in advance !


"Mike H." wrote:

Your example gives no indication what you want to do. How do you determine
the routing? I see how you're getting the description, but is .00 always
added to the previous description #'s. Also, what do you do with the FT in
the first record? You need to provide more specifics and I think I can help
you.

"Eric @ BP-EVV" wrote:

I have a spreadsheet that looks something like this:

Item Fac Type Oper# Seq# Description
AA11 10 R 10 1 FT
AA11 10 R 10 2 108
AA11 10 R 10 3 110
AA11 10 R 10 4 112
AA11 10 R 10 5 FT
AA11 10 R 10 6 144
AA11 10 R 10 7 146
AA11 10 R 10 8 148

The spreadsheet has about 8,000 rows and is one of many I will be getting
that will need to be reformatted. What I need it to look like is this:

Item Fac Type Oper# Routing Description
AA11 10 R 10 WID 108.00-110.00-112.00
AA11 10 R 10 LEN 144.00-146.00-148.00

My brain seems to be stuck on how to begin...how do I define the data in the
spreadsheet as a recordset so VB will know what cells to use as input data ?

So far, all the VB coding I have done has had external data as the
recordset, this one has part of the Excel spreadsheet....Please help !

Thanks !

Thanks Mike....I'm looking at code and modifying as needed.....looks like it
will do what I want.

Thanks !

"Mike H." wrote:

This will work. It assumes data starts on row 2 of sheet1 and you are to
place the data in sheet2. Modify as needed.

Sub NewDo()
Dim Item As Variant
Dim Fac As Variant
Dim TheType As Variant
Dim Oper As Variant
Dim StartSeq As String
Dim X As Double
Dim Y As Double

X = 2
Do While True
If Cells(X, 1).Value = Empty Then Exit Do
If Cells(X, 6) = "FT" And Len(StartSeq) < 1 Then
Let Item = Cells(X, 1).Value
Let Fac = Cells(X, 2).Value
Let TheType = Cells(X, 3).Value
Let Oper = Cells(X, 4).Value
If Cells(X, 6).Value < "FT" Then
Let StartSeq = Cells(X, 6).Value & ".00"
Else
Let StartSeq = "START"
End If
Else
If Cells(X, 6).Value < "FT" Then
If Len(StartSeq) = 5 Then
Let StartSeq = StartSeq & Cells(X, 6).Value & ".00"
Else
Let StartSeq = StartSeq & "-" & Cells(X, 6).Value & ".00"
End If
End If
End If
If Cells(X, 6).Value = "FT" And Len(StartSeq) 5 Then
Sheets("Sheet2").Select
Y = Y + 1
nextcell = nextcell + 1
Cells(nextcell, 1).Value = Item
Cells(nextcell, 2).Value = Fac
Cells(nextcell, 3).Value = TheType
Cells(nextcell, 4).Value = Oper
Cells(nextcell, 6).Value = Right(StartSeq, Len(StartSeq) - 5)
Sheets("Sheet1").Select
StartSeq = Empty
X = X - 1
End If
X = X + 1
Loop


End Sub

"Eric @ BP-EVV" wrote:

There are specific things that need to happen for each seq# value...the
record is only important if the seq# is 2 - 4, 6 - 8, 14 - 16, & 18 -
20...all other records can simply be ignored. The routing is likewise driven
by the seq#...there are specific values for each of the 4 ranges of seq#. I
can handle that part of the code with a select...case statement. Basically
I'm trying to write a routine that will take the descriptions from the
previously mentioned seq# ranges and concatenate them into one description,
with each portion being formatted to xxx.xx and separated by a "-" as the
field this data will ultimately be uploaded into has a limit of 20 characters.

I'm so used to using an external data source that I seem to be stuck on how
to define to VB the range of cells in the current spreadsheet that contain
the data I need to manipulate. This is really the only assistance I need.

Thanks in advance !


"Mike H." wrote:

Your example gives no indication what you want to do. How do you determine
the routing? I see how you're getting the description, but is .00 always
added to the previous description #'s. Also, what do you do with the FT in
the first record? You need to provide more specifics and I think I can help
you.

"Eric @ BP-EVV" wrote:

I have a spreadsheet that looks something like this:

Item Fac Type Oper# Seq# Description
AA11 10 R 10 1 FT
AA11 10 R 10 2 108
AA11 10 R 10 3 110
AA11 10 R 10 4 112
AA11 10 R 10 5 FT
AA11 10 R 10 6 144
AA11 10 R 10 7 146
AA11 10 R 10 8 148

The spreadsheet has about 8,000 rows and is one of many I will be getting
that will need to be reformatted. What I need it to look like is this:

Item Fac Type Oper# Routing Description
AA11 10 R 10 WID 108.00-110.00-112.00
AA11 10 R 10 LEN 144.00-146.00-148.00

My brain seems to be stuck on how to begin...how do I define the data in the
spreadsheet as a recordset so VB will know what cells to use as input data ?

So far, all the VB coding I have done has had external data as the
recordset, this one has part of the Excel spreadsheet....Please help !

Thanks !