MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Help with "Nested Do While Loop"

  Asked By: Binge    Date: Oct 24    Category: MS Office    Views: 1884

I have very limited programming
experience but more than the rest of the group – so I got this task.
The following macro works fine on one ticker and one date.

Sometimes we have several tickers and several dates. I'm trying to do
a script that will take the first ticker (A6) and use that ticker to
run every date from B6 to B?. Then take the next ticker B7 and run
every date from B6 to B?.

I have looked at many of the nested loop examples and I think that is
what I need. I have attempted to copy some of the examples posted in
the old messages but can't get anything I have tried so far to work.

I would appreciate if someone could look at what I have done so far
and tell me where I'm going wrong.

It looks to me like all the examples started with "Option Explicit
Private Sub". Can I still use a Keyboard Shortcut Key like Crtl v?

Thanks K

' All_Tickers Macro
' Macro recorded
' Keyboard Shortcut: Ctrl v
Dim ticker As String
Dim bdate As String

ticker = Worksheets("Sheet1").Range("A6").Value
bdate = Worksheets("Sheet1").Range("B6").Value

Workbooks.Open Filename:="C:\MyDocuments\Stops\" & ticker & "/" &
bdate & ".csv"

' My Statements

ActiveWorkbook.SaveAs Filename:="C:\MyDocuments\Stops\" & ticker &
"\" & bdate & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

`My attempt at a nested Loop!

' Option Explicit Private Sub

Dim ticker As String
Dim bdate As String

ticker = Worksheets("Sheet1").Range("A6").Value
bdate = Worksheets("Sheet1").Range("B6").Value

Do While ticker <> ""

Do While bdate <> ""

` My Statements

bdate = bdate + Offset(1, 0


bdate = Worksheets("Sheet1").Range("B6").Value

loop. ticker = ticker + Offset(1, 0)




3 Answers Found

Answer #1    Answered By: Clay Cook     Answered On: Oct 24

OK, Random thought-wise..
Option Explicit: In effect, this says that you're selecting the "option" that
all variables must be "explicit"ly dimensioned.
This is a valuable option, since, without it, VBA will "automatically" dimension
a variable the first time it is used.
That means that if you say:
cnt = 10
than later say:
cnt = cont + 1 but MEANT cnt = cnt + 1
cnt is NOT 11, but 1. Because you haven't defined "cont", VBA defined it for
you and initialized it to 0. If you use option  Explicit, the compiler will give
you an error and tell you that "cont" is not defined.

Private Sub just means that the subroutine is not available outside of this
workbook. This is useful if you tend to use the same subroutine names in
different files, and you want to make sure you're running the right sub.

Neither of these will "preclude" you from using a keyboard  shortcut.

Next, I noticed that in the .Open function, you're using:
Stops\" & ticker & "/" & bdate & ".csv"
This POTENITALLY could cause a problem with the "forward" slash ("/") instead of
the "backward" slash ("\")

Now, in the "B" column, will the dates  have any "blank" values?
That is, if you were to count the non-blank cells between B6 and B65000,
and, say, there is 10 dates, can you count on these 10 to be in rows 6 through
15 with no blank cells?

If so, you can count the dates using a worksheet function:

datecnt = Application.worksheetfunction.counta(range("B6:B" & 65000)

Then use:
set DateRange = Sheets("Sheet1").Range("B6:B" & datecnt + 6)
for each Data in daterange.columns(1).cells
bdate = data.value
' Use your code here
next Data

Now, using these tools, try your hand at creating a "tickercnt"
and set TickRange = "range of tickers"

Answer #2    Answered By: Josephine Gomez     Answered On: Oct 24

Appreciate the explanation on 'Option Explicit' and I have corrected
the "/" error. I usually know what I want but many times have a hard
time explaining to someone else what I want!
I want the program to use the ticker in A6 and the date  in B6 and run
my code. Then use A6 with B7. Then A6 with B8. This will continue
until there is a blank cell in column B.
Then use A7 with all available B's. Then A8 with all available B's.
And so on until there is a blank cell in column A.

I tried to do just one loop. I was trying to start using cell A6 & B6
and work  my way down column A until I reached a blank cell.
For my 'counter' I tried to use;
ticker = Worksheets(" Sheet1"). Range("A6" ).Value
Do While ticker <> ""
ticker = ticker + Offset(1, 0)
I get an error message with the Offset highlighted in Blue. The 'Help'
tell me I have a Sub, Function, or Property not defined. Could you
tell me where I'm going wrong  here?

Answer #3    Answered By: Aadi Martin     Answered On: Oct 24

Did you look at the code I sent?
I think it does what you're asking.
What I described finds non-blank values in B
and combines them with the value of A6.
If you understand THAT loop, you can write another loop
with the same technique to loop  through the values of "A".

The problem with what you wrote with ticker = ticker + offset(1,0)
is that it first, it is trying to COMBINE the tickers. (if all other problems
where fixed) That means that if the value in A6 was, say: "paul"
and the value in A7 was "Steve", and the value in A8 was "John",
then the first loop would have Paul"date1", Paul"date2", but the
second loop would be PaulSteve"date1", PaulSteve"date2", the third
loop would be PaulSteveJohn"date1", PaulSteveJohn"date2"... so, you see
that's not gonna work. Also, "offset" is an offset from the currently SELECTED
cell. you're not actually SELECTING the first cell, you're letting the program
look at the contents of the first cell.

Take a look at my previous response.
If you give me some sample data (5 values or so) and sample file names,
I can write it for you, but will that help  you learn??

Didn't find what you were looking for? Find more on Help with "Nested Do While Loop" Or get search suggestion and latest updates.