Drewgarry007
Board Regular
- Joined
- Oct 1, 2011
- Messages
- 142
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
The code I am having issues with is below, I guess its something to do with the part I highlighted in red??
Any help would be appreciated!
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[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!