Emmily
Well-known Member
- Joined
- Oct 5, 2008
- Messages
- 676
Hi
I have a work task which I want to automate via VBA. I want the code to first clear the data in Col K "ControlSheet" and then do a lookup by looking at Col I and J in Sheet "ControlSheet" and compare this to the concatenated data in Col D in Sheet "HiportData" if match found then return Holding amount in Col K based on the holding shown in Col I "HiportData". If match not found then return 0 rather than #N/A. Please note there will be trailing spaces in the data, so the code will need to remove these first.
Real data will be around 1500 rows.
Thanks in advance
I have a work task which I want to automate via VBA. I want the code to first clear the data in Col K "ControlSheet" and then do a lookup by looking at Col I and J in Sheet "ControlSheet" and compare this to the concatenated data in Col D in Sheet "HiportData" if match found then return Holding amount in Col K based on the holding shown in Col I "HiportData". If match not found then return 0 rather than #N/A. Please note there will be trailing spaces in the data, so the code will need to remove these first.
Real data will be around 1500 rows.
Excel Workbook | |||||
---|---|---|---|---|---|
I | J | K | |||
1 | Security Code | Pfolio Code | Hiport Holding | ||
2 | EQI0005 | HRISII | 15636.36 | ||
3 | AMPSCF | CSFAUP | |||
4 | MFAFS004 | N4S15X | |||
5 | MFAFS005 | WSRAPP | |||
ControlSheet |
Excel Workbook | ||||
---|---|---|---|---|
D | I | |||
1 | UUTID | Holding | ||
2 | MFAFS004N4S15X | 1,000.00 | ||
3 | EQI0005HRISII | 15,636.36 | ||
4 | MFAFS005WSRAPP | 98,784.00 | ||
HiportData |
Thanks in advance