Microsoft? Excel? for Microsoft 365 MSO (Version 2302) 32-bit
Posts
22
Need VBA code that applies multiple sets of inputs to model and pastes each set of values
I am trying to take a data set which comprises many different sets of inputs, and run those inputs through a model on a separate sheet and return the relevant outputs back to the data set sheet, and then move to the next row and do the same.
I've attached a sample excel file for context - the inputs to be applied to the "Model" sheet are found on the "Data Tape" sheet - each row in the "Data Tape" sheet represents a different scenario. The outputs to be pasted to columns E:G in "Data Tape" are cells C10:C12 of "Model" sheet.
The end result should be a fully populated Data Tape sheet that ties with the "checks" to the right. This will need to be stable for a few hundred rows without crashing. Really appreciate any help - thank you.
Re: Need VBA code that applies multiple sets of inputs to model and pastes each set of val
Hi, ascheetz1. Try with:
PHP Code:
Sub Macro8()
Dim a, Q&, i&
'-------->
With Sheets("Data Tape")
a = .Range("B4", .[d3].End(xlDown)): Q = UBound(a)
End With
'-------->
With Sheets("Model")
For i = 1 To Q
.[b4:d4] = Array(a(i, 1), a(i, 2), a(i, 3))
a(i, 1) = .[c10].Value: a(i, 2) = .[c11].Value: a(i, 3) = .[c12].Value
Next
End With
'-------->
Range("'Data Tape'!E4:G4").Resize(Q) = a
End Sub
You are always very welcome if you add reputation by clicking the * (bottom left) of each message that has helped you.
Microsoft? Excel? for Microsoft 365 MSO (Version 2302) 32-bit
Posts
22
Re: Need VBA code that applies multiple sets of inputs to model and pastes each set of val
Understood, makes sense. In the actual use case I am going to have a much larger array of inputs (more like 30 input cells than 3 input cells). Is there a way to adjust the below section of code so that I don't need to manually assign each individual cell to the array?
Re: Need VBA code that applies multiple sets of inputs to model and pastes each set of val
This code snippet is not the only thing you have to adapt.
Look what follows:
PHP Code:
Sub Macro9()
Const qCol% = 3
Dim a, Q&, i&, j%
Rem -------->
With Sheets("Data Tape")
a = .Range("B4", .[b3].End(xlDown)).Resize(, qCol): Q = UBound(a)
End With
Rem -------->
With Sheets("Model")
For i = 1 To Q
.[b4].Resize(, qCol) = Application.Index(a, i, 0)
For j = 1 To qCol
a(i, j) = .Range("C10")(j, 1)
Next
Next
End With
Rem -------->
Range("'Data Tape'!B4").Offset(, qCol).Resize(Q, qCol) = a
End Sub
The variable 'qCol' indicates the number of columns to take into account.
Microsoft? Excel? for Microsoft 365 MSO (Version 2302) 32-bit
Posts
22
Re: Need VBA code that applies multiple sets of inputs to model and pastes each set of val
I tried adjusting qCol to 15 for a larger example set and it doesn't appear to be working. Do you mind taking a look at the attached file and let me know where I'm going wrong?
The intended results to be pasted into Q4:Z23 of "Data Tape" are found in columns AB:AK (calculated manually).
Once I have this example figured out I should be able to adapt for the actual use case - really appreciate all of your help.
Re: Need VBA code that applies multiple sets of inputs to model and pastes each set of val
I am going to make a comment that you should take into account in future consultations in the Forums:
- It is almost never important how many rows your model has.
- The exact data in your model is almost never important unless you have to look for something specific in the cells.
- But the number of columns in the model should always reflect the actual situation.
LinkBack URL
About LinkBacks
Register To Reply
Originally Posted by ascheetz1
Bookmarks