Tolerance Check with VBA Help

[Drewgarry007]

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

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??

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[1]/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!

 

[ChrisM]

Change:

Cells(myrow, mycol + 1) * FormulaR1C1

to

Cells(myrow, mycol + 1).FormulaR1C1

See if that does it

 

[Drewgarry007]

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]

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

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?

 

[ChrisM]

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:

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

Else
  'no vlookup match found, add appropriate code here

End If

 

[Drewgarry007]

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

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]

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]

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

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.

Your help is much appreciated

 

[ChrisM]

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:

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