Use VLookUp table to set variable to equal a value-CodePudding

I have created a table on a worksheet within my workbook titled LookUpTable where it has data that looks like this

What I want to do is reference that table to set variables to equal the table cell values within my if statement. Here is a portion of my code.

 If sweeprate_value = 50 Then
    sweep_value = 49.8
    sweep_value_max = 50.2
    Call updateSD(sysnum, sweeprate_rowindex_1, specmin, sweep_value)
    Call updateSD(sysnum, sweeprate_rowindex_1, specmax, sweep_value_max)
    Call updateSD(sysnum, sweeprate_rowindex, spectyp, sweeprate_value)
    Call updateSD(sysnum, sweeprate_rowindex_1, spectyp, sweeprate_value)
ElseIf sweeprate_value = 100 Then
    sweep_value = 99.8
    sweep_value_max = 100.2
    Call updateSD(sysnum, sweeprate_rowindex_1, specmin, sweep_value)
    Call updateSD(sysnum, sweeprate_rowindex_1, specmax, sweep_value_max)
    Call updateSD(sysnum, sweeprate_rowindex, spectyp, sweeprate_value)
    Call updateSD(sysnum, sweeprate_rowindex_1, spectyp, sweeprate_value)
Else: sweeprate_value = 200
    sweep_value = 199.4
    sweep_value_max = 200.4
    Call updateSD(sysnum, sweeprate_rowindex_1, specmin, sweep_value)
    Call updateSD(sysnum, sweeprate_rowindex_1, specmax, sweep_value_max)
    Call updateSD(sysnum, sweeprate_rowindex, spectyp, sweeprate_value)
    Call updateSD(sysnum, sweeprate_rowindex_1, spectyp, sweeprate_value)
End If

Where I have the lines sweep_value = 49.8 sweep_value_max = 50.2 currently when I debug they both equal 50 which is not what I would like. For this case since sweeprate_value = 50 then sweep_value should = 49.8 and sweep_value_max should = 50.2 as shown in the VLookUp table. Then follow the same flow for the other values. I do not want the values to be hardcoded into the script but rather reference the table to which those values could be changed if needed.

CodePudding user response：

Either you set the values hardcoded like this. Make sure you have an error on non supported sweep rates. Do not repeat code.

Dim sweeprate_value As Long
Dim sweep_value As Double
Dim sweep_value_max As Double

If sweeprate_value = 50 Then
    sweep_value = 49.8
    sweep_value_max = 50.2
ElseIf sweeprate_value = 100 Then
    sweep_value = 99.8
    sweep_value_max = 100.2
ElseIf sweeprate_value = 200 Then
    sweep_value = 199.4
    sweep_value_max = 200.4
Else
    MsgBox "A SweepRate Value of """ & sweeprate_value & """ is not supported.", vbCritical   vbOKonly
    Exit Sub
End If

Call updateSD(sysnum, sweeprate_rowindex_1, specmin, sweep_value)
Call updateSD(sysnum, sweeprate_rowindex_1, specmax, sweep_value_max)
Call updateSD(sysnum, sweeprate_rowindex, spectyp, sweeprate_value)
Call updateSD(sysnum, sweeprate_rowindex_1, spectyp, sweeprate_value)

Or you use a lookup:

Dim sweeprate_value As Long
' don't know where your sweeprate_value comes from so you get it here

Dim LookUpTable As Worksheet
Set LookUpTable = ThisWorkbook.Worksheets("LookUpTable")

Dim FoundAt As Double  ' get the row number of the `sweeprate_value` in the  LookUpTable
FoundAt = 0  ' initialize (needed if this is used in a loop!)
On Error Resume Next  ' next line will error if that `sweeprate_value` is not in the LookUpTable. Catch that error and hide it.
FoundAt = Application.WorksheetFunction.Match(sweeprate_value, LookUpTable.Range("A:A"), 0)
On Error GoTo 0  ' re-activate error reporting!

' if `sweeprate_value` is not in the LookUpTable stop
If FoundAt = 0 Then
    MsgBox "A SweepRate Value of """ & sweeprate_value & """ is not supported.", vbCritical   vbOKOnly
    Exit Sub
End If

Dim sweep_value As Double
sweep_value = LookUpTable.Cells(FoundAt, "B").Value

Dim sweep_value_max As Double
sweep_value_max = LookUpTable.Cells(FoundAt, "C").Value

Call updateSD(sysnum, sweeprate_rowindex_1, specmin, sweep_value)
Call updateSD(sysnum, sweeprate_rowindex_1, specmax, sweep_value_max)
Call updateSD(sysnum, sweeprate_rowindex, spectyp, sweeprate_value)
Call updateSD(sysnum, sweeprate_rowindex_1, spectyp, sweeprate_value)