I'm a newbie struggling to figure out an excel macros code. Any help would be appreciated.
I have attached a simple file to what I'm trying to do.
File for excel forum.xlsx
Column E: I have Company names (Grant's Inc., Bills Market, Sarah Bakery, Pete Diner, etc.)
Column I : Will be a "Yes" or "No" answer (already entered)
Column BD: Needs to have a number entered "0" or "1" depending on a reply box.
Column BE: Needs to have a number entered "2" or "0" depending on a reply box.
Column BF: Needs to have a number entered "0" or "1" depending on a reply box.
*All data starts at row 8.
I basically want to make some loops to add scores to the 3 columns BD,BE,BF.
Which give Grant's Inc. a score in cell BD8.
Next, give Bill's Market a score in cell BD9.
Next, give Sarah Bakery a score in cell BD10.
Next, give Pete Diner a score in cell BD11.
etc. until column I shows a "No" answer for a company.
After Column I shows a "No" answer, I will end that loop and go back up to row 8 with Grant's Inc. and start giving scores in BE8 depending on a reply box.
(but I will only give scores in BE if there is a "1" value in BD.
I will continue a loop to score all Companies in Column BE if they have a "1" score in Column BD.
After I have no more scores of "1" in BD, then I will end the loop and go back up to row 8 with Grant's Inc. and start giving scores in BF8 depending on a reply box.
...and so on...
Here's my bad attempt at a code so far:
'--(This reply box starts the questioning process.) Yes I want help, then ask if they know the companies.
If iReply = vbYes Then
iReply2 = MsgBox(Prompt:="Do you know of this company: [ " & E8 & " ]?", _
Buttons:=vbYesNo, Title:="Do you know this company?")
End If
If iReply2 = vbYes Then
BD8 = "1"
End If
If iReply2 = vbNo Then
BD8 = "0"
End If
'----Ask KnowCompany questions for E8,E9,E10...until you come to a row with a "No" in column I.
Do Until Column I = "No"
iReply2 = MsgBox(Prompt:="Do you know of this company: [ " & E9 & " ]?", _
Buttons:=vbYesNo, Title:="Do you know this company?")
If iReply2 = vbYes Then
BD9 = "1"
End If
If iReply2 = vbNo Then
BD9 = "0"
End If
Loop
'Now start a new loop of questioning to give scores in BE8,BE9,BE10,BE11...etc.
iReply3 = MsgBox(Prompt:="Does [ " & E8 & " ] offer a unique and dominating product or service with no close competition within its industry? (Ex: CocaCola, John Deere)", _
Buttons:=vbYesNo, Title:="Dominating Product or Service?")
If iReply3 = vbYes Then
Range("BE8") = "2"
End IF
If iReply3 = vbNo Then
Range("BE8") = "0"
End IF
'continue scoring BE9,BE10,BE11,BE12,...etc until the current row has a "No" value in Column I.
end loop
'Now start a new loop of questioning to give scores in BF8,BF9,BF10,BF11..etc.
iReply4 = MsgBox(Prompt:="Does [ " & E8 & " ] offer the lowest prices for products or services among its competitors? (Ex: Walmart, Ross Stores Inc.)", _
Buttons:=vbYesNo, Title:="Lowest Prices?")
If iReply4 = vbYes Then
Range("BF8") = "1"
End If
If iReply4 = vbNo Then
Range("BF8") = "0"
End IF
'continue scoring BF9,BF10,BF11,BF12,...etc until the current row has a "No" value in Column I.
end loop
'Turn on Automatic Calculation
Application.Calculation = xlCalculationAutomatic
End Sub
Bookmarks