# vba programming and excel function problems

VBA programming and Excel function problems

Please complete the following Exercises with VBA programming and excel function, see the attachment for full disclosure of an examples in Exercise 3.

Exercise 1

Please write a VBA program to sum A1:A5 conditioning on B1:B5. More specifically, add cells in A column if the corresponding cell in column B equals 1, and ignore the value if the corresponding cell in column B equals 0. Send an error message if column B contains other values. You should output your result to A6.

For example:

A1:A5=[1,2,3,4,5]

B1:B5=[1,0,0,1,0]

Your output to A6 should be 5 (adding 1 and 4, ignoring other)

A1:A5=[1,2,3,4,5]

B1:B5=[1,0,2,1,0]

Your output to A6 should be #N/A and an error message box should pop out.

Exercise 2

Write a VBA code that will copy all the cells on the current worksheet and paste the values to the same worksheet, so that all the cells that originally contain formulas will now become values only. (Hint: use record macro)

Exercise 3

Write a function to look for a value from the first column in a table, and retrieve the corresponding value from another specified column in the table (similar to the function Vlookup in excel).

You should define your function as myLookup(value, table, index), where value should be the value to be found in the first column of the table, table should be a range in Excel and index is the number of the column in the table from which to retrieve the value. (Don’t call the WorkSheetFunction for this Exercise)

Your function should be able to adapt to different size of tables. You should return “Cannot find value” if the value is not found in the first column, and “Index out of range” if the index is greater than the number of columns of the table.

With the above case, myLookup(“Alice”, A1:C5, 2) should return 21, myLookup(“Charles”, A1:C5, 3) should return “Male”, myLookup(“Elena”, A1:C5, 2) should return “Cannot find value”, and myLookup(“Bob”, A1:C5, 4) should return “Index out of range”. 