Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to find the closest value
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to find the closest value
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 - gsnu200844 "Lucile" wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to find the closest value
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 -- Gary''s Student - gsnu200844 "Lucile" wrote: 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to find the closest value
In the Vlookup arguments, change "G" to 5
-- Jim Cone Portland, Oregon USA "Lucile" 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to find the closest value
Thank you! It works great!
"Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200844 "Lucile" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find number closest to 0 | Excel Discussion (Misc queries) | |||
Array Lookup to Find Closest Date and Next Closest Date | Excel Worksheet Functions | |||
Find the closest value | Excel Programming | |||
Find closest value | Excel Programming | |||
How do I find a column entry closest to a particular value | Excel Discussion (Misc queries) |