I am making a workbook (file is attached) that will ultimately be a tool for calculating and extracting data from a raw data set. If I could get some help on how to code VBA, so that within my well name column the data can be separated and copied only including wells with the same name. Thanks in advance. Also, I am new to VBA.
Last edited by Gabe Whisonant; 06-25-2015 at 01:32 PM.
Put this in a new module and see if it does what you want? Some values were same for all wells in the last sheet, but then I see that the Hyperbolic Curve Fitting sheet has a couple of static values.
Please Login or Register to view this content.
Please help by:
Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know
There are 10 kinds of people in this world... those who understand binary, and those who don't.
Arkadi, thank you for your reply. You are helping me out a ton! Yes, this code does what I want as far as tabulating the results in the last sheet. There seems to be a problem though, the solver should run right after each well data is copied in. Running this macro yields different extracted values than if it were done manually for each wells data. I apologize if I am not understanding this clearly, I have only recently started using VBA.
Arkadi, thank you for your reply. You are helping me out a ton! Yes, this code does what I want as far as tabulating the results in the last sheet. There seems to be a problem though, the solver should run right after each well data is copied in. Running this macro yields different extracted values than if it were done manually for each wells data. I apologize if I am not understanding this clearly, I have only recently started using VBA.
Oh, I'm not triggering other code... I didn't even look at the existing macros. If you can explain better what the sequence is when you do it manually, I can perhaps help better.
Never mind... I think I see...
Last edited by Arkadi; 06-25-2015 at 02:21 PM.
What about this:
Please Login or Register to view this content.
It is still giving me problems with the extracted data sheet. Using your code, the first well (Colorado ...) comes out perfect; all the extracted values are correct for the Colorado well. The extracted parameters for the other two wells are way off. I will explain how I do it manually.
So all the raw data is put into the first sheet. Then I select and copy the data for one well, I paste this data into the next sheet in the first set of columns. This sheet has formulas in place to format the data in the right way, so the next sheet can use the data. In the third sheet I have to run the solver macro, which fits a type curve using a regression style equation. After the solver is run the parameters are displayed in the last sheet, which identifies the well and gives the parameters determined from the solver.
Your code was good for the first well, but the parameters for the next two didn't come out right. I'm not sure where it is going wrong, but I think it is somewhere in the third sheet or the solver portion of the code. The last two wells do extract correct values for peak production month and peak production rate. Which tells me that the code is working correctly up until the third sheet with the solver.
Let me know if that helps you understand the manual process. I can send you a file for each of the three wells, so you can get a picture of what needs to happen (if you want). I am very grateful for your help on this. I would be way lost without you!
Problem in part on my end there is a refererence to a a solver file of some kind I don't have.... but yes it might be useful to have the right data for at least one other well I suppose... I suspect the problem lies with the solver bit, but I adjusted my last code provided to call the same macro as the "run solver" button.
The solver file that is referenced that you don't have should not be used you are correct in using the solver3 or the run solver button.
The other wells extracted values should look this this when done.
|BEHRMANN 33-7-15 #1 STH | 8/1/2010 | 105140 | 24.66 | 0.2699 | 104211 | 0.36012442 | 0.9815 |
| Barricade 41-6S1MH | 5/1/2013 | 648384 | 52.94 | 0.2660 | 690354 | 0.721349425 | 0.9773 |
Thanks again!
I don't know... this is strange, I even put in pauses, and activate the sheet with the solver button, the data won't come out right. And I know the code to copy it is ok, but something about triggering the solver via code seems to be the issue. The first well works, but that's probably because the data is already there to begin with, the issue is the refreshing of the data for the other wells once pasted.
Yeah... The solver is the only thing bringing us down. The peak and decline determination is spot on.
I have some constraints set on one of the solver variables, but that doesn't seem to be a problem when done manually one well at a time.
I do clear the production data from the ws2 after I run the solver and get the data from the first well, but it looks like you have that already in the code.
Ok, for anyone helping out, this was the last code I tried, time delay excessive or not needed but I was trying to see if it helped:
Please Login or Register to view this content.
Uh! I'm an idiot!
I missed the fact that there were cell references in the formulas on Extracted Data that pointed to G4 on that sheet, which in turn points to another value on another sheet.... This should do it?
Please Login or Register to view this content.
Last edited by Arkadi; 06-26-2015 at 10:20 AM.
After I run the macro, the values come out the same as with the first code. Are you still running it on the same workbook as originally uploaded? or have you changed it in some way?
The result on the last page looks like this:
|COLORADO 32-7 #13 [FC] | 5/1/2010 | 28762.856 | 7.511686048 | 1E-06 | 27139.46932 | 0.075352869 | 0.730593854 |
|BEHRMANN 33-7-15 #1 STH | 8/1/2010 | 105139.793 | 7.511686192 | 1E-06 | 27139.46932 | 0.075352869 | -0.779389552 |
|Barricade 41-6S1MH | 5/1/2013 | 648383.634 | 7.511687753 | 1E-06 | 27139.46932 | 0.075352869 | -1.833457159 |
Where when done manually for each well separately it looks like this:
|COLORADO 32-7 #13[FC] | 5/1/2010 | 28763 | 7.55 | 0.0000 | 27163 | 0.075727491 | 0.7306 |
|BEHRMANN 33-7-15 #1 STH | 8/1/2010 | 105140 | 24.66 | 0.2699 | 104211 | 0.36012442 | 0.9815 |
|Barricade 41-6S1MH | 5/1/2013 | 648384 | 52.94 | 0.2660 | 690354 | 0.721349425 | 0.9773 |
Note how the date column and the second column are correct but the 4-8 columns are not right. I copied the latest code you had me try into the original workbook. Is there a step I am missing?
Try again? I just updated the code in my post above yours... the only change is that just before "call solver3" I added ws4.activate, I guess I'd forgotten to save that change to my code (that was not the original issue).
Please try:
Please Login or Register to view this content.
- Please remember to mark threads Solved with Thread Tools link at top of page.
- Please use code tags when posting code: [code]Place your code here[/code]
- Please read Forum Rules
Izandol, what changed in your code?
I noticed on the original file that the manually extracted data for the Colorado Well was left on the last sheet. I don't know if this will help us, but when I delete the Colorado extracted data then run your latest VBA, the Colorado extracted data shows up correct. The other two wells are still incorrect. I'm not sure if the Colorado came out right just because it was already present before the run or what.
Gabe, is that after my modification of my last posted code? This works for me now: (using your originally posted workbook)
Please Login or Register to view this content.
Yes this works! Thank you so much!
Finally!!!! haha.... great
Please mark as solved if you're happy
And thanks for all the rep!
Thank you Arkadi and Izandol! I will change this to solved now. I may have to come and find you again if I run in to anything else as I am still refining this tool. It will potentially be used on more than just these three testing wells. Thanks again!
Out of curiosity, which code was your final success? Izandol? or my last post?
Both codes give me the same successful result, but I used Izandol's first because that just so happened to be the one I found first when I got back into the thread.
You guys are Excel Gods!
I did use ws3.Evaluate instead of Evaluate so formula references will be evaluated in context of ws3 worksheet instead of active sheet.Originally Posted by Arkadi
Ah Izandol, you based it on what my last post at the time? the key as far as I know was that ws4 needed to be activated for the solver function.
Thay may just be something I could have fixed by modifying "Sub Solver3" to have sheet context, but didn't want to mess with the original code.
Thanks for the reply.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks