Adding loop to current code?

Hello,

I am having some difficulty with a macro I am working on. I know a little bit about VBA but not very much, I mainly know how to record macros in Excel but I did take an entry level VBA course in college a while ago...

Here is what I am trying to accomplish, I want to take two values (from my Results Test worksheet) and input them into a "model" (New Model worksheet) which generates results based on the two inputs. Then take those results and paste them into my Results Test worksheet below the input values I used. I need to do this for numerous columns.

The code below is for just one simulation, I would like to find a way to make this work for a range of columns. The first inputs are in C2 and C3, the next inputs would be D2 and D3, and so on with the generated results below each column of inputs.

Hopefully this makes sense, I am stumped and will be digging out my VBA book from college tonight but I am not to optimistic it will help me out here.

Thanks in advance,
RJ

Sub Single_Simulation_REV1()
'
' Single_Simulation_REV1 Macro

'Grab input values for model (in rows 2 an 3) from the Results Test worksheet
    Sheets("New model").Select
    Range("C5").Select
    ActiveCell.FormulaR1C1 = "='Results TEST'!R[-3]C"
    Range("C6").Select
    ActiveCell.FormulaR1C1 = "='Results TEST'!R[-3]C"
    Range("C7").Select
    
 'Copy and paste first block of results in Results Test worksheet
    Range("J178:J203").Select
    Selection.Copy
    Sheets("Results TEST").Select
    Range("C4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
 'Copy and paste second block of results in Results Test worksheet
    Sheets("New model").Select
    Range("J205:J282").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Results TEST").Select
    Range("C30").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
End Sub

Hi

think I've got the positions right...

Sub aaa()
  Dim WorkSH As Worksheet
  Set WorkSH = Sheets("New Model")
  
  Sheets("Results TEST").Activate
  
  For i = 3 To Cells(2, Columns.Count).End(xlToLeft).Column
    WorkSH.Range("C5:C6").Value = Cells(2, i).Resize(2, 1).Value
    
    Cells(4, i).Resize(26, 1).Value = WorkSH.Range("J178:J203").Value
    
    Cells(30, i).Resize(78, 1).Value = WorkSH.Range("J205:J282").Value
  
  Next i
End Sub

rylo

Thanks Rylo! It worked perfectly, now I am going to try and apply it to another part of my model. Hopefully I won't mess it up to bad.

Thanks again!
RJ

I am using an older version of Excel and I don't have enough columns. How can I switch the code to be in rows? So the inputs pulled from the Results Test sheet would be in A2:B2 then the next inputs in A3:B3... However they will still be input into the New Model sheet in the same cells C5:C6. Then when pasteing the results they will need to be transposed to be in a row and not a column?

Thanks again!
Ryan

Ryan

here you are.

Sub aaa()
  Dim WorkSH As Worksheet
  Set WorkSH = Sheets("New Model")
  
  Sheets("Results TEST").Activate
  
  For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    WorkSH.Range("C5").Value = Cells(i, 1).Value
    WorkSH.Range("C6").Value = Cells(i, 2).Value
    
    WorkSH.Range("J178:J203").Copy
    Cells(i, 3).PasteSpecial xlPasteValues, Transpose:=True
    
    WorkSH.Range("J205:J282").Copy
    Cells(i, 29).PasteSpecial xlPasteValues, Transpose:=True
  
  Next i
  Application.CutCopyMode = False
End Sub

rylo