Question

write the code for a). to e) using VBA in excel

: . a) Find the fifth customer whose first name starts vith ·K", and show his/her customer ID (using Do while ǐoop) ' b) Redo the above exercise with (Do Loop Until) c) Find how many Matthew are in the 1ist (using any loop you 1ike) and the same time find how many custoners are in the list ' Take care of your stop condition customerCounter - matthewCounter MsgBox "We have " ‘ matthewCounter ‘ " Matthew in the į įst of ” & eustome rCounter " -custone rs,- d) Create an arra Put the nanes of "BiL"Bob", Tom, "Mike, "Jim" in the array Add a new employee "Hary" in the array above using the End Sub Formulas File Home Insert Draw Page Layout xr Adam First name Customer ID arron 14 Kudelich Catherine 24 Banerjee Therese Nicholas Anushka Customers Ready O Type here to search I insert Draw Page Layout Formulas Dota Review View Help File Home Adam Anna 1 Lee Johnathar Heejin Customers O Type here to search Home File Home Insert Draw Page Layout Formulas Data Review ViewHelp Adam Rupani ng 101 Stumbaugh Customers 0 Type here to search ' e fifth customer whose first name starts with 'K', and show his/her customer ID (using Do While loop) b) Redo the above exercise with (Do Loop Unti1) c) Find how many Matthew are in the list (using any loop you like and the same time find how many customers are in the list ' Take care of your stop condition customerCounter0 matthewCounter0 MsgBox "We have matthewCounter &Matthew in the list of "&customerCounter&"customers." ' d) Create an array of 5 employees with the dynamic indexing method Put the names of "Bi11", "Bob", "Tom", "Mike", Jim in the array ' e) Add a new employee "Mary" in the array above using the preserve met End Sub

Answer 1

'a.
Sub MAC1()
Dim i As Integer
Dim c As Integer
c = 0
i = 1

Book1 Microsoft Excel HomeInsertPage LayoutFormulas Data Review View Developer r Properties ヤView Code ModeRun Dialog Map Properties Expansion Packs Refresh Data Import Use Relative References Export Visual Macros Insert Design Code Controls A18 1 Last Name Customer ID 4 BAJPAI 100% ; 3:37 AM 3/30/2019

While i <= 24
s = Cells(i, 2)
s1 = Left(s, 1)
If s1 = "K" Then
c = c + 1
If c = 5 Then

MsgBox (s)
MsgBox (Cells(i, 3))
End If
End If

i = i + 1

Wend

End Sub
'b.

Sub MAC1()
Dim i As Integer
Dim c As Integer
c = 0
i = 1

Do
s = Cells(i, 2)
s1 = Left(s, 1)
If s1 = "K" Then
c = c + 1
If c = 5 Then

MsgBox (s)
MsgBox (Cells(i, 3))
End If
End If

i = i + 1

Loop Until i >= 24

End Sub
'c.

Sub MAC1()
Dim customercounter As Integer
Dim matthewcounter As Integer
Dim i As Integer

customercounter = 0
matthewcounter = 0
i = 1
While i <= 24
s = Cells(i, 2)
customercounter = customercounter + 1
If s = "Matthew" Then
matthewcounter = matthewcounter + 1

End If

i = i + 1

Wend

MsgBox ("we have " & matthewcounter & " Matthews in the list of " & customercounter & " customer")

End Sub
'd.
Sub mac2()
Dim myArray() As String
ReDim myArray(4)
myArray(0) = "bill"
myArray(1) = "bob"
myArray(2) = "tom"
myArray(3) = "mike"
myArray(4) = "Jim"
ReDim Preserve myArray(5)
myArray(5) = "Marry"
For i = 0 To 5
MsgBox (myArray(i))
Next

End Sub