Table? VBA?

[DaveMegee]

Happy Thanksgiving, Everyone!

I'm trying to create a table that is dependent on two (percentage) variables. To start, I have my original sheet where I currently manually input the variables. The variables are then used in a calculation sheet and are checked to see how many times the historical data matches the inputs, and returns the number of instances that this happens to a cell in the original sheet.

As there are a lot of different possibilities with two ranges of inputs, I am trying to find a way to return the minimum and maximum instances without having to check each iteration manually. I do not think solver can help, as my end value is not a formula, but rather a count.

I was thinking that a table sheet might be able to solve my dilemma, by creating a different sheet, and using the percentage ranges of x in Row 1, and the percentage changes of y down column A in that same sheet. Is there a way to create a formula in the table, or VBA in the table sheet, that will take the values from column A and and the values from row 1, and insert them, one at a time, as values into the computational sheet, and return the value from the computational sheet?

Does this make sense? I have minimal experience with VBA, but it seems like this should be possible. I am very grateful to have found this site! Thank you all for any help you can give me, or just pointing me in the right direction.

 

[CyrusTheVirus]

Welcome to the forums Dave.

Please post a sample(s) of your data showing what you're trying to do (and further explain).

 

[DaveMegee]

Hello, CyrusTheVirus,

The computer I am trying to run the VBA on is not connected to the internet. I will try to explain better what I am trying to do.

I have a sheet(table) am trying to create a table on, based on x and y inputs located in Row 1 and column A. The macro will need to take the values from the inputs and insert them onto the sheet(input) which has stationary input cells. For example, The X value from sheet(table) cell B1 will need to be inserted into sheet(input) cell A2, and the Y value from sheet(table) A2 will be inserted into sheet(input) cell A3. Then the calculations will be made and the value given in sheet(input) cell A3 will be returned to sheet(table) cell B2. This will be done numerous times through all values of sheet(table) row 1, and column A.

Does that make sense?

 

[CyrusTheVirus]

Hi Dave,

For me, it's best to see examples of your tables and see what you're trying to do. You might not need VBA... I'm not sure yet.

 

[DaveMegee]

Good Evening Cyrus!

Here is the link to the spreadsheet.
https://www.dropbox.com/s/x3kaoqryx49wfl2/uploadspreadsheet.xlsm?dl=0

If you go to the MACD Table tab you will see that I am trying to insert the value in cell A2 into sheet SA V.1 cell B21, then insert MACD Table tab cell B1 into sheet SA V.1 cell B22. This will cause a calculation to run in the MACDSwapping tab that will return a number to SA V.1, cell b23, that I would like to have returned to sheet MACD Table cell b22. Then next... and so on and so on. Make sense?

Thank you for your help with this.

 

[CyrusTheVirus]

I'm sorry Dave but the link wasn't really working for me, i tried it a few times but the connection was breaking for some reason.

Would you be able to create a sample spreadsheet in your reply showing what you're trying to do?

 

[DaveMegee]

Ok, I have a sheet "Input" that looks like this...

A B C
1 InputX 0%
2 InputY 1%
3 Result 564
4

The inputs in cells B1 and B2 need to come from the sheet "Table", that looks like this...

A B C D E F
1 0% 1% 2% 3% 4%
2 1%
3 2%
4 3%
5 4%
6 5%
7 6%

what I'm trying to figure out is how to get a macro to take the values in the table B1,A2 and insert them into the sheet "Input" where they will be used in a calculation in a separate sheet that returns a number to the cell B3 in the sheet "Input". I would then like that number to be recorded into the table in cell B2 like this...

A B C D E F
1 0% 1% 2% 3% 4%
2 1% 564
3 2%
4 3%
5 4%
6 5%
7 6%

Once that is completed I would like the macro to continue moving through the values of the table, recording the results, like a multiplication table. Does that make sense?

 

[DaveMegee]

Bump

 

[CyrusTheVirus]

i'm sorry man, but honestly i just can't tell what you're trying to do. how you explained it in post no. 7 just doesn't make sense to me. Maybe it would to someone else.

Either way though, I don't write VBA, but I work with tables every day, I would be supplying you with a formula, not VBA. You're more than welcome to re-word your explanation along with formatting your tables (you know, you can copy/paste from excel which would make it much easier for me) and i'll take another look at your problem.

 

[DaveMegee]

That's okay Cyrus! I appreciate you taking the time to try and help me. I do believe this will be a VBA problem though, so I will continue to try and figure it out. Thanks again!