You are not logged in. [Log In]


Forums » General Discussion » The Water Cooler » Excel VBA Question

Page 1 of 3 1 2 3 >
Topic Options
Rate This Topic
#342543 - 03/18/11 05:55 PM Excel VBA Question
terzaghi Offline
connoisseur

Registered: 04/04/07
Posts: 4870
Loc: Tulsa, Oklahoma
I think there are a few excel gurus around here so here it goes:

I have two variables that are row numbers I have already stored.

I want to select from A Var1 to C Var2 and copy the data.

Can anyone help? The following Line of Code does not work and turns red in the VBA editor. Thanks.

Range("A" & LevFirstRowPlus1:"C" & LevLastRowMinus1).Copy
_________________________
-David

Top
#342550 - 03/18/11 07:04 PM Re: Excel VBA Question [Re: terzaghi]
Kruncher Offline
devotee

Registered: 10/05/06
Posts: 484
Loc: Maple Ridge, BC
Hi David,

Without too much to go on, I'd expect the following to work a little better for you:

Range("A" & Cstr(LevFirstRowPlus1) & ":" & "C" & Cstr(LevLastRowMinus1)).Copy

Any better?

Top
#342553 - 03/18/11 08:04 PM Re: Excel VBA Question [Re: Kruncher]
terzaghi Offline
connoisseur

Registered: 04/04/07
Posts: 4870
Loc: Tulsa, Oklahoma
Thanks Kruncher, that did the trick. I figured you would be the one to respond if any.

We have 27 piezometers installed out at one of our lakes (tube in the ground that measures water pressure). A Datalogger reads the stored data and saves it into text files.

AS a result, I have 27 text files with 6 months of data each. The data is date, pressure, and temperature obtained every hour for the past 6 months (3 columns, lots of rows).

I have 27 *.xlsx files, that I created that processes the text file data with some formulas , plots it, etc. Currently there is about 2 years of data for each piezometer, and we add to it every 6 months or so. I also have three other excel files with hourly data (lake level, tailwater level, and barometric pressure) that get fed into the "processing and plotting file" for each piezometer.

Previous method used by others for updating the "processing" files involved looking at the processing file, seeing the last date and hour, then manually copying over the temperature and pressure from the next corresponding hour up to the most current date and time available. Same thing for lake level, tailwater, and barometric pressure. WASTE of time!

I'm developing a macro that we can run every 6 months when we download the new data to speed things up.

Currently I have my code set to open the processing xlsx file, find the last row# in the date column, then save the row number and date value in that cell. Next it opens the piezometer data Text file, splits the text file into appropriate cells based on a fixed width, does a "Match" command on column A to find the row number that corresponds to the last date found in the "processing" xls file.

Next, I needed to select and copy all three columns of data starting from the next hourly row in the piezometer data text file and paste it into the first blank row in the processing file, which is where your formula does the trick.

I'll have to do the same thing for the barometer, lake level, and tailwater spreadsheets. Next step is to copy the plotting formulas into all of the new rows. Then I will save the work book close it out.

I hope to repeat all of that inside of a loop of some sort that counts from 1 to 27 opening the files for PZ-1 through PZ 27.

My plan is to store the macro in a separate workbook that will open all of the other workbooks as needed during the data copy and paste, etc.


_________________________
-David

Top
#342566 - 03/18/11 10:00 PM Re: Excel VBA Question [Re: terzaghi]
Kruncher Offline
devotee

Registered: 10/05/06
Posts: 484
Loc: Maple Ridge, BC
Now that there's a thing of beauty, to my eyes. A man who sees the benefits of a better way and who has a solid plan and a desire to accomplish what he wants.

I've used exactly the approach that you've described (with different data sources, naturally) with great success, saving hours of detailed, tedious, meticulous and stressful work over the long run.

