Previous Thread
Next Thread
Print Thread
Rate Thread
Page 1 of 3 1 2 3
Excel VBA Question
#342543 03/18/11 09:55 PM
Joined: Apr 2007
Posts: 4,877
connoisseur
OP Offline
connoisseur
Joined: Apr 2007
Posts: 4,877
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
Re: Excel VBA Question
terzaghi #342550 03/18/11 11:04 PM
Joined: Oct 2006
Posts: 484
devotee
Offline
devotee
Joined: Oct 2006
Posts: 484
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?

Re: Excel VBA Question
Kruncher #342553 03/19/11 12:04 AM
Joined: Apr 2007
Posts: 4,877
connoisseur
OP Offline
connoisseur
Joined: Apr 2007
Posts: 4,877
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
Re: Excel VBA Question
terzaghi #342566 03/19/11 02:00 AM
Joined: Oct 2006
Posts: 484
devotee
Offline
devotee
Joined: Oct 2006
Posts: 484
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.

Re: Excel VBA Question
Kruncher #342575 03/19/11 03:18 AM
Joined: Jan 2004
Posts: 13,840
Likes: 13
shareholder in the making
Offline
shareholder in the making
Joined: Jan 2004
Posts: 13,840
Likes: 13
what are you guys smoking, lol smile


M80s VP180 4xM22ow 4xM3ic EP600 2xEP350
AnthemAVM60 Outlaw7700 EmoA500 Epson5040UB FluanceRT85


Re: Excel VBA Question
SirQuack #342628 03/20/11 02:09 AM
Joined: Apr 2003
Posts: 16,441
shareholder in the making
Offline
shareholder in the making
Joined: Apr 2003
Posts: 16,441
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

Re: Excel VBA Question
pmbuko #342646 03/20/11 02:10 PM
Joined: Jan 2004
Posts: 13,840
Likes: 13
shareholder in the making
Offline
shareholder in the making
Joined: Jan 2004
Posts: 13,840
Likes: 13
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
AnthemAVM60 Outlaw7700 EmoA500 Epson5040UB FluanceRT85


Re: Excel VBA Question
SirQuack #342647 03/20/11 02:13 PM
Joined: Sep 2004
Posts: 11,458
shareholder in the making
Offline
shareholder in the making
Joined: Sep 2004
Posts: 11,458
I don' no nuthin'! smile


::::::: No disrespect to Axiom, but my favorite woofer is my yellow lab :::::::
Re: Excel VBA Question
MarkSJohnson #342649 03/20/11 02:36 PM
Joined: Aug 2009
Posts: 6,015
axiomite
Offline
axiomite
Joined: Aug 2009
Posts: 6,015
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.

Last edited by CatBrat; 03/20/11 02:40 PM.
Re: Excel VBA Question
CatBrat #342659 03/20/11 05:00 PM
Joined: Apr 2007
Posts: 4,877
connoisseur
OP Offline
connoisseur
Joined: Apr 2007
Posts: 4,877
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.

Last edited by terzaghi; 03/20/11 05:00 PM.

-David
Page 1 of 3 1 2 3

Moderated by  alan, Amie, Andrew, axiomadmin, Brent, Debbie, Ian, Jc 

Link Copied to Clipboard

Need Help Graphic

Forum Statistics
Forums16
Topics24,940
Posts442,457
Members15,616
Most Online2,082
Jan 22nd, 2020
Top Posters
Ken.C 18,044
pmbuko 16,441
SirQuack 13,840
CV 12,077
MarkSJohnson 11,458
Who's Online Now
0 members (), 558 guests, and 3 robots.
Key: Admin, Global Mod, Mod
Newsletter Signup
Powered by UBB.threads™ PHP Forum Software 7.7.4