Using vlookup to copypaste values to row using vba codes

H

HearMePlease

New Member
Joined
Jul 26, 2018
Messages
6
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.

[TABLE="width: 1090"]
<colgroup><col><col><col span="4"><col><col span="9"></colgroup><tbody>[TR]
[TD]Store No_[/TD]
[TD] Amount Tendered [/TD]
[TD][/TD]
[TD]0106[/TD]
[TD]0109[/TD]
[TD]0111[/TD]
[TD]0112[/TD]
[TD]0117[/TD]
[TD]0119[/TD]
[TD]0120[/TD]
[TD]0123[/TD]
[TD]0125[/TD]
[TD]0126[/TD]
[TD]0127[/TD]
[TD]0131[/TD]
[TD]0161[/TD]
[/TR]
[TR]
[TD]0106[/TD]
[TD] 39,250.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0106[/TD]
[TD] 18,400.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0109[/TD]
[TD] 170,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0111[/TD]
[TD] 1,680,200.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0112[/TD]
[TD] 20,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0117[/TD]
[TD] 5,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0119[/TD]
[TD] 4,400.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0120[/TD]
[TD] 3,950.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0123[/TD]
[TD] 15,150.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0125[/TD]
[TD] 13,650.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0126[/TD]
[TD] 653,700.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0127[/TD]
[TD] 16,500.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0131[/TD]
[TD] 3,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0161[/TD]
[TD] 2,000.00

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

Thank you for your help.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
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 ;))

Code: 
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
ABCDEFGHIJKLMNOP
1Store No_Amount Tendered106109111112117119120123125126127131161
210639,250.0039250
310618,400.0018400
4109170,000.00170000
51111,680,200.001680200
611220,000.0020000
71175,000.005000
81194,400.004400
91203,950.003950
1012315,150.0015150
1112513,650.0013650
12126653,700.00653700
1312716,500.0016500
141313,000.003000
151612,000.002000
Allocate to site
 
Upvote 0
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!
 
Upvote 0
HearMePlease said:
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!
Click to expand...
You are very welcome. It is what the forum is for. :)
 
Upvote 0
Hi Peter_SSs,

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

[TABLE="width: 1432"]
<colgroup><col><col><col><col span="4"><col><col><col><col span="2"><col><col span="3"></colgroup><tbody>[TR]
[TD]Store No_[/TD]
[TD] Amount Tendered [/TD]
[TD][/TD]
[TD]0106[/TD]
[TD]0109[/TD]
[TD]0111[/TD]
[TD]0112[/TD]
[TD]0117[/TD]
[TD]0119[/TD]
[TD]0120[/TD]
[TD]0123[/TD]
[TD]0125[/TD]
[TD]0126[/TD]
[TD]0127[/TD]
[TD]0131[/TD]
[TD]0161[/TD]
[/TR]
[TR]
[TD]0106[/TD]
[TD] 39,250.00[/TD]
[TD][/TD]
[TD] 39,250.00[/TD]
[TD] 170,000.00[/TD]
[TD] 1,680,200.00[/TD]
[TD] 20,000.00[/TD]
[TD] 5,000.00[/TD]
[TD] 4,400.00[/TD]
[TD] 3,950.00[/TD]
[TD] 15,150.00[/TD]
[TD] 13,650.00[/TD]
[TD] 653,700.00[/TD]
[TD] 16,500.00[/TD]
[TD] 3,000.00[/TD]
[TD] 2,000.00[/TD]
[/TR]
[TR]
[TD]0106[/TD]
[TD] 18,400.00[/TD]
[TD][/TD]
[TD] 18,400.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0109[/TD]
[TD] 170,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0111[/TD]
[TD] 1,680,200.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0112[/TD]
[TD] 20,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0117[/TD]
[TD] 5,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0119[/TD]
[TD] 4,400.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0120[/TD]
[TD] 3,950.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0123[/TD]
[TD] 15,150.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0125[/TD]
[TD] 13,650.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0126[/TD]
[TD] 653,700.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0127[/TD]
[TD] 16,500.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0131[/TD]
[TD] 3,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0161[/TD]
[TD] 2,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 1432"]
<colgroup><col><col><col><col span="4"><col><col><col><col span="2"><col><col span="3"></colgroup><tbody></tbody>[/TABLE]



Thank you very much.

Regards,

HearMePlease
 
Upvote 0
HearMePlease said:
Can i ask if we can add some codes so that the final report will look like this?
Click to expand...
Sure, just add this blue text
Rich (BB code): 
Cells(c.Row, d(c.Offset(, -1).Value)).End(xlUp).Offset(1).Value = c.Value
 
Upvote 0
You must log in or register to reply here.

Similar threads

SkywardPalm
  • Question Question
Aligning sections for comparison based on section number
Replies
0
Views
316
SkywardPalm
SkywardPalm
P
  • Question Question
Copy values of previus cell, if the value is not zero
Replies
7
Views
1K
ppvivanco
P

Forum statistics

Threads
1,224,749
Messages
6,180,730
Members
452,995
Latest member
isldboy

Share this page

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