Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Excel loop Need help
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default Excel loop Need help
I have tohe following code and it works for combining the data in
column B1 and B2 into B1 with commas if column A is the same name in A1
and A2. What I am also needing is the data in columns C-E to be merged
as well the same.
Col A ColB
ColC ColD Col E
BRAKE LINE RUBBING, KNOCKING 4 BT93 LY52486 8888
BRAKE LINE RUBBING, KNOCKING 4 BU31 LT91873 2807
DIFFERENTI. TRANSMISSION 4 DU53 LF77113
DOOR INNER HANDLE FUNCTION ISSUE 4 BU33 LW59323 1130
DOOR CUT-OUT WELD ISSUE 4 DU91 LK43454

The output of the script would be:
BRAKE LINE RUBBING, KNOCKING 4,4 BT93,BU31 LY52486,LT91873 8888,2807
DIFFERENTI. TRANSMISSION 4 DU53 LF77113
DOOR INNER HANDLE FUNCTION ISSUE 4 BU33 LW59323 1130
DOOR CUT-OUT WELD ISSUE 4 DU91 LK43454



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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Excel loop Need help
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
for j = 2 to 4
rngCell(1, j).Value = rngCell(1, j).Value & ", " & _
rngCell(lngR, j).Value
Next j
'rngCell(lngR, 2).EntireRow.Clear
lngR = lngR + 1
Loop
End If
Next
rngColumn.EntireRow.Sort key1:=Range("A1")
End Sub

--
Regards,
Tom Ogilvy

"dlh" wrote:

I have tohe following code and it works for combining the data in
column B1 and B2 into B1 with commas if column A is the same name in A1
and A2. What I am also needing is the data in columns C-E to be merged
as well the same.
Col A ColB
ColC ColD Col E
BRAKE LINE RUBBING, KNOCKING 4 BT93 LY52486 8888
BRAKE LINE RUBBING, KNOCKING 4 BU31 LT91873 2807
DIFFERENTI. TRANSMISSION 4 DU53 LF77113
DOOR INNER HANDLE FUNCTION ISSUE 4 BU33 LW59323 1130
DOOR CUT-OUT WELD ISSUE 4 DU91 LK43454

The output of the script would be:
BRAKE LINE RUBBING, KNOCKING 4,4 BT93,BU31 LY52486,LT91873 8888,2807
DIFFERENTI. TRANSMISSION 4 DU53 LF77113
DOOR INNER HANDLE FUNCTION ISSUE 4 BU33 LW59323 1130
DOOR CUT-OUT WELD ISSUE 4 DU91 LK43454



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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Excel loop Need help
for j = 2 to 4

should be

for j = 2 to 5

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

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
for j = 2 to 4
rngCell(1, j).Value = rngCell(1, j).Value & ", " & _
rngCell(lngR, j).Value
Next j
'rngCell(lngR, 2).EntireRow.Clear
lngR = lngR + 1
Loop
End If
Next
rngColumn.EntireRow.Sort key1:=Range("A1")
End Sub

--
Regards,
Tom Ogilvy

"dlh" wrote:

I have tohe following code and it works for combining the data in
column B1 and B2 into B1 with commas if column A is the same name in A1
and A2. What I am also needing is the data in columns C-E to be merged
as well the same.
Col A ColB
ColC ColD Col E
BRAKE LINE RUBBING, KNOCKING 4 BT93 LY52486 8888
BRAKE LINE RUBBING, KNOCKING 4 BU31 LT91873 2807
DIFFERENTI. TRANSMISSION 4 DU53 LF77113
DOOR INNER HANDLE FUNCTION ISSUE 4 BU33 LW59323 1130
DOOR CUT-OUT WELD ISSUE 4 DU91 LK43454

The output of the script would be:
BRAKE LINE RUBBING, KNOCKING 4,4 BT93,BU31 LY52486,LT91873 8888,2807
DIFFERENTI. TRANSMISSION 4 DU53 LF77113
DOOR INNER HANDLE FUNCTION ISSUE 4 BU33 LW59323 1130
DOOR CUT-OUT WELD ISSUE 4 DU91 LK43454



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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default Excel loop Need help

I declare j as long and it worked perfect

Tom Ogilvy wrote:
for j = 2 to 4

should be

for j = 2 to 5

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

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
for j = 2 to 4
rngCell(1, j).Value = rngCell(1, j).Value & ", " & _
rngCell(lngR, j).Value
Next j
'rngCell(lngR, 2).EntireRow.Clear
lngR = lngR + 1
Loop
End If
Next
rngColumn.EntireRow.Sort key1:=Range("A1")
End Sub

--
Regards,
Tom Ogilvy

"dlh" wrote:

I have tohe following code and it works for combining the data in
column B1 and B2 into B1 with commas if column A is the same name in A1
and A2. What I am also needing is the data in columns C-E to be merged
as well the same.
Col A ColB
ColC ColD Col E
BRAKE LINE RUBBING, KNOCKING 4 BT93 LY52486 8888
BRAKE LINE RUBBING, KNOCKING 4 BU31 LT91873 2807
DIFFERENTI. TRANSMISSION 4 DU53 LF77113
DOOR INNER HANDLE FUNCTION ISSUE 4 BU33 LW59323 1130
DOOR CUT-OUT WELD ISSUE 4 DU91 LK43454

The output of the script would be:
BRAKE LINE RUBBING, KNOCKING 4,4 BT93,BU31 LY52486,LT91873 8888,2807
DIFFERENTI. TRANSMISSION 4 DU53 LF77113
DOOR INNER HANDLE FUNCTION ISSUE 4 BU33 LW59323 1130
DOOR CUT-OUT WELD ISSUE 4 DU91 LK43454



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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
For Each ... Next loop - need to reference the loop variable [email protected] Excel Programming 4 July 13th 06 06:12 PM
Advancing outer Loop Based on criteria of inner loop ExcelMonkey Excel Programming 1 August 15th 05 05:23 PM
Loop Function unable to loop Junior728 Excel Programming 1 July 28th 05 10:23 AM
Problem adding charts using Do-Loop Until loop Chris Bromley[_2_] Excel Programming 2 May 23rd 05 01:31 PM
HELP!!!! Can't stop a loop (NOT an infinite loop) TBA[_2_] Excel Programming 3 December 14th 03 03:33 PM


All times are GMT +1. The time now is 02:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement