BA365 Intro to Operationss Management – Final Exam
September 17, 2020
This week there was a “Tribute  to a King” who was played by Chadwick Boseman  w
September 17, 2020

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”.

 

Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount!
Use Discount Code “Newclient” for a 15% Discount!

NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.


The post vba programming and excel function problems appeared first on Urgent Nursing Writers.

 

"Are you looking for this answer? We can Help click Order Now"