Need help with Excel VBA

[Pyro77]

Hey friends,

I'm practically new to VBA and I've been asked to do some coding in it.

I've attached a zip file containing 2 excel files (link below). The file has the script in it. The first one called gendata is the one we're looking at. The other file, is where we pull the data from.

Ok, so forget about how to pull the data. What I'm having trouble is pulling the data from different files. I managed to do a crude version of it. As you can see, it will take in the parameters from the cells in the report_src file and it will do the calculations and create new worksheets in that file while pulling data from etc. There will be others of course, for example.

So what I'm having problems with here is what if I need to do all 12 months and maybe 2 months only some other time? I made it for 5 months in that version. I want it to be dynamic, knowing when to stop pulling when there are no parameters to take in. So I'm guessing so sort of loop? You can duplicate the data file to may, july, aug etc. They need to be opened for it to pull the data. Just rename it to the first 3 letters of the month.

Please ask me if you need any clarifications.

Again, I'm totally new to vb script, so I'm hoping someone could help.


thanks :)

[Pyro77]

Ok, I managed to do a while loop that will see if the next column is empty, it will stop. Now the problem is how do I change the B column values to C and so on once it finishes looping it once?

[Solkarnar]

Not really sure wat u are trying to do, but mrexcel.com is a good resource i used to use.

[Solkarnar]

After reading thru a few times, it seems that u might have up to 12 files from which u wanna pull data from, but you dunno which one u will need to use (ie could be Jan, or Feb-Mar-Apr, or Feb-Jun-Aug), am i correct?
Further more all these source sheets are in different excel files physically, so u need to open everyone of them via VBA and do editing/copying of the source data to generate some report.

Assuming im right about my assumptions, i would do it differently.

Here could be my method:
1) Since (or assuming MOS_Demand_Jun file) data doesnt change cuz its the data for the month of June, i would do a manual copying of the required sheet to my current report excel file. The reason is crosslinking of excel data across files is unwieldy (Tons of erros will occur if the source file gets moved or name gets changed). I prefer to do intra file linking via sheets.

2) Once u have all the different sheets (from all the different months) in your excel report file, you can start naming the sheets "Jan" "Feb" "Mar" etc).

3) Next create a new sheet named "Control". This is the sheet where all the conditions are located.

For example, if i wanna do for Mar-May-Jul, i do put as

| Jan | 0 |
| Feb | 0 |
| Mar | 1 |
| Apr | 0 |
| May | 1 |
| Jun | 0 |
| Jul | 1 |

Then use VBA to do a loop down the cells to see which month needs calculating, then call the sheet name from within your report file to manipulate.

Of course, if u dun wan ppl to see all your working sheets, hide them before presentation.

Hope it helps. :o

[Pyro77]

Thanks Solkarnar. However, I want to do it in another way that, although it's messier, but it's what my friend asked for.

This loop will stop once the column next to it is found to be empty.
However, once I run it, it generates the june report and then it gives me an error saying I cannot have a sheet of the same name. I expected this error as the loop is running again and taking in the parameters for the month of june. How would I change the .Range value (("B1"), ("B2")) etc, to C1, C2 etc?

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

Any ideas?

Thanks.

[WaterRain]

how about.. break; ?

[Pyro77]

Never mind, I managed to solve the error

Thanks all :D

[God_hands]

Thanks Solkarnar. However, I want to do it in another way that, although it's messier, but it's what my friend asked for.

This loop will stop once the column next to it is found to be empty.
However, once I run it, it generates the june report and then it gives me an error saying I cannot have a sheet of the same name. I expected this error as the loop is running again and taking in the parameters for the month of june. How would I change the .Range value (("B1"), ("B2")) etc, to C1, C2 etc?

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

Any ideas?

Thanks.

can you show me your codes? , i am doing the same things as you but i am using excel 2003 and vba 2003

[nathanielteo]

hey guys im stuck at a school poly project testing on VBA coding in Excel.

i'm willing to find someone to do it for me at a token fee of 150 to 200. its honestly not too hard for an experienced programmer. if you are interested, PM me. :)