The best advice that I can offer is to invest in your budding skills. Lock yourself away for a weekend or two and get yourself off to a good start with VBA with one of John Walkenbach's Power Programming books for your version of Excel. They're excellent resources that I used to go from "I think I can do this" to actually accomplishing fairly complex tasks within just a few weeks.

You (and your boss) will be amazed at what you can accomplish and contribute. And these days, that should give you a decided edge.

Top
#342575 - 03/18/11 11:18 PM Re: Excel VBA Question [Re: Kruncher]
SirQuack Offline
shareholder in the making

Registered: 01/29/04
Posts: 13572
Loc: Iowa
what are you guys smoking, lol smile
_________________________
M80s-VP180-4xM22ow-4xM3ic-EP600-2xEP350
Anthem AVM60 Outlaw 7700 Emotiva A500 Epson 5040UB



Top
#342628 - 03/19/11 10:09 PM Re: Excel VBA Question [Re: SirQuack]
pmbuko Offline
shareholder in the making

Registered: 04/02/03
Posts: 16437
Loc: Ben Lomond, California
Apparently not what you've been smoking, Randy. wink

Automation is a computer user's best friend. Tedious data entry / number crunching is one of the best candidates for automation. Computers were first made to take this burden away from us. Put them to work! smile
_________________________
I can explain it to you but I can't understand it for you.

Top
#342646 - 03/20/11 10:10 AM Re: Excel VBA Question [Re: pmbuko]
SirQuack Offline
shareholder in the making

Registered: 01/29/04
Posts: 13572
Loc: Iowa
naaah, development/programming is not my game. Know enough to be dangerous, but I'll stick to hardware/software support and repair.
_________________________
M80s-VP180-4xM22ow-4xM3ic-EP600-2xEP350
Anthem AVM60 Outlaw 7700 Emotiva A500 Epson 5040UB



Top
#342647 - 03/20/11 10:13 AM Re: Excel VBA Question [Re: SirQuack]
MarkSJohnson Offline
shareholder in the making

Registered: 09/27/04
Posts: 11443
Loc: Central NH
I don' no nuthin'! smile
_________________________
::::::: No disrespect to Axiom, but my favorite woofer is my yellow lab :::::::

Top
#342649 - 03/20/11 10:36 AM Re: Excel VBA Question [Re: MarkSJohnson]
CatBrat Offline
axiomite

Registered: 08/05/09
Posts: 6015
Loc: Milky Way Galaxy
VBA. Is that Visual Basic for Applications? I never heard of it, until now. The shop I work for doesn't like Mickey's Soft wares. We program in Universe flavor of Pick Basic, perl, and will be starting in java sometime soon. I develop in and maintain the legacy Pick Basic software.


Edited by CatBrat (03/20/11 10:40 AM)

Top
#342659 - 03/20/11 01:00 PM Re: Excel VBA Question [Re: CatBrat]
terzaghi Offline
connoisseur

Registered: 04/04/07
Posts: 4870
Loc: Tulsa, Oklahoma
Yes Cat.

I took a C+ class in highscool and then was required to take a Fortran class while in Engineering school. Don't remember any of the details, just the basic programming logic.

I've done a small amount of VBA stuff within excel over the past couple of year but not much.

I would definitely like to be more proficient in it though... I know some of the basic command but find myself searching for the proper syntax or function constantly. WOuld be nice to know it off the top of my head.


Edited by terzaghi (03/20/11 01:00 PM)
_________________________
-David

Top
Page 1 of 3 1 2 3 >

Moderator:  alan, Amie, Andrew, axiomadmin, Brent, Debbie, Ian, Jc 
Forum Stats

15,253 Registered Members
16 Forums
24,039 Topics
424,544 Posts

Most users ever online:
883 @ 03/04/17 05:06 PM

Top Posters
Ken.C 18044
pmbuko 16437
SirQuack 13572
CV 11677
MarkSJohnson 11443
1 registered (Rjlitho)
205 Guests and
5 Spiders online.
Key: Admin, Global Mod, Mod
Newsletter Signup