Aino
10-13-2014, 06:29 AM
Hi all,
I would like to know if I have corrected my problem with the HLOOKUP and VLOOKUP functions. Here's the original code:
'1)
‘Range of the table:
Set R3 = Sheets("Tables").Range("C28:I39")
‘HVLdown is HVL rounded down with 0.05 resolution:
HVLdown = Application.WorksheetFunction.RoundDown(2 * HVL, 1) / 2
‘Finding the HVLdown from the table:
Application.HLookup(HVLdown, R3, 2)
This didn't always work (most of the time it did, though), apparently because HVLdown was not an exact number and sometimes the HLOOKUP function returned an index that was one index smaller than the index of HVLdown. Here's how the code is now:
'2)
‘Range of the table:
Set R3 = Sheets("Tables").Range("C28:I39")
‘HVLdown is HVL rounded down with 0.05 resolution:
HVLdown = Application.WorksheetFunction.RoundDown(2 * HVL, 1) / 2
'I'm hoping this is exact enough:
HVLdown = Application.WorksheetFunction.Round(HVLdown, 2)
‘Finding the HVLdown from the table:
Application.HLookup(HVLdown, R3, 2)
The code seems to work now, but since I can't check every HVL number, I'm not sure. Will this correct the problem? Here is my third version, that I'm thinking of implementing:
'3)
‘Range of the table:
Set R3 = Sheets("Tables").Range("C28:I39")
‘HVLdown is HVL rounded down with 0.05 resolution:
HVLdown = Application.WorksheetFunction.RoundDown(2 * HVL, 1) / 2
'Increasing HVLdown by an epsilon:
HVLdown = HVLdown + 0.0001
‘Finding the HVLdown from the table:
Application.HLookup(HVLdown, R3, 2)
Since the HLOOKUP finds the biggest number that is smaller than HVLdown, should this work for sure? Is this better than the code number 2)? This code is actually quite important to many people and it needs to be robust.
Thank you in advance!
-Aino
I would like to know if I have corrected my problem with the HLOOKUP and VLOOKUP functions. Here's the original code:
'1)
‘Range of the table:
Set R3 = Sheets("Tables").Range("C28:I39")
‘HVLdown is HVL rounded down with 0.05 resolution:
HVLdown = Application.WorksheetFunction.RoundDown(2 * HVL, 1) / 2
‘Finding the HVLdown from the table:
Application.HLookup(HVLdown, R3, 2)
This didn't always work (most of the time it did, though), apparently because HVLdown was not an exact number and sometimes the HLOOKUP function returned an index that was one index smaller than the index of HVLdown. Here's how the code is now:
'2)
‘Range of the table:
Set R3 = Sheets("Tables").Range("C28:I39")
‘HVLdown is HVL rounded down with 0.05 resolution:
HVLdown = Application.WorksheetFunction.RoundDown(2 * HVL, 1) / 2
'I'm hoping this is exact enough:
HVLdown = Application.WorksheetFunction.Round(HVLdown, 2)
‘Finding the HVLdown from the table:
Application.HLookup(HVLdown, R3, 2)
The code seems to work now, but since I can't check every HVL number, I'm not sure. Will this correct the problem? Here is my third version, that I'm thinking of implementing:
'3)
‘Range of the table:
Set R3 = Sheets("Tables").Range("C28:I39")
‘HVLdown is HVL rounded down with 0.05 resolution:
HVLdown = Application.WorksheetFunction.RoundDown(2 * HVL, 1) / 2
'Increasing HVLdown by an epsilon:
HVLdown = HVLdown + 0.0001
‘Finding the HVLdown from the table:
Application.HLookup(HVLdown, R3, 2)
Since the HLOOKUP finds the biggest number that is smaller than HVLdown, should this work for sure? Is this better than the code number 2)? This code is actually quite important to many people and it needs to be robust.
Thank you in advance!
-Aino