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.

I searched Google with 'vba cheat sheet' and it came up with stuff like this. Perhaps you've already done this. I don't know if it helps, or not.

Yeah, google has been my main reference.

Success!

Have everything running now in a loop from 1-27. Updates all files in less than 4 minutes.

Probably a good 3+ days worth of effort if done manually.

Thanks again Kruncher.

Now to ask for that raise...

Glad to hear that you've accomplished what you set out to do. And damn quickly, at that. A man on a mission!

As with many skills, it'll take some time to get to that level of comfort where you can write a block of code with refering to any other resources, but it's a great feeling once you do, and you're able to say "I did that!"

VBA has its critics, and they tend to have some good points. The two things, IMO, that VBA has going for it are it's (generally) easy syntax (as compared to other programming languages), and its availablity. It's included with every copy of the best selling office productivity software, and has been for years.

I've often commented to people that you don't get a programming environment free with a good spreadsheet, you get a spreadsheet free with a good programming environment. Is it perfect? Heck no, but man, you can do a lot with it just the way it is.

Kruncher,

Excel seems to be your game.

I've got a spreadsheet that wants to be a relational db, but it can't be and I need some help.

For one section I need about 4 variables picked by the user and then do a look up based on those variables for a description, cost, etc.

Problem is we are a talking thousands of possiblities ( I can handle that part) but I need to have excel do the same lookups in 8 to 10 different sections of the workbook with the possibility of different results on the same dataset. The results also does lookups for additional options based on user input for the 8/10 sections. And the workbook would be to big recreating that data 8/10 times. Not to mention the maintenance

btw, I don't know VB, just excel

Are you willing to look at the file?

Thanks

Jeff

Hi Jeff,

I've just sent you a PM, if you hadn't noticed.

Kruncher

Ironic that i received a notification at work just today about training on VBA and Excel.

I'd like to think that it's because people are finally seeing the tangible and quantifiable economic benefits, as David has so nicely summarized for us with a real-world example, that can be acheived by those with a suitable background in not just Excel or programming, but also in the nature of the actual work at hand.

I once worked with a great programmer who didn't know the first thing about accounting, and therefore it was taking him forever to build some simple financial statements. But he knew his way around optimized code.

Many thanks, response sent

Jeeez guys, get a room.

Look away, Mark. Techie stuff happens here sometimes. I'll be sure to ask you about batch processing RAW photo formats in an upcoming post.

That is something that really impresses me about my boss. He has a very strong understanding of database design and implementation, hardware, programming, business processes and accounting. When guys from other departments come to talk to him, he seems to know more about their processes than they do.