VBA code to replace What-if Datatables

[neoseo1300]

Hi everyone,

Until recently, I was using 2-dimension datatables to create scenarios / sensitivities in a complex financial model. The model had to be restructured, forcing one of the two inputs used for the datatables to be located in a different sheet. As you know, datatables don't work if inputs are not in the same tab so I need to work a way around it.

Therefore I'm looking for a way to replace these datatables with an equivalent piece of VBA code. I'm attaching an example of what I'm trying to achieve (i.e. filling out the yellow table with the result of a calculation whose parameters are Input A and Input B). I don't think it's complicated but I'm not familiar enough with VBA to make something that works and that is clean (i.e. efficient - using loop) for that purpose.

Any help would therefore be much appreciated
Thanks!

 

[vletm]

neoseo1300
You hope to make something that works ...
... Could You show with a sample ... what would You need?
... especially if it would be something ...hmm? ... complex ... Your sample should be as close as Your needed layout/code/help...

 

[neoseo1300]

Hi Vletm, sorry if I didn't provide enough details. Attached the previous spreadsheet with the expected result. So basically, I'm trying to recreate what a What-if datatable does but with VBA code only (to go beyond the intrinsic limitations of Excel's datatables).

As you can see in the file, the objective is to fill out the bottom table (range G20:K24) with the result of cell C7 whose calculation is based on 2 parameters C4 (in same sheet - Tab A) and C3 (in another sheet - Tab B). Each turn / loop, vba will change each of the parameters based on the range G19:K19 for parameter A and F20:F24 for parameter B, and copy the result (from C7) in the correct position of the table. Basically, what would a datatable do in this situation.

Note, that this is an example layout but once I have the code for such example, I feel like I can easily adapte it to my actual spreadsheet (by just changing the cell names and locations)

Hope it's clearer, thanks again!

 

[vletm]

neoseo1300
What for is G9:K13?
I use that range, then Your sample results will stay as You've written.
If those Inputs G8:K8 and F9:F13 are fixed, then what matter of values of C4 & C5?
... expect that now those will trigger calculation.
I did two versions
a) modify cell C4
b) modify cell C5
Which one works better?

 

[neoseo1300]

Okay, so I managed to do what I was mentioning before with the following piece of code in the attached file:

Sub Datatable()
Dim i As Integer, j As Integer, t1 As Integer, t2 As Integer

t1 = Worksheets("TAB A").Range("INPUTA").Value
t2 = Worksheets("TAB B").Range("INPUTB").Value

For i = 7 To 11
    For j = 9 To 13
        Worksheets("TAB A").Range("INPUTA").Value = Worksheets("TAB A").Cells(8, i).Value
        Worksheets("TAB B").Range("INPUTB").Value = Worksheets("TAB A").Cells(j, 6).Value
        Worksheets("TAB A").Cells(j, i).Value = Worksheets("TAB A").Range("C7").Value
    Next j
Next i

Worksheets("TAB A").Range("C4").Value = t1
Worksheets("TAB B").Range("C3").Value = t2
End Sub

As you can tell, it's very basic and it only works for a very specific table located in a specific position in the worksheet (I'm literally counting lines and columns). Is there a way to make this code more efficient, cleaner and / or flexible? (so it can easily be adapted to other tables in other situations)

Thanks

 

[vletm]

neoseo1300
Did You notice that I tried to do some questions?
Of course, I could skip all Your writings too - but I'll try to comment something.
Without Your answers to my questions, I cannot know have You read or test those.
Did You test my sent file?
Did You checked that code?
I would use upper code for this - then there would be less steps to do.
But if it needs to manually edit 'formula' from sheet - then lower code.
> Your code
Where do You need that TAB B-sheet?
You wrote question ... You could see one possible answer, if You would have been checked my code.
I would 'clean' some parts away - but it's Your code - Your way to show Your ideas.
There would be possible to use tables or named ranges instead of 'counting lines (rows) and columns', but would it make something different.
For me, that code should be able to verify - how do it work? - is more important than eg how quickly it will run = result should be correct, no matter do it takes few seconds longer.

 

[p45cal]

I don't think you need any code at all.
For a data table to work it needs something in the top left cell.