PDA

View Full Version : Loop Help



JJanower
10-21-2011, 04:48 AM
Hey guys,
I am sure this is a very easy question to answer for most of you, but i just can t find the right code.
Private Sub CommandButton3_Click()
Dim StartTime As Date
Dim EndTime As Date

For i = 2 To 146 'end of the sheet1 team/name
For j = 3 To 15 'end of sheet1 dates
TempData = Worksheets("Sheet1").Cells(1, j) & Worksheets("Sheet1").Cells(i, 2)

k = 2

Do While Cells(k, 1) & Cells(k, 4) = TempData
StartTime = Cells(k, 5)
EndTime = Cells(k, 9)
Worksheets("Sheet1").Cells(i, j) = StartTime & "-" & EndTime
k = k + 1
Loop UNTIL k= 300

Next j
Next i

End Sub
I need this thing to loops until k = 300,, but obviuosly is not the correct way to express it in VBA, if you could give me a tipp I would appreciate it.

Paul_Hossler
10-21-2011, 05:33 AM
Maybe somethig like ...



k = 2

Do While Cells(k, 1) & Cells(k, 4) = TempData And K <= 300
StartTime = Cells(k, 5)
EndTime = Cells(k, 9)
Worksheets("Sheet1").Cells(i, j) = StartTime & "-" & EndTime
k = k + 1
Loop


Paul

Aflatoon
10-21-2011, 05:33 AM
I believe you mean:
Do While Cells(k, 1) & Cells(k, 4) = TempData And k < 300
StartTime = Cells(k, 5)
EndTime = Cells(k, 9)
Worksheets("Sheet1").Cells(i, j) = StartTime & "-" & EndTime
k = k + 1
Loop

JJanower
10-21-2011, 06:03 AM
Well actually is not working the way I wanted to :S

JJanower
10-21-2011, 06:06 AM
I think, the way I wrote this code is not the most efficient way cause it takes like 10 min or even more for my pc to get the whole information which isn't that many. Could I send you my file, so you can look at it and have a better overview of the issue ?

Paul_Hossler
10-21-2011, 09:12 AM
I believe you mean:


Aflatoon (http://www.vbaexpress.com/forum/member.php?u=24778) -- I believe you're right:doh:

JJanower (http://www.vbaexpress.com/forum/member.php?u=41984) - A loop within a loop within a loop will take a long time

Post a sample workbook with some of your data and the code, and I'm sure people will be willing and able to suggest alternatives.

Paul

mdmackillop
10-21-2011, 01:45 PM
Can you post a book with sample data.

JJanower
10-24-2011, 02:13 AM
So here is the thing. I would like to transform everything that is on Worksheets("Weekly") onto Worksheets("Sheet1"), so that is looks at the end, the way it looks in Worksheets("MyProgrammingGoal").
But this is what I ve gotten so far, please feel free to take a look in the attached File, I would appreciate every Help, advices, and comments.

I ve made comments to every Step that I ve written, Step 5) needs to be changed into a more efficient Code.

Thank very Much

JJanower
10-24-2011, 03:10 AM
Updated file*