Nested Do...Loops and Date Cell Defined as Date in VBA code

[Britney]

I'm trying to write code to look at a month and name in workbook#1, find the
matching month and name in workbook#2, lookup a value in workbook#2 for that
month and name, and put that value back into workbook#1. I'm having several
problems:

(1) I have three nested Do...loops and when I run the macro, it gives me the
loop without a do error. But I have three do's and 3 loops. Why am I getting
this error???

(2) I need to define a date cell in workbook#1 as a date variable so that I
can change the date in workbook#1 and the macro will work based on that date.
I can't figure out how to do this. I bypassed it for now by just defining a
date to get the rest of the macro to run, but I need to fix this too.

Keep in mind, I've been teaching myself VBA so who knows what I'm not doing
correctly.

THANKS!!!!!


Here's the macro (note I'm only working off the variables in the main sheet
and emissions sheet which are in workbook#1 and cooling tower spreadsheet
which is workbook#2).



Sub Update_Monthly_Emissions_From_Files()
'
' Macro written 03/31/2009 by BEH
'
'Define variables
Dim a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, _
x, y, z, aa, bb, cc, dd, ee, ff, gg, hh, ii, jj, kk, ll, mm, nn, oo, pp,
qq, _
rr, ss, tt, uu, vv, ww, xx, yy, zz, aaa, bbb, ccc, ddd, eee, fff, ggg,
hhh, _
iii, jjj, kkk, lll As Integer

'main sheet variables
a = 7 'row to start looking for location
b = 1 'column for equipment type
c = 2 'column to check if cell is marked x
d = 3 'column for location
e = 4 'column for file name
g = 2 'row for start date
h = 2 'column for start and end dates
hhh = 3 'row for end date
i = 6 'column to check for frequency
aa = 5 'column to check for macro name
lll = 5 'column for sheet name

'emissions sheets variables
j = 2 'row to start looking for EPN
k = 4 'column for EPN
l = 1 'row to match current month to months in sheet
m = 10 'column for Jan
n = 1 'offset to next month
y = 7 'column for past 2 yr actual average emissions
z = 8 'column for permit limit
ff = 2 'column for equipment name used for CT emissions tracking

'tank spreadsheet variables
bb = 6 'column for tank EPN
cc = 1 'column for date
dd = 8 'column for emissions in lb/month
ee = 10 'row to start looking for EPN

'cooling tower spreadsheet variables
gg = 1 'column for cooling tower equipment name
hh = 4 'column for january date
ii = 3 'offset to january lbs column
jj = 5 'offset to next month column- need to continue until jj is 59
iii = 9 'row to start looking for cooling tower equipment name and net lbs
jjj = 6 'row for months
kkk = 7 'column for january net lbs

'heater spreadsheet variables
jj = 3 'row for start date
kk = 2 'column for start and end dates
ll = 4 'row for end date
mm = 3 'row to start looking for heater values
nn = 5 'column for CO
oo = 6 'column for NOx
pp = 7 'column for VOC
qq = 8 'column for PM10
rr = 9 'column for SO2

'PtR3 and PtR4 vents spreadsheet variables
ss = 1 'row for start date
tt = 2 'column for start and end dates
uu = 2 'row for end date
vv = 9 'column for tpm emissions
ww = 10 'row for PtR3 NOx
xx = 11 'row for PtR3 SO2
yy = 12 'row for PtR3 PM10
zz = 13 'row for PtR3 CO
aaa = 14 'row for PtR3 HCl
bbb = 15 'row for PtR3 Cl2
ccc = 28 'row for PtR4 NOx
ddd = 29 'row for PtR4 SO2
eee = 30 'row for PtR4 CO
fff = 31 'row for PtR4 HCl
ggg = 32 'row for PtR4 Cl2

'macro calculations start

'Use a formula to define the date inputs and equipment types _
on the Emissions Spreadsheet to varaibles
Dim StartDate As Date
StartDate = #1/1/2009#

MainFileName = Book1v2.Text

Do Until a = 28

'formula for cooling tower emissions
If (Worksheets("Main").Cells(a, b) = "Cooling Towers") Then

Do While j < 700
'Formula to set Equipment Name (used only for Cooling Tower emissions) _
in VOC Worksheet to a varaible
EN = Worksheets("VOC").Cells(j, ff).Text

'Intialize Spreadsheets - this will open each spreadsheet

