Adding new rows of data in sheet A depending on data in sheet B

[excelnabb]

Hi I need a formula or VB script that add rows to sheet A with data that it retrieves from a column in sheet B. When the data in the column in sheet B changes it adds a NEW row with the new data in sheet A (below the previous data) and do not replace the existing data.

[Logit]

.
What are the sheet tab names ?

What column in Sheet B are you focused on for the updated changes ?

[excelnabb]

Sheet A is called "15 good day" sheet B is called "Data" (I will make several spreadsheets, so the name will most likely change).
There are several cells in sheet B that the data should be taken from, but for convenience sake I will add all the relevant data in one column, column "I". In sheet A (or 15 good day) the data will be added from column "A" to "AS", in rows 13 and then consecutively downwards (14,15,16 and so on). The thing is that new rows of data (14,15,16 and so on) should be added automatically in sheet B, when the values in column "I" changes in sheet A.

[Logit]

.
Paste this macro into the Sheet level module :

Please Login or Register  to view this content.

Refer to attached

[excelnabb]

Thanks a lot, you are very kind! I'm not quite sure If I did it right though,as I have absolutly no experience with the sheet level module or any scripting. Could you take a look? I have some formulas on the row's on sheet "15 good day", but I will move them to another place so they don't interrupt the output data as they would probably overwrite the formulas anyway?

[Logit]

.
In the attached file, right click on the DATA tab, then VIEW CODE.

Note how the macro is inserted into the SHEET LEVEL MODULE rather than MODULE 2 where you had it previously.

However, reviewing your DATA sheet I see that nothing is being included in Column I. The macro is designed to detect changes in Column I,
then to copy the entire row for pasting in the 15 GOOD DAY sheet.

Also, the information fro DATA does not align with the columns in 15 GOOD DAY sheet. So .... we need to understand what information from the DATA
sheet will be pasted into the other sheet.

If you can provide a more detailed explanation/example which data goes where .....

[excelnabb]

okay, so I have been struggling for a while now, the case is that:

When I add a new ticker (at Cell B9 Sheet Parameters)then it automatically delete all Data on the Data sheet, and replace it with new information. So I can't have any formulas or anything on this sheet.

To overcome this I have made a new area called Processed data on the "15 good day" sheet. On Row 7 I will have all the data that I previously was suppose to collect from Coloumn I in the "data" sheet (So Row 7 is now "the new I coloum")

