VBA Automation of Data Extraction and Calculation

[rschappe]

What I want to do is a bit complicated and I was hoping there was some scripts or macros that I could start with and simply modify to fit my needs since I am too novice to start entirely from scratch.


The project involves run-time data from wells. We are using estimated flow rates multiplied by the recorded run-times to estimate water use.


STEP 1:

Every month we download data from automatic recorders. The raw data is an excel workbook with 3 columns (ID, Date/Time, On/Off) The recorder records the time when the pump kicks on and off. What I need to create is macro or a script that will look at this raw data, prompt the user for a specified date range, then copy that data into a New Sheet in a different workbook.


Step 2:

Once Monthly Data is in its own sheet, I need to create 3 new columns. The first new column is conditional calculation based on whether the record shows ON or OFF in col3 (effectively calculating the run time for each time the pump turns on and off). This new value is then multiplied by a value (the flow rate estimate) from a different table.


Step 3:

I need to have a summery table that Sums the total run-time for the month, the total water use for the month and standard error based on our range of predicted flow-rates.


Step 4:

Once all the summary calculations are complete for each site, I need the results copied into a summary table in a separate workbook.


I know this is alot. I have sample data I can send out if requested.


If I need to explain anything better let me know.


Thanks in advance

 

[SirJB7]

Hi, rschappe!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


I recommend you to considera uploading not only a file with sample data but a sample file in fact (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.


Include there all the data, tables and explanations needed to perform the described calculations.


Regards!

 

[rschappe]

Thanks and sorry for not doing my due diligence prior to posting!


Sample worksheet can be found here:

https://dl.dropboxusercontent.com/u/23593654/sample_DSS.xlsx


I have found some pieces of code that look to be useful for Step 1, but I am getting an error on the autofilter lines.


I need three user defined parameters:

Start Date (strStartDate)

End Date (strEndDate)

Title for New Sheet (strTitle)


'

Sub movemonthlydata()


Application.ScreenUpdating = False

strStartDate = InputBox("Please enter start date:")

strEndDate = InputBox("Please enter end date:")

strTitle = InputBox("Please enter the Month and Year:")

Sheets.Add.Name = strTitle

With Worksheets("Clark_Raw")

Range("$A$1:$C$10000").AutoFilter Field:=2, Criteria1:=">strStartDate"

Range("$A$1:$C$10000").AutoFilter Field:=2, Criteria1:="<strEndDate"

Range("A:A").SpecialCells(xlCellTypeVisible).Copy_Worksheets(strTitle).Range ("A1")

Range("B:B").SpecialCells(xlCellTypeVisible).Copy_Worksheets(strTitle).Range ("B1")

Range("C:C").SpecialCells(xlCellTypeVisible).Copy_Worksheets(strTitle).Range ("C1")

Range("a1").AutoFilter

End With

Application.ScreenUpdating = True


End Sub

'


It creates and names the new sheet appropriately but doesn't get any of the data.


Any idea whats going wrong?


Thanks

 

[SirJB7]

Hi, rschappe!


Thank you for uploading your sample data file, but as I asked before it'd be helpful "a sample file in fact (including manual examples of desired output)", that's to say, what do you want to get and how should that goal be achieved (calculations, ...).


Regarding your posted code, there are a few things to be changed:

-----

[pre]

Sub movemonthlydata()

Dim strStartDate As Date, strEndDate As Date, strTitle As String

Application.ScreenUpdating = False
strStartDate = InputBox("Please enter start date:")
strEndDate = InputBox("Please enter end date:")
strTitle = InputBox("Please enter the Month and Year:")
Sheets.Add.Name = strTitle

With Worksheets("Clark_Raw")
.Range("$A$1:$C$10000").AutoFilter Field:=2, Criteria1:=">" & strStartDate, _
Operator:=xlAnd, Criteria2:="<" & strEndDate
.Range("A:A").SpecialCells(xlCellTypeVisible).Copy Worksheets(strTitle).Range("A1")
.Range("B:B").SpecialCells(xlCellTypeVisible).Copy Worksheets(strTitle).Range("B1")
.Range("C:C").SpecialCells(xlCellTypeVisible).Copy Worksheets(strTitle).Range("C1")
.Range("a1").AutoFilter
End With

Application.ScreenUpdating = True

End Sub

[/pre]
-----


Wrong things:

a) Didn't declare variables (parameters) so the 2 first should be entered carefully

b) Missing qualification (".") for ranges within With...EndWith loop

c) Error in copy syntax ("_" by space)

d) Filter settings twice for same criteria 1 and not once for criteria 1 and another for criteria 2

e) Missing criteria link operator (and/or, in this case and)


Regards!


PS: BTW, in your 1st post you asked for data being copied to another workbook and in the provided code it's being copied into a new sheet of the same workbook. Just fyi.