If Worksheets("Main").Cells(a, c).Text = "x" Then
FilePath = Worksheets("Main").Cells(a, d).Text
Filename = Worksheets("Main").Cells(a, e).Text
SheetName = Worksheets("Main").Cells(a, lll).Text
Workbooks.Open (FilePath)
Worksheets(SheetName).Select

'lookup EN and StartDate in cooling tower spreadsheet
Do Until hh = 61

'match start date and equipment name _
and get tons per month (tpm) for that date/equipment
If (Worksheets(SheetName).Cells(jjj, hh) =
StartDate) Then
If (Worksheets(SheetName).Cells(iii, gg) = EN)
Then

tpm = Worksheets(SheetName).Cells(iii, kkk)
/ 2000

'activate flex workbook to input tpm
Workbooks(MainFileName).Activate

'equation to match EPN and month to input
TPM _
into the right FLEX workbook cell
Do Until m = 71

If (Worksheets("VOC").Cells(l, m) =
StartDate) Then
Worksheets("VOC").Cells(j, m) = tpm
End If

m = m + n
Loop

'if not matching start date and EN then go to next EN in
CT sheet and check again
Workbooks(Filename).Activate

hh = hh + jj
kkk = kkk + jj
iii = iii + 1
Loop

End If
End If

Workbooks(MainFileName).Activate

j = j + 1
Loop

Workbooks(Filename).Close

a = a + 1
Loop

End If

MsgBox ("Update Done")

End Sub

 

[Jim Cone]

Britney,
You actually have four Do Loops.
However the 'loop without do' message can be misleading.
You also need to begin and end any "If - End If" statements within the same loop.
You have a dangling End If after the last "loop".
Further the count for "If" and "End If" must agree.

Also, if you use Option Explicit as the first line in the module,
you will find a lot of undeclared variables in your code.

And a word about code structure... keep a Do and its associated Loop
vertically aligned (in the same column). Problems are much easier
to figure out that way.
--
Jim Cone
Portland, Oregon USA
P.S. - there is an Excel programming newsgroup.




"Britney" <[email protected]>
wrote in message
I'm trying to write code to look at a month and name in workbook#1, find the
matching month and name in workbook#2, lookup a value in workbook#2 for that
month and name, and put that value back into workbook#1. I'm having several
problems:
(1) I have three nested Do...loops and when I run the macro, it gives me the
loop without a do error. But I have three do's and 3 loops. Why am I getting
this error???
(2) I need to define a date cell in workbook#1 as a date variable so that I
can change the date in workbook#1 and the macro will work based on that date.
I can't figure out how to do this. I bypassed it for now by just defining a
date to get the rest of the macro to run, but I need to fix this too.
Keep in mind, I've been teaching myself VBA so who knows what I'm not doing
correctly.

THANKS!!!!!

Here's the macro (note I'm only working off the variables in the main sheet
and emissions sheet which are in workbook#1 and cooling tower spreadsheet
which is workbook#2).

-snip-

 

[GMorris]

I don't know if this still applies, but what I can
remember about VB is that when Dim'ing vars,
it will allow you to put a bunch on one line, but
only the ones that actually have an "as <type>"
will be Dim'ed as that type. In other words, I
know it used to (and may still) Dim the variables
to be variants unless explicitly declared
individually, as:

Dim a As Integer, b As Integer

OR

Dim a As Integer
Dim b As Integer, etc.

I'm not sure if that is still the case,
but it may help in diagnosing some
problems from time to time! I found
out the hard way a long time ago.
I always try to err on the side of caution.
If that is not the case anymore, I haven't
yet been informed so I don't really know.

 

[Don Guillett]

or
dim a, b as integer

 

[GMorris]

No, what I'm talking about here is (and
this is in Excel 2007 VBA even):

Sub TryInt()

Dim a, b As Integer

a = 1.5
b = 2.2

Debug.Print a, b

End Sub

If you run this, it will truncate the b var
to "2", but the a var will still be "1.5",
and any that you declare before it will
retain the decimal value. That is because
VB doesn't apply the type unless
(or until) you supply an "As <type>" to
that particular one.

Now, if you tried that, try this:

Sub TryInt()

Dim a As Integer
Dim b As Integer

a = 1.5
b = 2.2

Debug.Print a, b

End Sub

You'll see that VB has assigned "2" to the
a var, now that is explicitly declared as an
Integer. This can cause problems if you
don't know about it (and I didn't for a long
time, leading to much debugging!).