Need help with looping

P

pyro77

New Member
Joined
Nov 16, 2008
Messages
20
Hey guys, I'm quite new to excel VBA and I need some help with looping

Here's what I have so far

Sub gendata()
'do while loop loops through gendata until it reaches an empty cell

Do
'required parameters
mth = Sheets("Info").Range("B1").Value
srcFile = Sheets("Info").Range("B2").Value
rowPerHr = Sheets("Info").Range("B3").Value
stStart = Sheets("Info").Range("B4").Value
oilStart = Sheets("Info").Range("B5").Value
otherStart = Sheets("Info").Range("B6").Value
allStart = Sheets("Info").Range("B7").Value
'add a new worksheet
Worksheets.Add().Name = mth

'fill in the data
Call draw_table(1, 1, "ST Job")
Call draw_table(31, 1, "Oil Job")
Call draw_table(61, 1, "Other Job")
Call draw_table(91, 1, "Total Job")

'ST Job
Call gen_section(mth, srcFile, stStart, 3, rowPerHr)

'Oil Job
Call gen_section(mth, srcFile, oilStart, 33, rowPerHr)

'Other Job
Call gen_section(mth, srcFile, otherStart, 63, rowPerHr)

'All Job
Call gen_section(mth, srcFile, allStart, 93, rowPerHr)

ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell.Offset(0, 1))

End Sub
Click to expand...

Ok, basically, this is the top part of my codes. It generates a report which are new sheets in the same workbook.

So this loops checks the parameters and calculates the necessary calculation and writes out the report. Once it reaches an empty cell it will stop.

Now, the code above generates the June report, taking in parameters from cells B1, B2, B3 and so on.

So when this loop is done, I want it to move to C1, C2, C3 etc. And then after that is done, D1, D2, D3 and so on and so forth until it reaches and empty cell.

I'm thinking of a For.. Next loop but I can't get it to work.

Any ideas?

Thanks :)
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Hi
Try these codes
Code: 
Dim x As Long, a As Long
x = Sheets("info").Cells(1, Columns.Count).End(xlToLeft).Column
For a = 2 To x
Do
'required parameters
mth = Sheets("Info").Range(Chr(a + 64) & 1).Value
srcFile = Sheets("Info").Range(Chr(a + 64) & 2).Value
rowPerHr = Sheets("Info").Range(Chr(a + 64) & 3).Value
stStart = Sheets("Info").Range(Chr(a + 64) & 4).Value
oilStart = Sheets("Info").Range(Chr(a + 64) & 5).Value
otherStart = Sheets("Info").Range(Chr(a + 64) & 6).Value
allStart = Sheets("Info").Range(Chr(a + 64) & 7).Value
'----- all other codes----
Next a
Ravi
 
Upvote 0
Hey Ravi,
Thanks for the help. However, I think it's not working very well.

It seems like it runs once and then it throws and error saying something like "
Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic.
This is a different error that I got from my previous coding.

Maybe it's the placing of "Next a", cause I'm not very sure where to place it, I placed it in after the line "Loop Until IsEmpty(ActiveCell.Offset(0, 1))" and before End Sub. Is that correct?

Oh and when I clicked on Debug, it highlighted "Worksheets.Add().Name = mth"
 
Upvote 0
Ok, I managed to get it to work. I placed the For.. Next loop inside the Do Until loop. Now when it reaches the end, it gives me the same error as above.

Do you know how to stop it when it reaches an empty cell, mine isn't working very well.
 
Upvote 0
Here's what I have so far

Sub gendata()
'do while loop loops through gendata until it reaches an empty cell

Dim x As Long, a As Long
x = Sheets("info").Cells(1, Columns.Count).End(xlToLeft).Column

Do
For a = 2 To x
'required parameters
mth = Sheets("Info").Range(Chr(a + 64) & 1).Value
srcFile = Sheets("Info").Range(Chr(a + 64) & 2).Value
rowPerHr = Sheets("Info").Range(Chr(a + 64) & 3).Value
stStart = Sheets("Info").Range(Chr(a + 64) & 4).Value
oilStart = Sheets("Info").Range(Chr(a + 64) & 5).Value
otherStart = Sheets("Info").Range(Chr(a + 64) & 6).Value
allStart = Sheets("Info").Range(Chr(a + 64) & 7).Value

