Hi, sorry I am new to VBA. I have a very basic worksheet which records deliveries received and complaints per month. I am using Vlookup to pull the data from another sheet and have set up my formula to calculate the percentage.
What I am trying to do is set up a loop that will check the data for each month and if delivery ("B6") = 0 and Complaints ("B7") = 0 then Monthly % ("B8") = "No Data". I have written the below which does this for one month but I would like to loop it so it does the same for each month Range ("B8:M8") I have tried Dim I as Integer and a Do While loop but cannot seem to get it to work. Could someone please offer a suggestion. I have attached a simplified version of my worksheet so you can see what I mean. Thank you in advance.
Public Sub MonthlyFormula()
Dim Deliveries As String
Dim Complaints As String
Dim Monthly As String
Deliveries = Range("B6").Value
Complaints = Range("B7").Value
Monthly = Range("B8").Value
If Deliveries = "0" And Complaints = "0" Then
Monthly = "No Data"
End If
ActiveCell.Value = Monthly
What I am trying to do is set up a loop that will check the data for each month and if delivery ("B6") = 0 and Complaints ("B7") = 0 then Monthly % ("B8") = "No Data". I have written the below which does this for one month but I would like to loop it so it does the same for each month Range ("B8:M8") I have tried Dim I as Integer and a Do While loop but cannot seem to get it to work. Could someone please offer a suggestion. I have attached a simplified version of my worksheet so you can see what I mean. Thank you in advance.
Public Sub MonthlyFormula()
Dim Deliveries As String
Dim Complaints As String
Dim Monthly As String
Deliveries = Range("B6").Value
Complaints = Range("B7").Value
Monthly = Range("B8").Value
If Deliveries = "0" And Complaints = "0" Then
Monthly = "No Data"
End If
ActiveCell.Value = Monthly
KPI.xlsm | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | |||
5 | Mcd | Jan 2020 | Feb 2020 | Mar 2020 | Apr 2020 | May 2020 | Jun 2020 | Jul 2020 | Aug 2020 | Sep 2020 | Oct 2020 | Nov 2020 | Dec 2020 | ||
6 | Deliveries | 1 | 5 | 3 | 6 | 2 | 0 | 0 | 0 | 0 | 6 | 5 | 0 | ||
7 | Complaints | 1 | 0 | 1 | 0 | 1 | 0 | 0 | 1 | 1 | 0 | 0 | 0 | ||
8 | Monthly % | 0.00% | 100.00% | 66.67% | 100.00% | 50.00% | 0.00% | 0.00% | -100.00% | -100.00% | 100.00% | 100.00% | 0.00% | ||
9 | YTD % | ||||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C8:M8 | C8 | =IFERROR((C$6-C$7)/C$6,(C$6-C$7)/1) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
G8 | Cell Value | contains "No Data" | text | NO |
B8:M8 | Cell Value | =0 | text | NO |
B8:M8 | Cell Value | <0.79 | text | NO |
B8:M8 | Cell Value | >0.8 | text | NO |