So when I type in a new ticker at Cell B9 Sheet Parameters, then row 7 in sheet "15 good day" is automatically updated, and the values are recorded and copied down at row 20. Inserting a new ticket (in sheet Parameters Cell B9))will update the Data sheet with new data, and the values are brought into row 7 ("15 good day" before it is recorded in row 21, and so on.

In other words, the awesome script you made for me has to be changed to adjust for the new place of the "I coloum"


A new problem I encounter when trying to make the formulas that should be in row 7 on the "15 good day", is that the stock are not all recorded equally, which means prices and times are missing on some stocks. (try typing in IBM and press "get data from google", , then try ASTC as ticker, and you will see in the data sheet what I mean)

What I first tried then was to make an index and match search =INDEX(Data!B8:Data!B1000;MATCH("a1513175400";Data!A8:Data!A1000;0)+0;1) (in this example I want the opening price of the second day). The problem is that the a1513175400 number changes as it is a Unix time stamp (http://investexcel.net/free-intraday-stock-data-excel/)

What doesn't change is the letter a in front of the time stamp, and since I will only import data for 3 days each time, I can find the first, second and third a in column A on the data sheet, to reference where I am when starting with my formulas.

What I tried to make, was a formula that combined Look up a value and return cell above/below https://www.extendoffice.com/documen...-or-above.html
=INDEX(Data!B8:Data!B25;MATCH("a";Data!A8:Data!A25;0)+0;1)

And then replacing the "a" with Vlookup Find The First, 2nd Or Nth Match Value In Excel https://www.extendoffice.com/documen...-match.html#a1

=INDEX($A$8:$A$1000;SMALL(IF("a"=$A$8:$A$1000;ROW($A$8:$A$1000)-ROW($A$8)+1);1))

I don't know how to combine those two formulas properly yet though

With this formula I hope I can build on with more formulas to find the opening price, closing price, lowest price in the first hour, highest price and so on, based on the position the three a's for the time stamps. Even though the position of the time stamps and the price and time data changes for every ticket I insert.

Hope this make sense, and thanks a lot for the help, I really appreciate it!

[Logit]

.
This macro goes in the Sheet Level Module for "15 Good Day"

Please Login or Register  to view this content.

I apologize that my skills with FORMULAS is severely lacking. Hopefully, another forum member can assist in this regard. We have
several very excellent FORMULA GURUs.

[excelnabb]

Thanks a lot Logit! You have been to great help! I think I will make a another post about the second problem then.

[Logit]

.
Great !

[excelnabb]

Hi Logit, it seems after getting some helps to insert formulas into row 7 on 15 good day, that your script won't record properly? When I insert a new ticker in cell B9 on the Parameters sheet, it only change the values in Row 7, but on row 20 and so on it won't record the changes, it just change the data that is already there. You can try inserting ASTC, IBM, AAPL and see what happens

[Logit]

.
This works here :

Please Login or Register  to view this content.

[excelnabb]

Great, thank it works, however it makes two copies for each stock now, as seen on the attached worksheet.

[Logit]

.

I was able to reproduce the result you see by double clicking in A7 or A8.

I modified the macro to this :

Please Login or Register  to view this content.

This portion of the macro:

Please Login or Register  to view this content.

.... will check if rng2 is empty. So long as it is, it makes certain the range remains empty and then exits the sub - no changes made to either rng or rng2.

[excelnabb]

hmmm... I still get the same result, though I see that the formula =Parameters!B9 in sheet 15 good day, makes the trouble, it picks up the ticker in I type in B9 so it activate your script for row7 and copies the data down before I'm able to push the button "Get data from google". Then when the parameters script runs and downloads the data, your script in row 7 runs again, and makes a duplicate. In other words, it should first run the script for row 7 except cell A (where the ticker is), then when the data is downloaded and it has retrieved the new data from Data sheet, then cell A7 should be recorded down to row 20 together with the new data. If that makes sense?

Also another thing, now when I delete the recorded data from row 20 and down, it starts to record from the last place where the data was. So I get a lot of open rows. I think the solution is to go back to your previous script. The problem with the duplicate was the =Parameters!B9 I inserted in cell A7.

[Logit]

.

Help me understand ... when you click the GET DATA FROM GOOGLE button, you want the TICKER NAME from B9 to populate in 15 good day cell A7.
Then, when the data is returned from GOOGLE, it is populated to the DATA sheet and then the formulas in row 7 15 good day populate the remainder of the row.
(So far I hope that is correct).

At this point, sheet 15 good day, B7 has a new entry (as does the remainder of row 7). So, we can use the change in B7 (not A7) as the target to copy and paste
the data to row 20 and below.

One other question: When you click GET DATA FROM GOOGLE, there is a delay for the data to be downloaded/populated to the DATA sheet ?

Is the above accurate ?

[excelnabb]

.

Help me understand ... when you click the GET DATA FROM GOOGLE button, you want the TICKER NAME from B9 to populate in 15 good day cell A7. is returned from GOOGLE, it is populated to the DATA sheet and then the formulas in row 7 15 good day populate the remainder of the row.
(So far I hope that is correct).

At this point, sheet 15 good day, B7 has a new entry (as does the remainder of row 7). So, we can use the change in B7 (not A7) as the target to copy and paste
the data to row 20 and below.

One other question: When you click GET DATA FROM GOOGLE, there is a delay for the data to be downloaded/populated to the DATA sheet ?

Is the above accurate ?

YES! everything you said there is correct. Though use C7 as the target instead, as I want to insert a =Parameters!B10 for the exchange (below the ticker.)
BUT remember when the data is copy and pasted down to row 20 and below, then it needs to copy the information in cell A7 and B7 as well. Maybe this part can be done by another script that is activated by the changes in row 20 or below?

[Logit]

.

Also another thing, now when I delete the recorded data from row 20 and down, it starts to record from the last place where the data was. So I get a lot of open rows.

You should be able to clear this nuisance by highlighting row 20 to the next row where the data is being populated. For example: if row 20 has data, then rows 21:30 don't have data ... then row 30 is the
next location where data is populated ... Highlight rows 20 to 30, then press the DELETE button on your keyboard. It will remove data that is seen plus anything you cannot see that is holding a place
in the blank rows. The next time you run the macro, it should begin populating from row 20 and down.

If the above doesn't work, to get a clean slate ... highlight rows 20 to 30, right click and click DELETE. This will totally delete those rows and give you a new group of empty rows to work with. This problem
should not occur again with the last macro change (Post #14).

The change in this macro should give you the correct copy/paste of the downloaded data once it appears on row #7 :

Please Login or Register  to view this content.

[excelnabb]

With your excel sheet it works great, but once I insert a formula in one of the rows 7 on the 15 good days the script starts to duplicate the result on row 20 and below

[Logit]

.
Must be something occurring on your end. I've manually entered a ticker name in B9 (Parameters) and also clicked
on GET DATA FROM GOOGLE. Only one line is populated on 15 good days.

?????

[excelnabb]

I have made a short video to illustrate the problem
https://www.dropbox.com/s/6lezt9f0k8...cript.mp4?dl=0
(I can't seem to upload a zip file on this forum, even though its under 9,77MB )

[excelnabb]

As I mentioned earlier, maybe this problem can be solved by having a second script only for Cell A7 and B7 that copies the value from these cells and insert them in front of the newest row from 20 and below when there is a change. If that made any sense? I have no clue how this script work, so its only a suggestion

[Logit]

.
Ok, made an edit to the GET DATA macro :

Please Login or Register  to view this content.

Also deleted the formula in A7, 15 good day sheet

See if this clears up the issue.

[excelnabb]

Now it seems to work exactly as planned! :D Great, thanks a lot! You are a scripting wizzard!

[Logit]

.
You are welcome.

( and no ... I'm still a lowly human being )

[excelnabb]

I tried to take a look at your script to make some changes, but I got stuck. Any chance you could do your magic trick on B7, C7 and D7 (that refer to B10, B14 and B13). I made a drop down list on the Parameter page as you can see, and when I chose any of the values there before I push the "get data from google" button, then it automatically inserts a row

[Logit]

.

Please Login or Register  to view this content.

Does this work as needed ?

[excelnabb]

Perfect!! Thanks a lot!

[Logit]

.
You are welcome.

[excelnabb]

I'm trying to add these formulas seen in post 31 and 32 https://www.excelforum.com/excel-for...formula-3.html
into I7, J7 and so on on the "day" sheet, but the script starts to run over and over and fill up all the cell down to row 105 before the I get this run time error and excel freeze. Do you have any clue why this is happening?
out of stack space.JPG

Row 7 from A to BI on the "day" sheet will contain different formulas in the end.

And will I get the same error after typing in stock/ticker number 105? Is the spreadsheet "full" then?

[Logit]

.
Regretfully my formula skills are not very accurate. Hopefully someone else on the FORUM will be able to assist.

[excelnabb]

I don't think it has anything to do with the formulas that are inserted, I think it could be something with the script that is activated when there are formulas in the 7 row. The formulas are array formulas that are activated by inserting them in the cell and pressing Ctrl + shift + enter, then the script starts to make duplicates from row 20 and down until excel crash

[excelnabb]

Hi

Hope you had a wonderfule christmas! I'v done some testing on the worksheet and the formulas, and it seems that there are some formulas that makes the script on the Day sheet to go in a loop or something (excel crash and goes into run time error) I have checked microsoft explanation of stack space, and I have tested by deleting a lot of formulas and then inserted the new ones that make it crash, and its the same result, so I don't think it has anything to do with the worksheet being too heavy for the computer.

I have also gone through each formula with and without the script, and found out this:

in I7

=MAX(OFFSET(Data!$C$8,MATCH(2,Helper!$A$8:$A$2000,0)-1,0,COUNTIF(Helper!$A:$A,2),1))
WORKS - but not with script activated on the Day sheet, Excel crash Run time error (loop?) with script activated.

in J7

=MOD(VLOOKUP($I$9,OFFSET(Data!$C$8,MATCH(2,Helper!$A$8:$A$2000,0)-1,0,COUNTIF(Helper!$A:$A,2),5),5,0),1)
DOESN'T WORK - Get #N/A with script not activated. Excel crash Run time error (loop?) with script activated.

in K7

=VLOOKUP($I$9,OFFSET(Data!$C$8,MATCH(2,Helper!$A$8:$A$2000,0)-1,0,COUNTIF(Helper!$A:$A,2),5),4,0)

DOESN'T WORK - Get #N/A with script not activated. Excel crash Run time error (loop?) with script activated.


in Q7

=SUM(OFFSET(Data!$F$8,MATCH(2,Helper!$A$8:$A$2000,0)-1,0,COUNTIF(Helper!$A:$A,2),1))
WORKS - but not with script activated on Day sheet, Excel crash Run time error (loop?) with script activated.


Any suggestion of what can be done with the script? As it seems there are specific formulas that trigger this error

[excelnabb]

.
Regretfully my formula skills are not very accurate. Hopefully someone else on the FORUM will be able to assist.

Happy new year!
I'm still working on this spreadsheet Now all the formulas (except one) are in place and fixed and should work normaly. However when the script is activated on the Day sheet it starts to run over and over until excel crash. I think it could have something with the Set rng C7 range, or the data you added to Module1 (I see that it says GoodDaySheet, it's only called Day sheet now, not 15 good day anymore.
Do you have time to take a look? I'm so close to be finish, but get stuck at this stupid error