What Excel VBA code will create a table of answer cells after inputting a range of values in sequence into an input cell used in formulas?

[R3vM0nd4y5]

Hi! I have a Workbook with 2 sheets. Sheet1 contains an input cell, "C4" which is used in formula cells which spit out values into answer cells, "H4:M4" and "H6:M6." Sheet 2 has a list of input values, "A2:A52" to be inputted in Sheet1's cell C4 in sequence starting with A2 ending at A52. I want to then take the answers from each sequence/iteration from the answer cells "H4:M4" and "H6:M6" and place these values in Sheet2's cells "B2:H52." My goal is to be able to create this table in Sheet2 "A2:B52" so I create charts from it.

Please refer to snips of my excel file which will correspond to the sheet and cell references above.

I would like to learn how this can be done in Excel VBA please. Please help! I am stuck!

 

[Akuini]

Hi, @R3vM0nd4y5 Welcome to the Forum
In your example, when C4 = 25, what the result should look like in sheet2?
I'm confused because the answer cells, "H4:M4" and "H6:M6" have 12 cells with values but the result in sheet2 has only 7 empty cells to fill in.

 

[R3vM0nd4y5]

Hi, @R3vM0nd4y5 Welcome to the Forum
In your example, when C4 = 25, what the result should look like in sheet2?
I'm confused because the answer cells, "H4:M4" and "H6:M6" have 12 cells with values but the result in sheet2 has only 7 empty cells to fill in.

Sorry. You are correct. See new attached snips for clarification. There are 12 answer cells now in Sheet2 for each iteration. The 12 answers for each row/iteration in Sheet2 come from Sheet1's "H4:M4" and "H5:M5" now.

 

[Akuini]

Ok, try this:
Sheet2 must be the active sheet when you run the macro.
I'm using random formula in H4:M5, but the formula are irrelevant to the macro.

Sub a1158376a()
'https://www.mrexcel.com/board/threads/what-excel-vba-code-will-create-a-table-of-answer-cells-after-inputting-a-range-of-values-in-sequence-into-an-input-cell-used-in-formulas.1158376/
Dim va
Dim i As Long

va = Range("A1", Cells(Rows.Count, "A").End(xlUp))

With Sheets("Sheet1")
    
    For i = 2 To UBound(va, 1)
        
        .Range("C4") = va(i, 1)
        Range("B" & i).Resize(1, 6).Value = .Range("H4:M4").Value
        Range("B" & i).Offset(, 6).Resize(1, 6).Value = .Range("H5:M5").Value
        
    Next
    
End With

Book1
	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4	30					40	50	60	70	80	90
5						80	90	100	110	120	130
6
Sheet1

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	25	35	45	55	65	75	85	75	85	95	105	115	125
3	26	36	46	56	66	76	86	76	86	96	106	116	126
4	27	37	47	57	67	77	87	77	87	97	107	117	127
5	28	38	48	58	68	78	88	78	88	98	108	118	128
6	29	39	49	59	69	79	89	79	89	99	109	119	129
7	30	40	50	60	70	80	90	80	90	100	110	120	130
8
9
Sheet2

 

[R3vM0nd4y5]

Pretty slick. Thanks. Worked like a charm. So I spent many hours scouring the internet searching for any teachings that may solve my task in Excel VBA. There was plenty on using VBA ranges, VBA cell reference methods, For loops, and the like...but nothing that would remotely perform the task I was looking for. Can you point me in the right direction on what literature I should be studying to get better at this sort of thing? Thanks a million btw.

 

[R3vM0nd4y5]

Ok, try this:
Sheet2 must be the active sheet when you run the macro.
I'm using random formula in H4:M5, but the formula are irrelevant to the macro.

Sub a1158376a()
'https://www.mrexcel.com/board/threads/what-excel-vba-code-will-create-a-table-of-answer-cells-after-inputting-a-range-of-values-in-sequence-into-an-input-cell-used-in-formulas.1158376/
Dim va
Dim i As Long

va = Range("A1", Cells(Rows.Count, "A").End(xlUp))

With Sheets("Sheet1")
   
    For i = 2 To UBound(va, 1)
       
        .Range("C4") = va(i, 1)
        Range("B" & i).Resize(1, 6).Value = .Range("H4:M4").Value
        Range("B" & i).Offset(, 6).Resize(1, 6).Value = .Range("H5:M5").Value
       
    Next
   
End With

Book1
	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4	30					40	50	60	70	80	90
5						80	90	100	110	120	130
6
Sheet1

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	25	35	45	55	65	75	85	75	85	95	105	115	125
3	26	36	46	56	66	76	86	76	86	96	106	116	126
4	27	37	47	57	67	77	87	77	87	97	107	117	127
5	28	38	48	58	68	78	88	78	88	98	108	118	128
6	29	39	49	59	69	79	89	79	89	99	109	119	129
7	30	40	50	60	70	80	90	80	90	100	110	120	130
8
9
Sheet2

Just needed to add an End Sub below the End With for anyone else needing this solution.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

Can you point me in the right direction on what literature I should be studying to get better at this sort of thing?

This one is a good source:
Excel Macro Mastery