Loop through a range of cells with an if condition

TiNC701

New Member
Joined
May 10, 2020
Messages
7
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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))
 
Upvote 0
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
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top