Array Values to A Range of Cells

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,562
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I'm very inexperienced with array's and really not able to understand any online explanations.

I would like to take a list of 13 known values and populate 13 consecutive cells with those values.

ie CUPEWP, CUPERP, CUPEE1, CUPEE2, CUPEL1, HPE1, HPE2, HPL1, RPE1, RPE2, RPL1, WPE1, WPE2, WPL1 to populate range A3:A16

I could do 13 lines of code of .range("A3") = "CUPEWP" etc, but I'm wondering if there is a cleaner, more efficient way of doing this. Maybe with an array?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
I'm very inexperienced with array's and really not able to understand any online explanations.

I would like to take a list of 13 known values and populate 13 consecutive cells with those values.

ie CUPEWP, CUPERP, CUPEE1, CUPEE2, CUPEL1, HPE1, HPE2, HPL1, RPE1, RPE2, RPL1, WPE1, WPE2, WPL1 to populate range A3:A16

I could do 13 lines of code of .range("A3") = "CUPEWP" etc, but I'm wondering if there is a cleaner, more efficient way of doing this. Maybe with an array?
Select Range A3:A16 and enter the below array formula . IT's an array formula so confirm with Ctrl + Shift + Enter.
={"CUPEWP";" CUPERP";" CUPEE1";" CUPEE2";" CUPEL1";" HPE1";" HPE2";" HPL1";" RPE1";" RPE2";" RPL1";" WPE1";" WPE2";" WPL1"}
 
Upvote 0
Hi snjpverma .... thanks for your reply. I feel terrible ... I forgot to specify that I was looking for a VBA solution.
 
Upvote 0
Is this what you need?
Code:
Sub Test()
    Range("A3:A16") = WorksheetFunction.Transpose(Array("CUPEWP", _
        "CUPERP", "CUPEE1", "CUPEE2", "CUPEL1", "HPE1", "HPE2", _
        "HPL1", "RPE1", "RPE2", "RPL1", "WPE1", "WPE2", "WPL1"))
End Sub
 
Upvote 0
Hi snjpverma .... thanks for your reply. I feel terrible ... I forgot to specify that I was looking for a VBA solution.
Using what snjpverma posted as a basis...
Code:
[table="width: 500"]
[tr]
	[td]Range("A3:A16")=[{"CUPEWP";"CUPERP";"CUPEE1";"CUPEE2";"CUPEL1";"HPE1";"HPE2";"HPL1";"RPE1";"RPE2";"RPL1";"WPE1";"WPE2";"WPL1"}][/td]
[/tr]
[/table]
 
Upvote 0
Thank you all ... I hadn't seen anywhere else this concept, and was exactly what I needed. Thank you very much!
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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
Back
Top