Axiom Home Page Forums General Discussion The Water Cooler Excel VBA Question

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

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?

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.

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.

what are you guys smoking, lol

Apparently not what you've been smoking, Randy.

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!

naaah, development/programming is not my game. Know enough to be dangerous, but I'll stick to hardware/software support and repair.

I don' no nuthin'!

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.

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.