# Tolerance Check with VBA Help

#### Drewgarry007

##### Board Regular
Hi,

I am trying to use the below code but I think formula is returning a zero value for some reason?

My aim is to multiply the 'myrow + 1' percentage number with the number in the vlookup table in the 'CHECKS' tab, then divide that number by the percentage number in 'mycol' and check if the value is within a tolerance.

Howerver the 'DiffCheck' value I get back is zero for some reason? can anyone tell me where I'm going wrong? and how to fix it? I checked this by replacing

Rich (BB code):
``````If DiffCheck > 101 Or DiffCheck < 99 Then
Cells(myrow, mycol).Interior.ColorIndex = 3
'with
If DiffCheck = 0 Then
Cells(myrow, mycol).Interior.ColorIndex = 3``````

The code I am having issues with is below, I guess its something to do with the part I highlighted in red??
Rich (BB code):
``````Dim mycol As Long
Dim myrow As Long, CheckRow As Long, LastRow As Long, DiffCheck As Long, LastCol As Long
CheckRow = 3
LastRow = ActiveSheet.Range("B650").End(xlUp).Row
LastCol = ActiveSheet.Range("IV14").End(xlToLeft).Column
For mycol = 3 To LastCol Step 1
If Cells(CheckRow, mycol).Value <> "" Then
For myrow = 13 To LastRow

DiffCheck = Cells(myrow, mycol + 1) * FormulaR1C1 = "=VLOOKUP(RC2,CHECKS!C1:C2,2,FALSE)*RC/R3C"

If DiffCheck > 101 Or DiffCheck < 99 Then
Cells(myrow, mycol).Interior.ColorIndex = 3

End If
Next myrow
End If
Next mycol``````

Any help would be appreciated!

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### ChrisM

##### Well-known Member
Change:

Cells(myrow, mycol + 1) * FormulaR1C1

to

Cells(myrow, mycol + 1).FormulaR1C1

See if that does it

#### Drewgarry007

##### Board Regular
Change:

Cells(myrow, mycol + 1) * FormulaR1C1

to

Cells(myrow, mycol + 1).FormulaR1C1

See if that does it

I still would like to multiply Cells(myrow, mycol + 1) by the vlookup number tho how would I achieve that?

#### Drewgarry007

##### Board Regular
Ok so I have re done the code but am getting a run time error with 'PerCol'

Rich (BB code):
``````Dim mycol As Long
Dim myrow As Long, LastRow As Long, DiffCheck As Long
Dim PerCol As Integer
Dim LookupCol As Long
Dim Sheet1, Sheet2 As Worksheet

Set Sheet1 = ActiveWorkbook.Sheets("FX")
Set Sheet2 = ActiveWorkbook.Sheets("CHECKS")
LastRow = ActiveSheet.Range("B650").End(xlUp).Row
LookupCol = Cells(13, LastRow)
PerCol = Application.WorksheetFunction.VLookup(Sheet1.Range("B", LookupCol), Sheet2.Range("A2:B200"), 2, False)

For mycol = 3 To LastCol Step 1
If Cells(3, mycol).Value <> "" Then
For myrow = 13 To LastRow
DiffCheck = Cells(myrow, mycol + 1) * PerCol / Cells(3, mycol)

If DiffCheck = 0 Then
'If DiffCheck >= 34 And DiffCheck <= 37 Then
Cells(myrow, mycol).Interior.ColorIndex = 3

End If
Next myrow
End If
Next mycol``````

if I take out 'PerCol' and replace it with the number * 100 instead then the formula works, can you see what I'm doing wrong here?

Last edited:

#### ChrisM

##### Well-known Member

I see several issues here:

1. You've dimmed Sheet1, Sheet2 as Worksheet. This is not the right way to dim two variables at once. You have to write it like this: Dim Sheet1 as Worksheet, Sheet2 as Worksheet

2. The sheet variable names you are using (Sheet1, Sheet2) are actually internal sheet code names, I'm not sure what behavior you're going to get by using these conflicting variable names. To be safe, personally, I would never name sheets that way. Look in the top left corner of your VBE window, see all your worksheets? See Sheet1, Sheet2, etc next to each of your named worksheets? Those are the internal names. You can actually refer to your sheets in code that way, as just Sheet1, etc.

3. You've used "Cells(13, LastRow)" without a sheet qualifier, so are you sure you are using the right sheet? You should really put a sheet name in front of this, like Sheet1.Cells(13, LastRow)

4. The Cells() property is Rows first, then columns, like Cells(row,column). You appear to be using it backwards. Seems like it should be Cells(LastRow, 13)

5. Since your Cells formula is probably wrong, you are probably assigning an invalid value to LookupCol, and that is causing your vlookup to fail

6. Since you've used Application.WorksheetFunction, if your vlookup fails you will get a run time error. You need to add error handling (On Error) to trap and test this. Personally, I like to leave out the WorksheetFunction bit, and just do Application.Vlookup. If you choose to go this route, you need to change your PerCol variable from Integer to Variant. Then, after the vlookup, you need to test if you have a valid value, like this:

