Question

Question

(excel)

1. Add a worksheet to the file and call it Output.

2. In the Output sheet:

a. enter first name in cell A1.    

b. enter last name in cell B1.

3. Record a macro. Call your macro CIS308Exam3. Within your macro, do the following:     

a. In cell C1, use a formula to concatenate A1 and B1 with a space in between    

b. Stop recording.

4. Open your macro in Visual Basic edit mode. Copy your macro code and paste it into cell A1 of the "Macro Code" tab of this workbook.

5. Go back to the Visual Basic, and alter your macro to do the following:       

a. Make the Output sheet as the Active sheet     

b. Ask the user for their first name and supply the information to cell A1.     

c. Ask the user for their last name and supply the information to cell B1.    

d. Leave the code that concatenates A1 and B1     

e. Ask the user if they want to input another name.     

g. If the user says "Yes", it needs to add the supplied first and last name to the next A and B cells in the column, then concatenate the names in C (A2, B2 and C2, A3, B3 and C3, etc.)          

Hint: GoTo    

i. If the user says "No", then exit the macro.

6. Copy your altered macro code and paste it into cell A1 of the "Altered Macro Code" tab of this workbook.

Answer 1

Macro_1

Sub CIS308Exam3()

Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = "=RC[-2]&"" ""&RC[-1]"

End Sub

Macro_2

Sub CIS308Exam3_2()

Dim FirstName As String

Dim SecondName As String

Dim counter As String

Dim i As Integer

FirstName = InputBox("Enter first name ", "Enter Value")

SecondName = InputBox("Enter second name ", "Enter Value")

Range("A1").Select

ActiveCell.FormulaR1C1 = FirstName

Range("B1").Select

ActiveCell.FormulaR1C1 = SecondName

Range("C1").Select

ActiveCell.FormulaR1C1 = "=RC[-2]&"" ""&RC[-1]"

counter = InputBox("Do you want to add another name", "Enter Value")

i = 1

Do While counter = "Yes"

i = i + 1

FirstName = InputBox("Enter first name ", "Enter Value")

SecondName = InputBox("Enter second name ", "Enter Value")

Cells(i, 1).Value = FirstName

Cells(i, 2).Value = SecondName

Range("C1").Select

Selection.Copy

Cells(i, 3).Select

ActiveSheet.Paste

counter = InputBox("Do you want to add another name", "Enter Value")

Loop

End Sub

Snapshot of the code:

Sample output

Let me know if anything is not clear.