Need Help Matching a variable and defining as a range

[egraham3]

I'd like to have a variable. find it in column of data, and then define the value that is Offset(0,1) as a range.

i thought i had it, but couldnt quite get the code right. I need this for VBA.

 

[VoG]

Something like this?

Sub test()
Dim x As Variant, Found As Range
x = "This is the chosen row"
Set Found = Columns("F").Find(what:=x, LookIn:=xlValues, lookat:=xlWhole).Offset(1)
If Not Found Is Nothing Then MsgBox Found.Address
End Sub

 

[egraham3]

thank you VoG,
quick question, i know i didnt state this, but what if the value isn't exact, is there a way to find the closest value. sorry.

 

[VoG]

Are you looking for numbers or text?

 

[egraham3]

numbers. probably off by some decimals

 

[VoG]

Maybe something like this (change the rounding criteria to suit)

Sub test()
Dim x As Variant, Found As Range, LR As Long, i As Long
x = 1.234
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        If Round(.Value, 0) = Round(x, 0) Then
            Set Found = .Offset(1)
            Exit For
        End If
    End With
Next i
If Not Found Is Nothing Then MsgBox Found.Address
End Sub

 

[egraham3]

is x where you put how much you want to allow rounding?

 

[VoG]

No, here

Round(.Value, 0) = Round(x, 0) Then

0 rounds to 0 decimal places. If you wanted to round to 2 decimal places use 2.

 

[egraham3]

so, .value is the value in the column we found(so we dont change decimal)

and x is the number we want it to be, up to how ever many decimal points( and if the number is

 

[VoG]

x is the number being searched for. The code rounds both x and the value in column A to the same number of decimal places and tests for equality.