Code:
``````If IsError(PerCol) = False Then
'match found, proceed

Else
'no vlookup match found, add appropriate code here

End If``````

#### Drewgarry007

##### Board Regular
Hi Chris,
I have re-done the bits you suggested but I'm still not sure its calculating correctly. I've stuck the red bit in to check the values and they're not returning the expected values. basically all the lookup values are in col "B" and the look up table is in a separate 'checks' sheet

Rich (BB code):
``````Dim mycol As Long
Dim myrow As Long, LastRow As Long
Dim PerCol As Variant, DiffCheck As Variant
Dim FXsheet As Worksheet, CheckSheet As Worksheet

Set FXsheet = ActiveWorkbook.Sheets("FX")
Set CheckSheet = ActiveWorkbook.Sheets("CHECKS")
LastRow = ActiveSheet.Range("B650").End(xlUp).Row
PerCol = Application.VLookup(FXsheet.Range("B" & LastRow), CheckSheet.Range("A2:B200"), 2, False)

For mycol = 3 To LastCol Step 1
If Cells(3, mycol).Value <> "" Then
For myrow = 13 To LastRow
DiffCheck = Cells(myrow, mycol + 1) * PerCol / Cells(3, mycol)
If DiffCheck <> 0 Then
'If DiffCheck >= 34 And DiffCheck <= 37 Then
'Cells(myrow, mycol).Interior.ColorIndex = 3
Cells(myrow, mycol).Value = DiffCheck

End If
Next myrow
End If
Next mycol``````

#### ChrisM

##### Well-known Member

There's an issue here: For mycol = 3 To LastCol Step 1

You haven't dimmed a LastCol variable, nor have you set it equal to anything, so it will default to zero.

Also, what is the purpose behind the PerCol value? Should that vlookup happen only once, or should it occur for each row in your loop? You have it going only once. You mention that "all the lookup values are in col B", but again, you are only ever taking the last one.

#### Drewgarry007

##### Board Regular
Sorry Chris the mycol was dimmed earlier in the code script, I've added it for your ref

Rich (BB code):
``````Dim mycol As Long
Dim myrow As Long, LastRow As Long, LastCol As Long
Dim PerCol As Variant, DiffCheck As Variant
Dim FXsheet As Worksheet, CheckSheet As Worksheet

Set FXsheet = ActiveWorkbook.Sheets("FX")
Set CheckSheet = ActiveWorkbook.Sheets("CHECKS")
LastCol = ActiveSheet.Range("IV14").End(xlToLeft).Column
LastRow = ActiveSheet.Range("B650").End(xlUp).Row
PerCol = Application.VLookup(FXsheet.Range("B" & LastRow), CheckSheet.Range("A2:B200"), 2, False)

For mycol = 3 To LastCol Step 1
If Cells(3, mycol).Value <> "" Then
For myrow = 13 To LastRow
DiffCheck = Cells(myrow, mycol + 1) * PerCol / Cells(3, mycol)
If DiffCheck <> 0 Then
'If DiffCheck >= 34 And DiffCheck <= 37 Then
'Cells(myrow, mycol).Interior.ColorIndex = 3
Cells(myrow, mycol).Value = DiffCheck

End If
Next myrow
End If
Next mycol``````

I want the PerCol vlookup to occur for each row in the loop. The lookup values are from row 13 to 'lastrow' (in col B)... (it should return different values) I think the vlookup value 'FXsheet.Range("B" & LastRow)' is the issue here but I don't know how to get it right.

#### ChrisM

##### Well-known Member
I'm not quite following your logic, but here's a stab at it. You need to move the vlookup inside the loop as you say:

Code:
``````Dim mycol As Long
Dim myrow As Long, LastRow As Long, LastCol As Long
Dim PerCol As Variant, DiffCheck As Variant
Dim FXsheet As Worksheet, CheckSheet As Worksheet

Set FXsheet = ActiveWorkbook.Sheets("FX")
Set CheckSheet = ActiveWorkbook.Sheets("CHECKS")
LastCol = ActiveSheet.Range("IV14").End(xlToLeft).Column
LastRow = ActiveSheet.Range("B650").End(xlUp).Row

For mycol = 3 To LastCol Step 1
If Cells(3, mycol).Value <> "" Then
For myrow = 13 To LastRow
PerCol = Application.VLookup(FXsheet.Range("B" & myRow), CheckSheet.Range("A2:B200"), 2, False)
DiffCheck = Cells(myrow, mycol + 1) * PerCol / Cells(3, mycol)
If DiffCheck <> 0 Then
'If DiffCheck >= 34 And DiffCheck <= 37 Then
'Cells(myrow, mycol).Interior.ColorIndex = 3
Cells(myrow, mycol).Value = DiffCheck

End If
Next myrow
End If
Next mycol``````

Replies
1
Views
69
Replies
10
Views
210
Replies
0
Views
54
Replies
5
Views
450
Replies
2
Views
204 Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,163,717
Messages
5,833,288
Members
430,202
Latest member
Faizal5zl ### 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.

### Which adblocker are you using?    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

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