Using vlookup to copypaste values to row using vba codes

[HearMePlease]

Hey Masters,

Good morning! Im a newbies in VBA and want to learn more about it to speed up my work.

I have this file which needs to be sorted everyday. Copy all the movements to each corresponding sites. Please see below the example.

Store No_	Amount Tendered		0106	0109	0111	0112	0117	0119	0120	0123	0125	0126	0127	0131	0161
0106	39,250.00
0106	18,400.00
0109	170,000.00
0111	1,680,200.00
0112	20,000.00
0117	5,000.00
0119	4,400.00
0120	3,950.00
0123	15,150.00
0125	13,650.00
0126	653,700.00
0127	16,500.00
0131	3,000.00
0161	2,000.00

<colgroup><col><col><col span="4"><col><col span="9"></colgroup><tbody>
</tbody>

Can you please help me copypaste values in each store number using VBA codes.

Thank you for your help.

 

[Peter_SSs]

Welcome to the MrExcel board!

There are many ways to approach most problems in Excel/VBA. Here is one method (assuming that I have interpreted correctly what result you want )

Sub AllocateToSite()
  Dim d As Object
  Dim c As Range
  
  Set d = CreateObject("Scripting.Dictionary")
  For Each c In Range("D1", Range("D1").End(xlToRight))
    d(c.Value) = c.Column
  Next c
  For Each c In Range("B2", Range("B2").End(xlDown))
    Cells(c.Row, d(c.Offset(, -1).Value)).Value = c.Value
  Next c
End Sub

My sheet after running the code.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Store No_	Amount Tendered		106	109	111	112	117	119	120	123	125	126	127	131	161
2	106	39,250.00		39250
3	106	18,400.00		18400
4	109	170,000.00			170000
5	111	1,680,200.00				1680200
6	112	20,000.00					20000
7	117	5,000.00						5000
8	119	4,400.00							4400
9	120	3,950.00								3950
10	123	15,150.00									15150
11	125	13,650.00										13650
12	126	653,700.00											653700
13	127	16,500.00												16500
14	131	3,000.00													3000
15	161	2,000.00														2000
Allocate to site

 

[HearMePlease]

Thank you for your warm welcome!
Thank you Peter_SSs for sharing your knowledge to a newbies like me. This is the code that i really wanted.
It really works!

 

[Peter_SSs]

Thank you for your warm welcome!
Thank you Peter_SSs for sharing your knowledge to a newbies like me. This is the code that i really wanted.
It really works!

You are very welcome. It is what the forum is for.

 

[HearMePlease]

Hi Peter_SSs,

Can i ask if we can add some codes so that the final report will look like this?

Store No_	Amount Tendered		0106	0109	0111	0112	0117	0119	0120	0123	0125	0126	0127	0131	0161
0106	39,250.00		39,250.00	170,000.00	1,680,200.00	20,000.00	5,000.00	4,400.00	3,950.00	15,150.00	13,650.00	653,700.00	16,500.00	3,000.00	2,000.00
0106	18,400.00		18,400.00
0109	170,000.00
0111	1,680,200.00
0112	20,000.00
0117	5,000.00
0119	4,400.00
0120	3,950.00
0123	15,150.00
0125	13,650.00
0126	653,700.00
0127	16,500.00
0131	3,000.00
0161	2,000.00

<colgroup><col><col><col><col span="4"><col><col><col><col span="2"><col><col span="3"></colgroup><tbody>
</tbody>

<colgroup><col><col><col><col span="4"><col><col><col><col span="2"><col><col span="3"></colgroup><tbody></tbody>

Thank you very much.

Regards,

HearMePlease

 

[Peter_SSs]

Can i ask if we can add some codes so that the final report will look like this?

Sure, just add this blue text

Cells(c.Row, d(c.Offset(, -1).Value)).End(xlUp).Offset(1).Value = c.Value

 

[HearMePlease]

Wow!
One of the best days of my life!
Thank you very much Peter_SSs..

 

[Peter_SSs]

Wow!
One of the best days of my life!
Thank you very much Peter_SSs..