Shot Points Extrapolation

[ridhigg.1007]

Hi all, I have been trying to write a macro in which i can extrapolate 642 lines shot points in one go rather than going one by one for each Line. Also, trying to write a macro which after extrapolation helps me in saving each file separately as a .txt file. I had posted this earlier as well but didn't get any answer.Please help if you can.

[shg]

Welcome to the forum.

"Shot points"?

[6StringJazzer]

This is your first post to this forum. Where did you post it earlier without an answer?

You should attach a file and also provide a little more background so we can see what you're talking about. Your explanation is pretty sketchy.

[ridhigg.1007]

[email protected]
hi i can send the file as an attachment only and its not letting me do it in the forum can you please mail me ur mail id so that i can explain the issue in detail.

[ridhigg.1007]

Hi please find the attached file and see coloumn b and coloumn c values I have to build a 1:2 relationship between the two columns taking care that the number in column should only repeat twice .Please see the attached file and help.i have 800 lines like this who is first 90 or 100 values in column b have to be assigned a value.Till now i have done it manually be applying a formula like taking the value after last -1 -0.5 if the 97th & 98th value are repeating twice, if repeating thrice i have used value -0.33 and so on.After this i have to save each excel sheet separately as a atxt file as well.Please help if you can design a macro a VBAtest (2).txt

[ridhigg.1007]

please open the attached file in word pad and see till 95 s.no the values are -1 in column b and from 96 its 35 and for 97 & 98 its 36 and 36 so i have to make the relation as such that the 95 value should also be 35 which would be 35-0.5=34.5.

[MrShorty]

I am not sure that I understand exactly what result you want. Here's how I tried it: (If you need help for any of the functions mentioned, help files are here: https://support.office.com/en-us/art...90033e188#bm20

1) Open/copy/import text file into Excel and get data separated into columns.
2) MATCH() function to locate the last entry in column 2 that is below 0. =MATCH(0,$B$5:$B$1000,1) Note the 1 in the third argument so that it finds the row between the last -1 and the first 35.
3) COUNT() function to determine how many rows there are in the entire data set. =COUNT($B$5:$B$1000) These two functions will go into OFFSET() functions to get ranges for regressions in a TREND() function.
4) TREND() function for each point to compute the extrapolation (probably best put in E5 or other column in row 5) =TREND(OFFSET($A$5,result of 2,1,result of 3-result of 2,1),OFFSET($A$5,result of 2,0,result of 3-result of 2,1),A5)
5) ROUND() function to force results to integer: =ROUND(TREND(...),0)
6) Copy ROUND(TREND()) function down.
7) Check that the results are correct.

Pull the needed information into a separate tab for creating the text file. Then create the text file by ->office button->save as->filetype as text->give file a name.
Probably the easiest way to see how to create a macro for saving as text file is to record a macro while you save the file as text.

If I understand correctly, that approach seems to work for the sample data provided. You should probably test the algorithm against several different data sets until you are confident that it will work for all situations you expect to encounter.

[ridhigg.1007]

Hi Thanks for the reply I ma trying to use the above solution.Now as stated i also requested A vba which converts my 642 excel sheets into each txt file sperately. I have written a macro but this i have to run 642 times since each sheet is separately saved.I have all the excel sheets in one folder.,Please find the macro below:

Please Login or Register  to view this content.

and provide a macro which can i can run on the folder having 642 excel sheets and save each of them separately as txt file.

[MrShorty]

This forum can be strict about its rules, like putting code in code tags. You will want to edit your post to comply to avoid getting your topic locked.

This isn't the kind of VBA programming I do. What you have looks about right to me (a For Each..Next loop to go through each sheet and save it as a text file). Do you have a specific question about the code? Is it doing something it shouldn't? Not doing something it should? Giving you an error? Can you step through the code (debugging strategies: http://www.cpearson.com/Excel/DebuggingVBA.aspx )?

[ridhigg.1007]

Thanks for sharing and advising me the right thing MrShorty but having said that in my previous post one my question had addressed to the saving each excel sheet separately as a txt file all placed in a folder. Since the number of sheets is too high so designed a code which is working individually for each worksheet that is i have paste and run the code for each new worksheet. So thought of sharing the code with you such that can get a solution to it.

[6StringJazzer]

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

Your post does not comply with Rule 3 of our Forum Rules. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here