| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping thorough records
I have the following code that loops through the records and does fine but I
cannot get it to loop back through all of the records in column A. It looks at cell A1 and does the loop and stops. What am I doing wrong? Option Base 1 Public RelOpts() As String Sub GetRelOpt() x = 1 NextRow = 0 StrToMatch = Cells(1 + NextRow, 1).Value ReDim RelOpts(1 To x) Do While Cells(1 + NextRow, 1).Value < "" If Cells(1 + NextRow, 1).Value = StrToMatch Then RelOpts(x) = Cells(1 + NextRow, 2).Value x = x + 1 ReDim Preserve RelOpts(x) As String End If NextRow = NextRow + 1 Loop y = 1 Do Until RelOpts(y) = "" CCString = CCString + RelOpts(y) + " ," y = y + 1 Loop Cells(1, 5).Value = Left(CCString, (Len(CCString) - 2)) End Sub Deb |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping thorough records
Are you comparing Cell "A1" to the rest of the cells in column A or
are you looking for duplicates in column A ? You could get a start on figuring out the problem by declaring all of your variables. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Debi H" wrote in message I have the following code that loops through the records and does fine but I cannot get it to loop back through all of the records in column A. It looks at cell A1 and does the loop and stops. What am I doing wrong? Option Base 1 Public RelOpts() As String Sub GetRelOpt() x = 1 NextRow = 0 StrToMatch = Cells(1 + NextRow, 1).Value ReDim RelOpts(1 To x) Do While Cells(1 + NextRow, 1).Value < "" If Cells(1 + NextRow, 1).Value = StrToMatch Then RelOpts(x) = Cells(1 + NextRow, 2).Value x = x + 1 ReDim Preserve RelOpts(x) As String End If NextRow = NextRow + 1 Loop y = 1 Do Until RelOpts(y) = "" CCString = CCString + RelOpts(y) + " ," y = y + 1 Loop Cells(1, 5).Value = Left(CCString, (Len(CCString) - 2)) End Sub Deb |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping thorough records
I have a list of parts in column A that I loop through and if they are the
same I get the data from colunm B that matches all the same values in column A and insert it into C1. colA ColB door 1234 door 1234 door 5555 hood 5555 hood 6666 hood 1234 The output would be col A Col C door 1234, 5555 hood 5555,6666,1234 "Jim Cone" wrote: Are you comparing Cell "A1" to the rest of the cells in column A or are you looking for duplicates in column A ? You could get a start on figuring out the problem by declaring all of your variables. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Debi H" wrote in message I have the following code that loops through the records and does fine but I cannot get it to loop back through all of the records in column A. It looks at cell A1 and does the loop and stops. What am I doing wrong? Option Base 1 Public RelOpts() As String Sub GetRelOpt() x = 1 NextRow = 0 StrToMatch = Cells(1 + NextRow, 1).Value ReDim RelOpts(1 To x) Do While Cells(1 + NextRow, 1).Value < "" If Cells(1 + NextRow, 1).Value = StrToMatch Then RelOpts(x) = Cells(1 + NextRow, 2).Value x = x + 1 ReDim Preserve RelOpts(x) As String End If NextRow = NextRow + 1 Loop y = 1 Do Until RelOpts(y) = "" CCString = CCString + RelOpts(y) + " ," y = y + 1 Loop Cells(1, 5).Value = Left(CCString, (Len(CCString) - 2)) End Sub Deb |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping thorough records
Debi,
Move the "StrToMatch = Cells(1 + NextRow, 1).Value" line down so it is the first line in the Do loop... Do While Cells(1 + NextRow, 1).Value < "" StrToMatch = Cells(1 + NextRow, 1).Value If Cells(1 + NextRow, 1).Value = StrToMatch Then -- Jim Cone San Francisco, USA http://www.officeletter.com/blink/specialsort.html "Debi H" wrote in message I have a list of parts in column A that I loop through and if they are the same I get the data from colunm B that matches all the same values in column A and insert it into C1. colA ColB door 1234 door 1234 door 5555 hood 5555 hood 6666 hood 1234 The output would be col A Col C door 1234, 5555 hood 5555,6666,1234 "Jim Cone" wrote: Are you comparing Cell "A1" to the rest of the cells in column A or are you looking for duplicates in column A ? You could get a start on figuring out the problem by declaring all of your variables. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping thorough records
That got all of column B but put did put the field for column B when column A
changed col A Col C door 1234, 5555,5555,6666,1234 hood There should be the matching records for door and the matching records for door col A Col C door 1234, 5555 hood 5555,6666,1234 Does that make sense? "Jim Cone" wrote: Debi, Move the "StrToMatch = Cells(1 + NextRow, 1).Value" line down so it is the first line in the Do loop... Do While Cells(1 + NextRow, 1).Value < "" StrToMatch = Cells(1 + NextRow, 1).Value If Cells(1 + NextRow, 1).Value = StrToMatch Then -- Jim Cone San Francisco, USA http://www.officeletter.com/blink/specialsort.html "Debi H" wrote in message I have a list of parts in column A that I loop through and if they are the same I get the data from colunm B that matches all the same values in column A and insert it into C1. colA ColB door 1234 door 1234 door 5555 hood 5555 hood 6666 hood 1234 The output would be col A Col C door 1234, 5555 hood 5555,6666,1234 "Jim Cone" wrote: Are you comparing Cell "A1" to the rest of the cells in column A or are you looking for duplicates in column A ? You could get a start on figuring out the problem by declaring all of your variables. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping thorough records
Try playing around with this...
'------------------------ Sub GetRelOpt() Dim x As Long Dim NextRow As Long Dim strToMatch Dim rngCell As Excel.Range NextRow = 1 Do While Cells(1 + NextRow, 1).Value < "" x = 3 Set rngCell = Cells(NextRow, 1) strToMatch = rngCell.Value Do While Cells(1 + NextRow, 1).Value = strToMatch rngCell(1, x).Value = Cells(1 + NextRow, 2).Value NextRow = NextRow + 1 x = x + 1 Loop NextRow = NextRow + 1 Loop End Sub '----------- Jim Cone "Debi H" wrote in message That got all of column B but put did put the field for column B when column A changed col A Col C door 1234, 5555,5555,6666,1234 hood There should be the matching records for door and the matching records for door col A Col C door 1234, 5555 hood 5555,6666,1234 Does that make sense? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping thorough records
It still did not work and pick up the hood and put the data in the cell in
the B column that related to the hood. Also it put it in 3 different cells not one "Debi H" wrote: That got all of column B but put did put the field for column B when column A changed col A Col C door 1234, 5555,5555,6666,1234 hood There should be the matching records for door and the matching records for door col A Col C door 1234, 5555 hood 5555,6666,1234 Does that make sense? "Jim Cone" wrote: Debi, Move the "StrToMatch = Cells(1 + NextRow, 1).Value" line down so it is the first line in the Do loop... Do While Cells(1 + NextRow, 1).Value < "" StrToMatch = Cells(1 + NextRow, 1).Value If Cells(1 + NextRow, 1).Value = StrToMatch Then -- Jim Cone San Francisco, USA http://www.officeletter.com/blink/specialsort.html "Debi H" wrote in message I have a list of parts in column A that I loop through and if they are the same I get the data from colunm B that matches all the same values in column A and insert it into C1. colA ColB door 1234 door 1234 door 5555 hood 5555 hood 6666 hood 1234 The output would be col A Col C door 1234, 5555 hood 5555,6666,1234 "Jim Cone" wrote: Are you comparing Cell "A1" to the rest of the cells in column A or are you looking for duplicates in column A ? You could get a start on figuring out the problem by declaring all of your variables. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping thorough records
Sub GetRelOpt()
Dim lngR As Long Dim varToMatch As Variant Dim rngCell As Excel.Range Dim rngColumn As Excel.Range Set rngColumn = Range("A1", Cells(Rows.Count, 1).End(xlUp)) For Each rngCell In rngColumn If Len(rngCell.Value) Then varToMatch = rngCell.Value lngR = 2 Do While rngCell(lngR, 1).Value = varToMatch rngCell(1, 2).Value = rngCell(1, 2).Value & ", " & rngCell(lngR, 2).Value rngCell(lngR, 2).EntireRow.Clear lngR = lngR + 1 Loop End If Next rngColumn.EntireRow.Sort key1:=Range("A1") End Sub '------------ "Debi H" wrote in message It still did not work and pick up the hood and put the data in the cell in the B column that related to the hood. Also it put it in 3 different cells not one |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping thorough records
that worked great! thanks more thatn you know
"Jim Cone" wrote: Sub GetRelOpt() Dim lngR As Long Dim varToMatch As Variant Dim rngCell As Excel.Range Dim rngColumn As Excel.Range Set rngColumn = Range("A1", Cells(Rows.Count, 1).End(xlUp)) For Each rngCell In rngColumn If Len(rngCell.Value) Then varToMatch = rngCell.Value lngR = 2 Do While rngCell(lngR, 1).Value = varToMatch rngCell(1, 2).Value = rngCell(1, 2).Value & ", " & rngCell(lngR, 2).Value rngCell(lngR, 2).EntireRow.Clear lngR = lngR + 1 Loop End If Next rngColumn.EntireRow.Sort key1:=Range("A1") End Sub '------------ "Debi H" wrote in message It still did not work and pick up the hood and put the data in the cell in the B column that related to the hood. Also it put it in 3 different cells not one |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping thorough records
one thing I noticed......I have data in column 3-6 for that 2nd row as well
and when it takes the data and merges it from column 2 (B) it deletes the other date in columns 3-6. I gues I need to merge that data as well??? And as you will notice the last column may or maynot have data. BRAKE LINE RUBBING, KNOCKING 4 BT93 LY52486 8888 BRAKE LINE RUBBING, KNOCKING 4 BU31 LT91873 2807 DOOR INNER HANDLE FUNCTION 4 BU33 LW59323 1130 DOOR CUT-OUT WELD ISSUE 4 DU91 LK43454 FOG REARLIGHT FUNCTION 4 DU52 LF68094 1130 "Jim Cone" wrote: Sub GetRelOpt() Dim lngR As Long Dim varToMatch As Variant Dim rngCell As Excel.Range Dim rngColumn As Excel.Range Set rngColumn = Range("A1", Cells(Rows.Count, 1).End(xlUp)) For Each rngCell In rngColumn If Len(rngCell.Value) Then varToMatch = rngCell.Value lngR = 2 Do While rngCell(lngR, 1).Value = varToMatch rngCell(1, 2).Value = rngCell(1, 2).Value & ", " & rngCell(lngR, 2).Value rngCell(lngR, 2).EntireRow.Clear lngR = lngR + 1 Loop End If Next rngColumn.EntireRow.Sort key1:=Range("A1") End Sub '------------ "Debi H" wrote in message It still did not work and pick up the hood and put the data in the cell in the B column that related to the hood. Also it put it in 3 different cells not one |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping thorough records
You can just add as many cells as you need to ...
'2 is the column number & rngCell(lngR, 2).Value so you would have... & rngCell(lngR, 2).Value & ", " & rngCell(lngR, 3).Value & ", " & rngCell(lngR, 4).Value 'and so on. Jim Cone "Debi H" wrote in message one thing I noticed......I have data in column 3-6 for that 2nd row as well and when it takes the data and merges it from column 2 (B) it deletes the other date in columns 3-6. I gues I need to merge that data as well??? And as you will notice the last column may or maynot have data. BRAKE LINE RUBBING, KNOCKING 4 BT93 LY52486 8888 BRAKE LINE RUBBING, KNOCKING 4 BU31 LT91873 2807 DOOR INNER HANDLE FUNCTION 4 BU33 LW59323 1130 DOOR CUT-OUT WELD ISSUE 4 DU91 LK43454 FOG REARLIGHT FUNCTION 4 DU52 LF68094 1130 "Jim Cone" wrote: Sub GetRelOpt() Dim lngR As Long Dim varToMatch As Variant Dim rngCell As Excel.Range Dim rngColumn As Excel.Range Set rngColumn = Range("A1", Cells(Rows.Count, 1).End(xlUp)) For Each rngCell In rngColumn If Len(rngCell.Value) Then varToMatch = rngCell.Value lngR = 2 Do While rngCell(lngR, 1).Value = varToMatch rngCell(1, 2).Value = rngCell(1, 2).Value & ", " & rngCell(lngR, 2).Value rngCell(lngR, 2).EntireRow.Clear lngR = lngR + 1 Loop End If Next rngColumn.EntireRow.Sort key1:=Range("A1") End Sub '------------ "Debi H" wrote in message It still did not work and pick up the hood and put the data in the cell in the B column that related to the hood. Also it put it in 3 different cells not one |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping thorough records
ok that worked but it put all of the data from column C-G into one cell in
column B I gues what I am trying to say is it is working good but the results should be BRAKE LINE RUBBING, KNOCKING 4 BT93 LY52486 8888 BRAKE LINE RUBBING, KNOCKING 4 BU31 LT91873 2807 column a BRAKE LINE RUBBING, KNOCKING column b 4,4 column c BT93, BU93 column D LY52486, LT91876 column E 8888,2807 Does that make sense? "Jim Cone" wrote: You can just add as many cells as you need to ... '2 is the column number & rngCell(lngR, 2).Value so you would have... & rngCell(lngR, 2).Value & ", " & rngCell(lngR, 3).Value & ", " & rngCell(lngR, 4).Value 'and so on. Jim Cone "Debi H" wrote in message one thing I noticed......I have data in column 3-6 for that 2nd row as well and when it takes the data and merges it from column 2 (B) it deletes the other date in columns 3-6. I gues I need to merge that data as well??? And as you will notice the last column may or maynot have data. BRAKE LINE RUBBING, KNOCKING 4 BT93 LY52486 8888 BRAKE LINE RUBBING, KNOCKING 4 BU31 LT91873 2807 DOOR INNER HANDLE FUNCTION 4 BU33 LW59323 1130 DOOR CUT-OUT WELD ISSUE 4 DU91 LK43454 FOG REARLIGHT FUNCTION 4 DU52 LF68094 1130 "Jim Cone" wrote: Sub GetRelOpt() Dim lngR As Long Dim varToMatch As Variant Dim rngCell As Excel.Range Dim rngColumn As Excel.Range Set rngColumn = Range("A1", Cells(Rows.Count, 1).End(xlUp)) For Each rngCell In rngColumn If Len(rngCell.Value) Then varToMatch = rngCell.Value lngR = 2 Do While rngCell(lngR, 1).Value = varToMatch rngCell(1, 2).Value = rngCell(1, 2).Value & ", " & rngCell(lngR, 2).Value rngCell(lngR, 2).EntireRow.Clear lngR = lngR + 1 Loop End If Next rngColumn.EntireRow.Sort key1:=Range("A1") End Sub '------------ "Debi H" wrote in message It still did not work and pick up the hood and put the data in the cell in the B column that related to the hood. Also it put it in 3 different cells not one |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping thorough records
No it doesn't and I am losing interest.
Regards, Jim Cone "Debi H" wrote in message ok that worked but it put all of the data from column C-G into one cell in column B I gues what I am trying to say is it is working good but the results should be BRAKE LINE RUBBING, KNOCKING 4 BT93 LY52486 8888 BRAKE LINE RUBBING, KNOCKING 4 BU31 LT91873 2807 column a BRAKE LINE RUBBING, KNOCKING column b 4,4 column c BT93, BU93 column D LY52486, LT91876 column E 8888,2807 Does that make sense? "Jim Cone" wrote: You can just add as many cells as you need to ... '2 is the column number & rngCell(lngR, 2).Value so you would have... & rngCell(lngR, 2).Value & ", " & rngCell(lngR, 3).Value & ", " & rngCell(lngR, 4).Value 'and so on. Jim Cone "Debi H" wrote in message one thing I noticed......I have data in column 3-6 for that 2nd row as well and when it takes the data and merges it from column 2 (B) it deletes the other date in columns 3-6. I gues I need to merge that data as well??? And as you will notice the last column may or maynot have data. BRAKE LINE RUBBING, KNOCKING 4 BT93 LY52486 8888 BRAKE LINE RUBBING, KNOCKING 4 BU31 LT91873 2807 DOOR INNER HANDLE FUNCTION 4 BU33 LW59323 1130 DOOR CUT-OUT WELD ISSUE 4 DU91 LK43454 FOG REARLIGHT FUNCTION 4 DU52 LF68094 1130 "Jim Cone" wrote: Sub GetRelOpt() Dim lngR As Long Dim varToMatch As Variant Dim rngCell As Excel.Range Dim rngColumn As Excel.Range Set rngColumn = Range("A1", Cells(Rows.Count, 1).End(xlUp)) For Each rngCell In rngColumn If Len(rngCell.Value) Then varToMatch = rngCell.Value lngR = 2 Do While rngCell(lngR, 1).Value = varToMatch rngCell(1, 2).Value = rngCell(1, 2).Value & ", " & rngCell(lngR, 2).Value rngCell(lngR, 2).EntireRow.Clear lngR = lngR + 1 Loop End If Next rngColumn.EntireRow.Sort key1:=Range("A1") End Sub '------------ "Debi H" wrote in message It still did not work and pick up the hood and put the data in the cell in the B column that related to the hood. Also it put it in 3 different cells not one |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping thorough records
sorry to bore you....Just did not want all of the data in one cell on the one
comining "Jim Cone" wrote: No it doesn't and I am losing interest. Regards, Jim Cone "Debi H" wrote in message ok that worked but it put all of the data from column C-G into one cell in column B I gues what I am trying to say is it is working good but the results should be BRAKE LINE RUBBING, KNOCKING 4 BT93 LY52486 8888 BRAKE LINE RUBBING, KNOCKING 4 BU31 LT91873 2807 column a BRAKE LINE RUBBING, KNOCKING column b 4,4 column c BT93, BU93 column D LY52486, LT91876 column E 8888,2807 Does that make sense? "Jim Cone" wrote: You can just add as many cells as you need to ... '2 is the column number & rngCell(lngR, 2).Value so you would have... & rngCell(lngR, 2).Value & ", " & rngCell(lngR, 3).Value & ", " & rngCell(lngR, 4).Value 'and so on. Jim Cone "Debi H" wrote in message one thing I noticed......I have data in column 3-6 for that 2nd row as well and when it takes the data and merges it from column 2 (B) it deletes the other date in columns 3-6. I gues I need to merge that data as well??? And as you will notice the last column may or maynot have data. BRAKE LINE RUBBING, KNOCKING 4 BT93 LY52486 8888 BRAKE LINE RUBBING, KNOCKING 4 BU31 LT91873 2807 DOOR INNER HANDLE FUNCTION 4 BU33 LW59323 1130 DOOR CUT-OUT WELD ISSUE 4 DU91 LK43454 FOG REARLIGHT FUNCTION 4 DU52 LF68094 1130 "Jim Cone" wrote: Sub GetRelOpt() Dim lngR As Long Dim varToMatch As Variant Dim rngCell As Excel.Range Dim rngColumn As Excel.Range Set rngColumn = Range("A1", Cells(Rows.Count, 1).End(xlUp)) For Each rngCell In rngColumn If Len(rngCell.Value) Then varToMatch = rngCell.Value lngR = 2 Do While rngCell(lngR, 1).Value = varToMatch rngCell(1, 2).Value = rngCell(1, 2).Value & ", " & rngCell(lngR, 2).Value rngCell(lngR, 2).EntireRow.Clear lngR = lngR + 1 Loop End If Next rngColumn.EntireRow.Sort key1:=Range("A1") End Sub '------------ "Debi H" wrote in message It still did not work and pick up the hood and put the data in the cell in the B column that related to the hood. Also it put it in 3 different cells not one |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping thorough records
Well if you think of a way to do what I am trying to do let me
know..... Jim Cone wrote: No it doesn't and I am losing interest. Regards, Jim Cone "Debi H" wrote in message ok that worked but it put all of the data from column C-G into one cell in column B I gues what I am trying to say is it is working good but the results should be BRAKE LINE RUBBING, KNOCKING 4 BT93 LY52486 8888 BRAKE LINE RUBBING, KNOCKING 4 BU31 LT91873 2807 column a BRAKE LINE RUBBING, KNOCKING column b 4,4 column c BT93, BU93 column D LY52486, LT91876 column E 8888,2807 Does that make sense? "Jim Cone" wrote: You can just add as many cells as you need to ... '2 is the column number & rngCell(lngR, 2).Value so you would have... & rngCell(lngR, 2).Value & ", " & rngCell(lngR, 3).Value & ", " & rngCell(lngR, 4).Value 'and so on. Jim Cone "Debi H" wrote in message one thing I noticed......I have data in column 3-6 for that 2nd row as well and when it takes the data and merges it from column 2 (B) it deletes the other date in columns 3-6. I gues I need to merge that data as well??? And as you will notice the last column may or maynot have data. BRAKE LINE RUBBING, KNOCKING 4 BT93 LY52486 8888 BRAKE LINE RUBBING, KNOCKING 4 BU31 LT91873 2807 DOOR INNER HANDLE FUNCTION 4 BU33 LW59323 1130 DOOR CUT-OUT WELD ISSUE 4 DU91 LK43454 FOG REARLIGHT FUNCTION 4 DU52 LF68094 1130 "Jim Cone" wrote: Sub GetRelOpt() Dim lngR As Long Dim varToMatch As Variant Dim rngCell As Excel.Range Dim rngColumn As Excel.Range Set rngColumn = Range("A1", Cells(Rows.Count, 1).End(xlUp)) For Each rngCell In rngColumn If Len(rngCell.Value) Then varToMatch = rngCell.Value lngR = 2 Do While rngCell(lngR, 1).Value = varToMatch rngCell(1, 2).Value = rngCell(1, 2).Value & ", " & rngCell(lngR, 2).Value rngCell(lngR, 2).EntireRow.Clear lngR = lngR + 1 Loop End If Next rngColumn.EntireRow.Sort key1:=Range("A1") End Sub '------------ "Debi H" wrote in message It still did not work and pick up the hood and put the data in the cell in the B column that related to the hood. Also it put it in 3 different cells not one |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping thorough records
Well if you think of a way to do what I am trying to do let me
know..... Jim Cone wrote: No it doesn't and I am losing interest. Regards, Jim Cone "Debi H" wrote in message ok that worked but it put all of the data from column C-G into one cell in column B I gues what I am trying to say is it is working good but the results should be BRAKE LINE RUBBING, KNOCKING 4 BT93 LY52486 8888 BRAKE LINE RUBBING, KNOCKING 4 BU31 LT91873 2807 column a BRAKE LINE RUBBING, KNOCKING column b 4,4 column c BT93, BU93 column D LY52486, LT91876 column E 8888,2807 Does that make sense? "Jim Cone" wrote: You can just add as many cells as you need to ... '2 is the column number & rngCell(lngR, 2).Value so you would have... & rngCell(lngR, 2).Value & ", " & rngCell(lngR, 3).Value & ", " & rngCell(lngR, 4).Value 'and so on. Jim Cone "Debi H" wrote in message one thing I noticed......I have data in column 3-6 for that 2nd row as well and when it takes the data and merges it from column 2 (B) it deletes the other date in columns 3-6. I gues I need to merge that data as well??? And as you will notice the last column may or maynot have data. BRAKE LINE RUBBING, KNOCKING 4 BT93 LY52486 8888 BRAKE LINE RUBBING, KNOCKING 4 BU31 LT91873 2807 DOOR INNER HANDLE FUNCTION 4 BU33 LW59323 1130 DOOR CUT-OUT WELD ISSUE 4 DU91 LK43454 FOG REARLIGHT FUNCTION 4 DU52 LF68094 1130 "Jim Cone" wrote: Sub GetRelOpt() Dim lngR As Long Dim varToMatch As Variant Dim rngCell As Excel.Range Dim rngColumn As Excel.Range Set rngColumn = Range("A1", Cells(Rows.Count, 1).End(xlUp)) For Each rngCell In rngColumn If Len(rngCell.Value) Then varToMatch = rngCell.Value lngR = 2 Do While rngCell(lngR, 1).Value = varToMatch rngCell(1, 2).Value = rngCell(1, 2).Value & ", " & rngCell(lngR, 2).Value rngCell(lngR, 2).EntireRow.Clear lngR = lngR + 1 Loop End If Next rngColumn.EntireRow.Sort key1:=Range("A1") End Sub '------------ "Debi H" wrote in message It still did not work and pick up the hood and put the data in the cell in the B column that related to the hood. Also it put it in 3 different cells not one |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Delete records when certain records have duplicate column data | New Users to Excel | |||
| draw 999 x 8 random records from file with 8614 records | Excel Programming | |||
| Delete two records together with looping | Excel Programming | |||
| urgent. looping thru all records and applynig formula to get new results | Excel Programming | |||
| Looping Through Records. | Excel Programming | |||
Linear Mode
