macro to find the closest value

[Lucile]

Hi all,
I am writing a program with vba and I need your help!
I need to find the closest value to a value enter by the user.
I have load values in column C, the user enter a value for the load. I need
to find the closest value in columns C and the corresponding value of voltage
that is in column G.
I need to do that in a macro, I tried vlookup but it doesn't work.. give me
type mismatch.
Here is the piece of code...

tabledata = Range(Worksheets("data").Cells(1, "C"),
Worksheets("data").Cells(lastrow, "G"))
finfat = Application.VLookup(finalprefat, tabledata, "G", True)

finalprefat is enter by the user....

Thanks for your help!

 

[Gary''s Student]

Sub WhereIsIt()
Dim r As Range
Set r = Range("C1:C100")
IFind = 1
irow = 1
ig = Range("G1")
finalprefat = Application.InputBox(prompt:="enter value", Type:=1)
minny = Abs(ig - finalprefat)
For i = 2 To 100
temp = Abs(Cells(i, "C").Value - finalprefat)
If temp < minny Then
minny = temp
irow = i
ig = Cells(i, "G").Value
End If
Next
MsgBox (Cells(irow, "C").Value & Chr(10) & irow & Chr(10) & Cells(irow,
"G").Value)
End Sub

This looks from C1 thru C100 and reports the closest value, which row, and
the value of the cell in column G.

 

[Gary''s Student]

Here is a correction:

Sub WhereIsIt()
Dim r As Range
Set r = Range("C1:C100")
irow = 1
finalprefat = Application.InputBox(prompt:="enter value", Type:=1)
minny = Abs(Range("C1").Value - finalprefat)
For i = 2 To 100
temp = Abs(Cells(i, "C").Value - finalprefat)
If temp < minny Then
minny = temp
irow = i
End If
Next
MsgBox (Cells(irow, "C").Value & Chr(10) & irow & Chr(10) & Cells(irow,
"G").Value)
End Sub

 

[Jim Cone]

In the Vlookup arguments, change "G" to 5
--
Jim Cone
Portland, Oregon USA



"Lucile"
<[email protected]>
wrote in message
Hi all,
I am writing a program with vba and I need your help!
I need to find the closest value to a value enter by the user.
I have load values in column C, the user enter a value for the load. I need
to find the closest value in columns C and the corresponding value of voltage
that is in column G.
I need to do that in a macro, I tried vlookup but it doesn't work.. give me
type mismatch.
Here is the piece of code...

tabledata = Range(Worksheets("data").Cells(1, "C"),
Worksheets("data").Cells(lastrow, "G"))
finfat = Application.VLookup(finalprefat, tabledata, "G", True)

finalprefat is enter by the user....

Thanks for your help!

 

[Lucile]

Thank you! It works great!

Here is a correction:

Sub WhereIsIt()
Dim r As Range
Set r = Range("C1:C100")
irow = 1
finalprefat = Application.InputBox(prompt:="enter value", Type:=1)
minny = Abs(Range("C1").Value - finalprefat)
For i = 2 To 100
temp = Abs(Cells(i, "C").Value - finalprefat)
If temp < minny Then
minny = temp
irow = i
End If
Next
MsgBox (Cells(irow, "C").Value & Chr(10) & irow & Chr(10) & Cells(irow,
"G").Value)
End Sub