Formula required or vba to input data automatically for some columns from database

R

RAM1972

Board Regular
Joined
Jun 29, 2014
Messages
217
Hi All

I am looking formula or vba to fill some columns in order to make calculations from these columns .

However in sheet data calculation column A is entered manually depending data recieved in column L,M,N .

In entering the code manually in column A and B for data calculation sheet,a formula or vba will fetch data in sheet data code data taking column A in account from B to I to fill the data calculation sheet column C and J

Note Codedata can have 20000 records

See annexed for explicit details .:confused::confused:

Thanks in advance those who can help me .

This is the first sheet called Code Data

[TABLE="width: 984"]
<tbody>[TR]
[TD]H. Code[/TD]
[TD]Description[/TD]
[TD]Statistical Unit[/TD]
[TD]General %[/TD]
[TD]Excise_1[/TD]
[TD]Excise_2[/TD]
[TD]Excise_3[/TD]
[TD]VAT %[/TD]
[TD]Agency[/TD]
[/TR]
[TR]
[TD="align: right"]22011011[/TD]
[TD]----- Mineral waters:In plastic bottles Waters, including natural or artificial[/TD]
[TD]L[/TD]
[TD="align: right"]15%[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[/TR]
[TR]
[TD="align: right"]22011019[/TD]
[TD]---- Other[/TD]
[TD]L[/TD]
[TD="align: right"]15%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[/TR]
[TR]
[TD="align: right"]22011021[/TD]
[TD]Aerated waters- In plastic bottles[/TD]
[TD]L[/TD]
[TD="align: right"]15%[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[/TR]
[TR]
[TD="align: right"]22011029[/TD]
[TD]---- Other[/TD]
[TD]L[/TD]
[TD="align: right"]15%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[/TR]
[TR]
[TD="align: right"]22019010[/TD]
[TD]-- Other: - In plastic bottles -[/TD]
[TD]L[/TD]
[TD="align: right"]15%[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[/TR]
[TR]
[TD="align: right"]22019090[/TD]
[TD]--- Other[/TD]
[TD]L[/TD]
[TD="align: right"]15%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[/TR]
[TR]
[TD="align: right"]22086010[/TD]
[TD]--- Vodka produced from alcohol
obtained by treating fermented mash
of cereals or potato[/TD]
[TD]L[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]1455[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[/TR]
[TR]
[TD="align: right"]22086090[/TD]
[TD]--- Other[/TD]
[TD]L[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]471[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[/TR]
[TR]
[TD="align: right"]22087000[/TD]
[TD]- Liqueurs and cordials[/TD]
[TD]L[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]320[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[/TR]
[TR]
[TD="align: right"]22089011[/TD]
[TD]---- In bulk for bottling purposes[/TD]
[TD]L[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]910[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[/TR]
[TR]
[TD="align: right"]22089019[/TD]
[TD]---- Other[/TD]
[TD]L[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]1455[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[/TR]
[TR]
[TD="align: right"]22089021[/TD]
[TD]---- In can[/TD]
[TD]L[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]44.7[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[/TR]
[TR]
[TD="align: right"]22089029[/TD]
[TD]---- Other[/TD]
[TD]L[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]44.7[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[/TR]
[TR]
[TD="align: right"]62044300[/TD]
[TD]-- Of synthetic fibres[/TD]
[TD]U[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]62044400[/TD]
[TD]-- Of artificial fibres[/TD]
[TD]U[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]62044900[/TD]
[TD]-- Of other textile materials[/TD]
[TD]U[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]62045100[/TD]
[TD]-- Of wool or fine animal hair[/TD]
[TD]U[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]62045200[/TD]
[TD]-- Of cotton[/TD]
[TD]U[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]62045300[/TD]
[TD]-- Of synthetic fibres[/TD]
[TD]U[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01041000[/TD]
[TD]- Sheep[/TD]
[TD]U[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EXM[/TD]
[TD]DVS[/TD]
[/TR]
[TR]
[TD]01042000[/TD]
[TD]- Goats[/TD]
[TD]U[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EXM[/TD]
[TD]DVS[/TD]
[/TR]
[TR]
[TD]01051100[/TD]
[TD]-- Fowls of the species Gallus
domesticus[/TD]
[TD]kg[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[TD]DVS[/TD]
[/TR]
[TR]
[TD]01051200[/TD]
[TD]-- Turkeys[/TD]
[TD]kg[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[TD]DVS[/TD]
[/TR]
[TR]
[TD]01051300[/TD]
[TD]-- Ducks[/TD]
[TD]kg[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[TD]DVS[/TD]
[/TR]
[TR]
[TD]01051400[/TD]
[TD]-- Geese[/TD]
[TD]kg[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[TD]DVS[/TD]
[/TR]
[TR]
[TD]01051500[/TD]
[TD]-- Guinea fowls[/TD]
[TD]kg[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[TD]DVS[/TD]
[/TR]
[TR]
[TD]01059400[/TD]
[TD]-- Fowls of the species Gallus[/TD]
[TD]kg[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[TD]DVS[/TD]
[/TR]
[TR]
[TD]01059900[/TD]
[TD]-- Other[/TD]
[TD]kg[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[TD]DVS[/TD]
[/TR]
[TR]
[TD]01061100[/TD]
[TD]-- Primates[/TD]
[TD]kg[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]DVS[/TD]
[/TR]
[TR]
[TD]01061200[/TD]
[TD]-- Whales, dolphins and
porpoises (mammals of the
order Cetacea); manatees and
dugongs (mammals of the
order Sirenia); seals, sea lions
and walruses (mammals of the
suborder Pinnipedia)[/TD]
[TD]kg[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]DVS[/TD]
[/TR]
[TR]
[TD]01061300[/TD]
[TD]-- Camels and other camelids
(Camelidae)[/TD]
[TD]kg[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]DVS[/TD]
[/TR]
[TR]
[TD]01061400[/TD]
[TD]-- Rabbits and hares
-- Other:[/TD]
[TD]kg[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]DVS[/TD]
[/TR]
[TR]
[TD]09021000[/TD]
[TD]- Green tea (not fermented) in
immediate packings of a content
not exceeding 3 kg[/TD]
[TD]kg[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[TD]NPPO+
FIU+
Napro[/TD]
[/TR]
[TR]
[TD]09022000[/TD]
[TD]- Other green tea (not fermented)[/TD]
[TD]kg[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[TD]NPPO+[/TD]
[/TR]
[TR]
[TD]09023000[/TD]
[TD]- Black tea (fermented) and partly
fermented tea, in immediate
packings of a content not
exceeding 3 kg[/TD]
[TD]kg[/TD]
[TD="align: right"]30%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[TD]NPPO+
FIU+
Napro[/TD]
[/TR]
[TR]
[TD]09024000[/TD]
[TD]- Other black tea (fermented) and
other partly fermented tea[/TD]
[TD]kg[/TD]
[TD="align: right"]30%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[TD]NPPO+
FIU+
Napro[/TD]
[/TR]
[TR]
[TD]09030000[/TD]
[TD]Maté.[/TD]
[TD]kg[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EXM[/TD]
[TD]NPPO+[/TD]
[/TR]
[TR]
[TD]09041100[/TD]
[TD]- Pepper:
-- Neither crushed nor ground[/TD]
[TD]kg[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[TD]NPPO+[/TD]
[/TR]
[TR]
[TD]09041200[/TD]
[TD]-- Crushed or ground[/TD]
[TD]kg[/TD]
[TD="align: right"]10%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[TD]NPPO+[/TD]
[/TR]
[TR]
[TD]09042100[/TD]
[TD]-- Dried, neither crushed nor
ground[/TD]
[TD]kg[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[TD]NPPO+
FIU[/TD]
[/TR]
[TR]
[TD]09042200[/TD]
[TD]-- Crushed or ground[/TD]
[TD]kg[/TD]
[TD="align: right"]10%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[TD]NPPO+[/TD]
[/TR]
[TR]
[TD="align: right"]22021020[/TD]
[TD]--- In can[/TD]
[TD]L[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.03[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[/TR]
[TR]
[TD="align: right"]22021090[/TD]
[TD]--- Other[/TD]
[TD]L[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.03[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[/TR]
[TR]
[TD="align: right"]22029010[/TD]
[TD]--- Soya milk[/TD]
[TD]L[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0.03[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[/TR]
[TR]
[TD="align: right"]22029020[/TD]
[TD]--- Aloe vera gel and aloe vera[/TD]
[TD]L[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[/TR]
[TR]
[TD="align: right"]22029030[/TD]
[TD]--- Fruit drinks[/TD]
[TD]L[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0.03[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[/TR]
[TR]
[TD="align: right"]22029040[/TD]
[TD]--- Almond milk[/TD]
[TD]L[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[/TR]
[TR]
[TD="align: right"]22029050[/TD]
[TD]--- Oat milk[/TD]
[TD]L[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[/TR]
[TR]
[TD="align: right"]22029060[/TD]
[TD]--- Rice milk[/TD]
[TD]L[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[/TR]
[TR]
[TD="align: right"]22029070[/TD]
[TD]--- Other beverages of milk, tea,
coffee, cocoa and chocolate[/TD]
[TD]L[/TD]
[TD="align: right"]15%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[/TR]
[TR]
[TD="align: right"]22029090[/TD]
[TD]--- Other[/TD]
[TD]L[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.03[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[/TR]
[TR]
[TD="align: right"]87082294[/TD]
[TD]Vehicles[/TD]
[TD]U[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]15%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]87082295[/TD]
[TD]Vehicles[/TD]
[TD]U[/TD]
[TD="align: right"]55%[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]15%[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This the second sheet called data calculation is required


[TABLE="width: 1673"]
<tbody>[TR]
[TD]H code[/TD]
[TD]h Description[/TD]
[TD]Description
[/TD]
[TD]Statistical Unit[/TD]
[TD]General %[/TD]
[TD]Excise_1[/TD]
[TD]Excise_2[/TD]
[TD]Excise_3[/TD]
[TD]VAT %[/TD]
[TD]Agency[/TD]
[TD]data header_1[/TD]
[TD]data header_ 2`[/TD]
[TD]data header_ 3[/TD]
[TD]data header_ 4[/TD]
[/TR]
[TR]
[TD="align: right"]22011021
[/TD]
[TD]Aerated waters- In plastic bottles
[/TD]
[TD]----- Mineral waters:In plastic bottles Waters, including natural or artificial[/TD]
[TD]L[/TD]
[TD="align: right"]15%[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD="align: right"]22011021[/TD]
[TD]Aerated waters- In plastic bottles[/TD]
[TD]----- Mineral waters:In plastic bottles Waters, including natural or artificial[/TD]
[TD]L[/TD]
[TD="align: right"]15%[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD="align: right"]22087000[/TD]
[TD]- Liqueurs and cordials
[/TD]
[TD]----- Mineral waters:In plastic bottles Waters, including natural or artificial[/TD]
[TD]L[/TD]
[TD="align: right"]15%[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD="align: right"]22087000[/TD]
[TD]- Liqueurs and cordials
[/TD]
[TD]----- Mineral waters:In plastic bottles Waters, including natural or artificial
[/TD]
[TD]L[/TD]
[TD="align: right"]15%[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD="align: right"]22089021[/TD]
[TD]---- In can
[/TD]
[TD]----- Mineral waters:In plastic bottles Waters, including natural or artificial[/TD]
[TD]L[/TD]
[TD="align: right"]15%[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD="align: right"]22089021[/TD]
[TD]---- In can[/TD]
[TD]----- Mineral waters:In plastic bottles Waters, including natural or artificial[/TD]
[TD]L[/TD]
[TD="align: right"]15%[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]01051500[/TD]
[TD]-- Guinea fowls[/TD]
[TD]-- Guinea fowls[/TD]
[TD]kg[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[TD]DVS[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD="align: right"]22021020[/TD]
[TD]--- In can[/TD]
[TD]--- In can[/TD]
[TD]L[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.03[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD="align: right"]22021020[/TD]
[TD]--- In can[/TD]
[TD]--- In can[/TD]
[TD]L[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.03[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD="align: right"]22029010[/TD]
[TD]--- Soya milk[/TD]
[TD]--- Soya milk[/TD]
[TD]L[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0.03[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD="align: right"]22011019[/TD]
[TD]---- Other[/TD]
[TD]---- Other[/TD]
[TD]L[/TD]
[TD="align: right"]15%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]09042200[/TD]
[TD]-- Crushed or ground[/TD]
[TD]-- Crushed or ground[/TD]
[TD]kg[/TD]
[TD="align: right"]10%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[TD]NPPO+[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]09042200[/TD]
[TD]-- Crushed or ground[/TD]
[TD]-- Crushed or ground[/TD]
[TD]kg[/TD]
[TD="align: right"]10%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD="align: right"]22029060[/TD]
[TD]--- Rice milk[/TD]
[TD]--- Rice milk[/TD]
[TD]L[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD]FIU[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD="align: right"]62045300[/TD]
[TD]-- Of synthetic fibres[/TD]
[TD]-- Of synthetic fibres[/TD]
[TD]U[/TD]
[TD="align: right"]30%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15%[/TD]
[TD][/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]data entered manually
[/TD]
[TD]data entered manually
[/TD]
[TD]formula or vba required
[/TD]
[TD]formula or vba required [/TD]
[TD]formula or vba required [/TD]
[TD]formula or vba required [/TD]
[TD]formula or vba required [/TD]
[TD]formula or vba required [/TD]
[TD]formula or vba required [/TD]
[TD]formula or vba required [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
try this code:

Code: 
Sub RAM1972()


Dim LastColumn As Integer


Dim LastRow As Long


Dim i As Integer


Dim crit As String


Sheets("Code Data").Select


If WorksheetFunction.CountA(Cells) > 0 Then
    LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    LastColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    MsgBox LastRow & " " & LastColumn
End If


For i = 2 To LastRow  'range of the HCode
crit = Sheets("Data Calculation").Cells(i, 1)
With Worksheets("Code Data")
    .Range("A1", Cells(1, LastColumn)).AutoFilter
    .Range("A1", Cells(1, LastColumn)).AutoFilter field:=1, Criteria1:="=" & crit
    Sheets("Data Calculation").Cells(i, 3) = Sheets("Code Data").Range("B2", Cells(LastRow, LastColumn))
End With
Next i


End Sub



--------------------------------------------------


Please Give me like if my post helps


Need my help further?


id.linkedin.com/in/williamgowtama


Thank you :D
 
Upvote 0
Tried code not functionning, see screenshots. advise

[removed garbled image code - Rory]
 
Last edited by a moderator:
Upvote 0
Hello ram1972,

i can't see the screenshot, please upload the image to another image hosting.

or tell me the error that it causes

thank you
 
Upvote 0
For the time being, try this code. sorry for late reply, because im at workplace and doing a project right now.
Code: 
Sub RAM1972()


Dim LastColumn As Integer


Dim LastRow As Long


Dim i As Integer


Dim crit As String


Sheets("Code Data").Select


If WorksheetFunction.CountA(Cells) > 0 Then
    LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    LastColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
End If


For i = 2 To LastRow  'range of the HCode
crit = Sheets("Data Calculation").Cells(i, 1)
With Worksheets("Code Data")
    .Range("A1", Cells(1, LastColumn)).AutoFilter
    .Range("A1", Cells(LastRow, LastColumn)).AutoFilter field:=1, Criteria1:="=" & crit
    Sheets("Data Calculation").Cells(i, 3) = Sheets("Code Data").Range("B2", Cells(LastRow, LastColumn))
End With
Next i


End Sub
 
Upvote 0
i revised my code, this should contain no error
Code: 
Sub RAM1972()


Dim LastColumn As Integer


Dim LastRow As Long


Dim i As Integer


Dim crit As String


Sheets("Code Data").Select


If WorksheetFunction.CountA(Cells) > 0 Then
    LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    LastColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
End If


For i = 2 To LastRow  'range of the HCode
crit = Sheets("Data Calculation").Cells(i, 1)
With Worksheets("Code Data")
    .Range("A1", Cells(1, LastColumn)).AutoFilter
    .Range("A1", Cells(LastRow, LastColumn)).AutoFilter field:=1, Criteria1:="=" & crit
Sheets("Code Data").Range("B2", Cells(LastRow, LastColumn)).Select
Selection.copy
    Sheets("Data Calculation").Cells(i, 3).paste
End With
Next i


End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Code to use multiple trigger cells to hide / unhide rows of corresponding question sets
Replies
1
Views
1K
dmt32
D
BigPaws
  • Question Question
VBA to create new worksheets from a template, populate the new worksheet by copying data from another sheet to specific locations.
Replies
0
Views
192
BigPaws
BigPaws
C
  • Question Question
Formula to find Max number of items in orders
Replies
1
Views
665
fjns
F
A
  • Question Question
Combine text from different cells but apply different formatting
Replies
1
Views
1K
attikuz
A
X
  • Question Question
Automate add new data in new row
Replies
0
Views
456
Xing Duong
X

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

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