| 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 |
| 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 | |||
Hybrid Mode
