VB Loop Help

[philip260]

Hello All.

I have written a module which runs an access query and then stores
the result of the query in variables and at the end of the module I
assign those variables to a particular cell in excel. The variables
are set in a Do Until loop on an access query. What i want to be able
to do is minimize the code that i have to use and put in place a
better loop code to eliminiate the programming text redundancy. I want
the variables to be set to query results with certain parameters. I
know theres a better way to do what im doing but im new to writing vb.
Please see code below.

Do Until rst.EOF

If rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like "APPWCASH"
And rst.Fields(2) Like "5:00:00 AM*" Then
nbmoneyappwcash5am = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"APPWCASH" And rst.Fields(2) Like "6:00:00 AM*" Then
nbmoneyappwcash6am = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"APPWCASH" And rst.Fields(2) Like "7:00:00 AM*" Then
nbmoneyappwcash7am = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"APPWCASH" And rst.Fields(2) Like "8:00:00 AM*" Then
nbmoneyappwcash8am = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"APPWCASH" And rst.Fields(2) Like "9:00:00 AM*" Then
nbmoneyappwcash9am = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"APPWCASH" And rst.Fields(2) Like "10:00:00 AM*" Then
nbmoneyappwcash10am = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"APPWCASH" And rst.Fields(2) Like "11:00:00 AM*" Then
nbmoneyappwcash11am = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"APPWCASH" And rst.Fields(2) Like "12:00:00 PM*" Then
nbmoneyappwcash12pm = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"APPWCASH" And rst.Fields(2) Like "1:00:00 PM*" Then
nbmoneyappwcash1pm = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"APPWCASH" And rst.Fields(2) Like "2:00:00 PM*" Then
nbmoneyappwcash2pm = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"APPWCASH" And rst.Fields(2) Like "3:00:00 PM*" Then
nbmoneyappwcash3pm = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"APPWCASH" And rst.Fields(2) Like "4:00:00 PM*" Then
nbmoneyappwcash4pm = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"APPWCASH" And rst.Fields(2) Like "5:00:00 PM*" Then
nbmoneyappwcash5pm = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"APPWCASH" And rst.Fields(2) Like "6:00:00 PM*" Then
nbmoneyappwcash6pm = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"APPWCASH" And rst.Fields(2) Like "7:00:00 PM*" Then
nbmoneyappwcash7pm = rst.Fields(3)

ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"MONEYIN" And rst.Fields(2) Like "5:00:00 AM*" Then
nbmoneymoneyin5am = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"MONEYIN" And rst.Fields(2) Like "6:00:00 AM*" Then
nbmoneymoneyin6am = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"MONEYIN" And rst.Fields(2) Like "7:00:00 AM*" Then
nbmoneymoneyin7am = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"MONEYIN" And rst.Fields(2) Like "8:00:00 AM*" Then
nbmoneymoneyin8am = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"MONEYIN" And rst.Fields(2) Like "9:00:00 AM*" Then
nbmoneymoneyin9am = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"MONEYIN" And rst.Fields(2) Like "10:00:00 AM*" Then
nbmoneymoneyin10am = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"MONEYIN" And rst.Fields(2) Like "11:00:00 AM*" Then
nbmoneymoneyin11am = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"MONEYIN" And rst.Fields(2) Like "12:00:00 PM*" Then
nbmoneymoneyin12pm = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"MONEYIN" And rst.Fields(2) Like "1:00:00 PM*" Then
nbmoneymoneyin1pm = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"MONEYIN" And rst.Fields(2) Like "2:00:00 PM*" Then
nbmoneymoneyin2pm = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"MONEYIN" And rst.Fields(2) Like "3:00:00 PM*" Then
nbmoneymoneyin3pm = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"MONEYIN" And rst.Fields(2) Like "4:00:00 PM*" Then
nbmoneymoneyin4pm = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"MONEYIN" And rst.Fields(2) Like "5:00:00 PM*" Then
nbmoneymoneyin5pm = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"MONEYIN" And rst.Fields(2) Like "6:00:00 PM*" Then
nbmoneymoneyin6pm = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"MONEYIN" And rst.Fields(2) Like "7:00:00 PM*" Then
nbmoneymoneyin7pm = rst.Fields(3)

Any help would be appreciated!
Thanks in advance
Phil

 

[Stefan Hoffmann]

hi Phil,

What i want to be able
to do is minimize the code that i have to use and put in place a
better loop code to eliminiate the programming text redundancy.

Filter in the query with an appropriate condition, e.g.:

SELECT *
FROM [YourTable]
WHERE [field0] LIKE "NBMONEY*"
AND [field1] LIKE "APPWCASH"

You may also use a second query:

SELECT *
FROM [YourTable]
WHERE [field0] LIKE "NBMONEY*"
AND [field1] LIKE "MONYIN"

Do Until rst.EOF
If rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like "APPWCASH"
And rst.Fields(2) Like "5:00:00 AM*" Then
nbmoneyappwcash5am = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like
"MONEYIN" And rst.Fields(2) Like "5:00:00 AM*" Then
nbmoneymoneyin5am = rst.Fields(3)
ElseIf rst.Fields(0) Like "NBMONEY*" And rst.Fields(1) Like

Have you a special layout in your Excel sheet? Are you using Excel
automation? Then the use of .CopyFromRecordset may work for you.


mfG
--> stefan <--

 

[philip260]

Hello Stefan. Thanks for your reply.

I fill excel by directing the variable to the exact cell like below:

With xlsheet.cells
.range("B7") = nbmoneyappwcash5am
.range("C7") = nbmoneyappwcash6am
.range("D7") = nbmoneyappwcash7am
.range("E7") = nbmoneyappwcash8am
.range("F7") = nbmoneyappwcash9am
.range("G7") = nbmoneyappwcash10am
.range("H7") = nbmoneyappwcash11am
.range("I7") = nbmoneyappwcash12pm
.range("J7") = nbmoneyappwcash1pm
.range("K7") = nbmoneyappwcash2pm
.range("L7") = nbmoneyappwcash3pm
.range("M7") = nbmoneyappwcash4pm
.range("N7") = nbmoneyappwcash5pm
.range("O7") = nbmoneyappwcash6pm
.range("P7") = nbmoneyappwcash7pm

I also need to account for the times in the loop.
I have a query with multiple field(0) and field(1) values. nbmoney and
appwcash are just 2 of them.. so you can see how much typing i would
have to do. I dont mind since i can just copy and paste the code and
then do a replace filter however i know theres a better way than
typing all this code out.

Thanks!
Phil

 

[Stefan Hoffmann]

hi Phil,

With xlsheet.cells
.range("B7") = nbmoneyappwcash5am

Take a look at

Function CopyFromRecordset(Data As Unknown, [MaxRows], [MaxColumns])
As Long
Element from Excel.Range


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Phil,

With xlsheet.cells
.range("B7") = nbmoneyappwcash5am

Take a look at
Function CopyFromRecordset(Data As Unknown, [MaxRows], [MaxColumns])
As Long
Element from Excel.Range

Forgot to mention:

This method is only usable for records with a total byte size < 1968
bytes (it's an Excel limitation).


mfG
--> stefan <--