Adding Items into Dictionary and Recalling them In Sheet

[lougs7]

Hi all,

I have to add all the values from a Sheet that acts as a Database to a Dictionary, and I am trying to spit out these Values where appropriate in another Worksheet. For illustrative purposes I created a sample Workbook with, Sheet("Database_ST") and Sheet("Output"), but in reality they are actually two entirely different Workbooks on different network drives, but I combined them into one to make it easier for the Example.

1. In Sheet "database_ST" each Stock has it's own unique Identifier in Row 1, and at each Date the data changes. The Column Numbers and row numbers will change (more data , with more Stocks might be added)
example_1.PNG
2. In another Sheet "Output" (in reality it is another workbook, ill adjust code later) im trying to fill in the sheet with the values from the Database. The Columns are in no particular order, nor are the dates but the data will always start as of Row7. Also column spaces splitting the data can apear at various points.
example_2.PNG
3. For the Dates which the Database Sheet does not have a value for, or a Stock that is not listed in the Database Sheet, I would like the Code to input a formula for example sake =RANDBETWEEN(0,10) (it is a custom function formula ill add later)
I am aware you can do this with a an If statement + Index Match formulas and not through code, however in reality their is VBA codes that run before this and after, creating new tables, clearing the data, and it is better to have it in VBA than manually putitng formulas each time

I am trying to use the Dictionary method because once everything is added to the Output sheet (values from database or if no values, the formula's value) I will then add all the new items to the dictionary and update the Database sheet. First I am trying to figure out the Output, if anyone has any ideas I would really appreciate your help.

EDIT: I forgot to add attachment it is there now

[Norie]

What exactly do you want to store in the dictionary?

What would be used for the key(s) of the dictionary and what would the item(s) be?

[lougs7]

Hi Norie, I'm not sure if Dictionary is the best way to go, perhaps something else, but I can explain why I was thinking that. Essentially each value in the matrix corresponds to 1 unique identifier at top and 1 date. For example, StockA_ST & "_" & 18-01-2021 would hold StockA's jan 18th value, StockB_ST & "_" & 18-01-2021, would hold StockB's value for Jan 18th, or StockB_ST & "_" & 17-01-2021 would contain StockB's Jan 17th value.

1. Essentially the dictionary would have all these values stored for each Stock & Date combination.
2. I was planning to Paste into Output sheet from Dictionary, in its respective Column and Row. If there is no data for that Row / column combo then to input a formula. The formula would load a value for me.
3. I then want to look at all the Identifier & "_"& Date values in this Output sheet checking if it exists in Dictionary, if not I add it [new Formula values essentially would be the ones missing that get added]
4. Lastly would be to update my database sheet with these new values from the dictionary, basically clearing entire database and pasting Dictionary (still figuring out how I would do that)

I am open to do this any other way if you see a better way, im only amature/moderate at VBA I am just learning. I appreciate your help

EDIT: I forgot to add attachment it is there now

[jindon]

See if this how you wanted.

Please Login or Register  to view this content.

[lougs7]

This is exactly what I was trying to do thank you so much !! this was my first big challenge, im going to review this logic for next time so I cna do it myself, I appreciate it.

I had one follow up however. How can I add a piece of code that will then go into the Output Sheet and for those Cells that correspond to a Date & Stock with a blank (meaning it wasn't in our initial dictionary) to put a generic formula (=RANDBETWEEN(0,10) or example) , I am actually going to change this for a custom formula that connects to an Add-In. Would you know how to do that?

[jindon]

Try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[lougs7]

thank you! doing a direct copy paste, replacing old code with new gives me an error "Subscript out of range" at this line
txt = Join(Array(a(i, 1), a(1, ii)), Chr(2))

Im trying to see why...

[jindon]

The code should look like this.

Please Login or Register  to view this content.

[lougs7]

Wow nice thanks for your reply here. I think I explained it wrong however, i am trying to get the formula to show up here
Attachment 714104

currently with the edit it goes in the columns not associated to any stock, as shown below
Attachment 714105

ill try playing arround with the code see if I can get it, although in case you know please let me know I really appreciate it im not so good with this specific dictionary language

[lougs7]

with this edit I get the following, this is actually okay for me to have formulas in betwene the columns, thanks for your help Thread resolved

Please Login or Register  to view this content.

Attachment 714112

[lougs7]

Hi jindon, sorry for this last reply.

Below is the real code that I adapted for my spreadsheets. It current works and fetches the data from the database file and inputs it in the Monitor. However as soon as I uncomment
'If a(i, ii) = "" Then
'a(i, ii) = "=IF(OR(B$1="",$A7=""),"",GetVol($A7,B$2:B$5,EDATE($A7,Summary!$B$3),GetSpot(B$2:B$5,$A7),2))"
'End If
then the code breaks at .Value = a

Please Login or Register  to view this content.

Do you know how I can input that formula in every blank cell in the region without causing a break?

[jindon]

I didn't read your code in details, however

Please Login or Register  to view this content.

Double quotes used within formula needed to be doubled.

Please Login or Register  to view this content.

[lougs7]

Thank you so much it works flawlessly now I can't believe I missed that! You Rock appreciate the help.