We need a simple XLS macro. I am estimating that it will be 20-30 lines long. For a VBA / Microsoft programmer I think this would be considered easy. And probably 1 or 2 hours work.
We need this within 10 hours so its a “straight-to-work” job.
Bonus paid if we get it within 3 hours.
We have a spreadsheet of data (attached). Each row is one record.
There are 27 Fields in each row, however we only have the data for 25 of them. The macro needs to calculate the data in the last two colums of each row (These are the “Catalog” and the “UKP value”, currency). The calculation is as follows:
UKP Value: To calculate the UKP value, read the value in the “Partner Share Currency”, this will either be AUD, EUR, USD, NZD, CAN, JPY or UKP. If the currency is set to UKP then just copy the value in “Extended Partner Share” to the red “UKP Value”. If it is not UKP then multiply the “Extended Partner Share” value by the appropiate exchange rate and then write into the “UKP Value” field. These are listed below. The result should be a number with 2 decimal places.
Catalog: To calcualte the Catalog value you need to read the value in the ISRC column and then look it up in a table of values. The table of values is in the 2nd tab of the spreadsheet “ISRC2Cat”. Remove the leading and trailing spaces from the ISRC and then match it to one in the ISRC2Cat table. When you match the value get the corresponding “Cat” number (e.g. Cat 61) and write that (as text) into the Cat field in Column Z. If there is no match
write “NO MATCH”.
Other general requirements
- Leave all other columns alone
- The macro should start from Row 1
- Make the macro read every row of the Input Sheet
- Pls comment the Code WELL
I enclose a sample set of data and ISRC codes however the final version should be capable of dealing with sheets containing up to 65535 rows of data.
I am online for 4 more hours on MSN to answer any questions.
AUD 0.45
USD 0.70
EUR 0.92
NZD 0.40
CAN 0.60
JPY 0.007