Loop through a range of cells with an if condition

TiNC701

New Member
Joined
May 10, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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


KPI.xlsm
ABCDEFGHIJKLM
5McdJan 2020Feb 2020Mar 2020Apr 2020May 2020Jun 2020Jul 2020Aug 2020Sep 2020Oct 2020Nov 2020Dec 2020
6Deliveries153620000650
7Complaints101010011000
8Monthly %0.00%100.00%66.67%100.00%50.00%0.00%0.00%-100.00%-100.00%100.00%100.00%0.00%
9YTD %
Sheet1
Cell Formulas
RangeFormula
C8:M8C8=IFERROR((C$6-C$7)/C$6,(C$6-C$7)/1)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G8Cell Valuecontains "No Data"textNO
B8:M8Cell Value=0textNO
B8:M8Cell Value<0.79textNO
B8:M8Cell Value>0.8textNO
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,406
Office Version
  1. 365
Platform
  1. Windows
Why not just change your formula to
=IF(AND(C$6=0,C$7=0),"No Data",IFERROR((C$6-C$7)/C$6,(C$6-C$7)/1))
 

TiNC701

New Member
Joined
May 10, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Why not just change your formula to
=IF(AND(C$6=0,C$7=0),"No Data",IFERROR((C$6-C$7)/C$6,(C$6-C$7)/1))
I was clearly trying to make it more difficult than it was! Thank you so much
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,406
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,473
Messages
5,548,246
Members
410,824
Latest member
Bobmn4
Top