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

New Member
Joined
Jan 13, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
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!
 

Attachments

  • snip.BSC.png
    snip.BSC.png
    87.2 KB · Views: 2
  • snip.autox.png
    snip.autox.png
    35.9 KB · Views: 2

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 13, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Attachments

  • snip.BSC.png
    snip.BSC.png
    87.2 KB · Views: 1
  • snip.autox.png
    snip.autox.png
    43.2 KB · Views: 1

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
CDEFGHIJKLM
1
2
3
430405060708090
58090100110120130
6
Sheet1


Book1
ABCDEFGHIJKLM
1
225354555657585758595105115125
326364656667686768696106116126
427374757677787778797107117127
528384858687888788898108118128
629394959697989798999109119129
7304050607080908090100110120130
8
9
Sheet2
 
Solution

R3vM0nd4y5

New Member
Joined
Jan 13, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jan 13, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
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.

VBA Code:
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
CDEFGHIJKLM
1
2
3
430405060708090
58090100110120130
6
Sheet1


Book1
ABCDEFGHIJKLM
1
225354555657585758595105115125
326364656667686768696106116126
427374757677787778797107117127
528384858687888788898108118128
629394959697989798999109119129
7304050607080908090100110120130
8
9
Sheet2
Just needed to add an End Sub below the End With for anyone else needing this solution.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,341
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top