Excel VBA, Loop, array, cut and paste

B

bbcdancer

Is it possible using VBA to convert (table1 in worksheet1: Start) to
(table1 in worksheet1: End Result)?

(table1 in worksheet1: Start)
Cust Payment Date BlankCol Res1 Res2 Res3
A M 12-Sep
B M 13-Aug
CC M 01-Aug
DD Q 08-Jun
FF Q 10-Aug
GG SA 03-Jul
HH SA 16-Dec
II SA 02-Dec
JJ Y 15-Apr
KK Y 05-Apr
LL Y 05-Sep
BB M 16-Mar
ED Q 11-Nov
.... ... ...

(table1 in worksheet1: End Result)
Cust Payment Date BlankCol Res1 Res2 Res3
A M 12-Sep M1 M2 M3
B M 13-Aug M1 M2 M3
CC M 01-Aug M1 M2 M3
DD Q 08-Jun Q1 Q2 Q3
FF Q 10-Aug Q1 Q2 Q3
GG SA 03-Jul SA1 SA2 SA3
HH SA 16-Dec SA1 SA2 SA3
II SA 02-Dec SA1 SA2 SA3
JJ Y 15-Apr Y1 Y2 Y3
KK Y 05-Apr Y1 Y2 Y3
LL Y 05-Sep Y1 Y2 Y3
BB M 16-Mar M1 M2 M3
ED Q 11-Nov Q1 Q2 Q3
.... ... ...


What I would like is a macro which looks at the Payment field in table1
(eg. Payment = M), cut the corresponding
Date parameter for that record and paste in to the correct cell in
table2 worksheet. Table1 Payment "M" = Table2 Payment"M"

table2 in worksheet2
Payment Date Res1 Res2 Res3
M ?? M1 M21 M3
Q ?? Q1 Q2 Q33
SA ?? SA1 SA2 SA3
Y ?? Y1 Y23 Y3
.... ?? ETC ETC ETC



Example1:

Payment Date Res1 Res2 Res3
M 12-Sep M1 M21 M3
Q ?? Q1 Q2 Q33
SA ?? SA1 SA2 SA3
Y ?? Y1 Y23 Y3
.... ?? ETC ETC ETC

Another Example:

Payment Date Res1 Res2 Res3
M ?? M1 M21 M3
Q ?? Q1 Q2 Q33
SA 03-Jul SA1 SA2 SA3
Y ?? Y1 Y23 Y3
.... ?? ETC ETC ETC


Next, it could cut the corresponding cells M1, M21 and M3 in table2 to
table1.

Example.
Cust Payment Date BlankCol Res1 Res2 Res3
A M 12-Sep M1 M2 M3

Aonther Example.
Cust Payment Date BlankCol Res1 Res2 Res3
A SA 03-Jul SA1 SA2 SA3

This process will repeat itself for the next record in table1 until
each record is done. So the final table would look like table2.

Conditons.
1. If there is no match betwwen table1 and table2 with respect to
Payment field, it will move onto the next record in table1.
2. Number of record in table1 and table2 are unknown and maybe more.
3. What is cut and paste from table2 to table1 will be values and not
formulas.
3. What is cut and paste from table1 to table2 will be date value and
hence it will alter the results of cut and paste back from table2 to
table1.
4. blank column for table1.

Any help would be would be great help...

Getting in a right old mess with Excel VBA.

Not looking for a formula based solution as I will be modifying the VBA
code and getting my head to understand how the code works.


Many thanks in advance.

Kind regards.
 
T

Tom Ogilvy

Dim rng1 as Range, cell as Range
Dim res as Variant
rng1 = Range("Table1").Columns(1).Cells
for each cell in Range("Table1").Columns(2).Cells
res = Application.Match(cell.Value,rng1,0)
if not iserror(res) then
cell.offset(0,3).Resize(1,3).Value = _
rng1(res).Offset(0,2).Resize(1,3).Value
end if
Next
 
Top