'add a new worksheet
Worksheets.Add().Name = mth

'fill in the data
Call draw_table(1, 1, "ST Job")
Call draw_table(31, 1, "Oil Job")
Call draw_table(61, 1, "Other Job")
Call draw_table(91, 1, "Total Job")

'ST Job
Call gen_section(mth, srcFile, stStart, 3, rowPerHr)

'Oil Job
Call gen_section(mth, srcFile, oilStart, 33, rowPerHr)

'Other Job
Call gen_section(mth, srcFile, otherStart, 63, rowPerHr)

'All Job
Call gen_section(mth, srcFile, allStart, 93, rowPerHr)

ActiveCell.Offset(1, 0).Select
Next a
Loop Until IsEmpty(ActiveCell.Offset(0, 1))


End Sub
Click to expand...
 
Upvote 0
Haha ok Ravi, I managed to get it to work finally. I removed the Do Until loop totally. Maybe that's what you had in mind too?

Anyway, if it isn't too much trouble, could you explain what you did?

Particularly this portion
Dim x As Long, a As Long
x = Sheets("info").Cells(1, Columns.Count).End(xlToLeft).Column
Click to expand...
I'm new to VBA so I'm not sure what this means?

Also, what does (a+64) mean?

Thanks :)
 
Upvote 0
pyro77 said:
Also, what does (a+64) mean?

Thanks :)
Click to expand...

Ravi is converting the number 2 to the number 66 (by adding 64), then this is being run through the converter to find it's ASCII value (which is "b") this is then your column reference, you could also do this with an R1C1 style reference with something like:

Code: 
mth = Sheets("Info").cells(1,a).Value

This

Code: 
Range(Chr(a + 64) & 1).Value
Translates to the value of B2 on the spreadsheet.

The problem with using the ASCII cell reference like this (And is why I switched to R1C1) is that when you have data past column Z you will have issues.
 
Upvote 0
Hi
first line defines the dimensions(dim) for variable x and a as long
2nd line finds the last column which has data.
As blade hunter pointed out chr returns the character of a number. Character A to Z falls in 65 to 90 so I added 64 to a to derive the column alphabets.
Essentially the for-next-loop goes thro' each column one at a time thro your macro
Ravi
 
Upvote 0
Blade Hunter said:
Ravi is converting the number 2 to the number 66 (by adding 64), then this is being run through the converter to find it's ASCII value (which is "b") this is then your column reference, you could also do this with an R1C1 style reference with something like:

Code: 
mth = Sheets("Info").cells(1,a).Value
This

Code: 
Range(Chr(a + 64) & 1).Value
Translates to the value of B2 on the spreadsheet.

The problem with using the ASCII cell reference like this (And is why I switched to R1C1) is that when you have data past column Z you will have issues.
Click to expand...

Ah ok I see it now.

How about where Ravi coded
Code: 
x = Sheets("info").Cells(1, Columns.Count).End(xlToLeft).Column
?

I found out that Dim a/x as Long is declaring the variables as Long.
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Question Question
Excel VBA, Loop, Copy, Loop, Paste, Autofilter, return value, Loop.
Replies
0
Views
261
user29383902
U
B
  • Solved
Better Way to Copy Rows Out of a Loop?
2
Replies
11
Views
231
dmt32
D
G
  • Solved
Add total row with a loop
Replies
4
Views
401
gberg
G
E
  • Solved
How to Loop through code
Replies
3
Views
306
dreid1011
dreid1011
SlinkRN
  • Solved
Handling Multiple Errors within one loop
Replies
7
Views
299
SlinkRN
SlinkRN

Forum statistics

Threads
1,225,477
Messages
6,185,219
Members
453,283
Latest member
Shortm88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top