Need Help Matching a variable and defining as a range

egraham3

Board Regular
Joined
Jun 14, 2010
Messages
200
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.
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Something like this?

Code:
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

Board Regular
Joined
Jun 14, 2010
Messages
200
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.
 

egraham3

Board Regular
Joined
Jun 14, 2010
Messages
200

ADVERTISEMENT

numbers. probably off by some decimals
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Maybe something like this (change the rounding criteria to suit)

Code:
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

Board Regular
Joined
Jun 14, 2010
Messages
200

ADVERTISEMENT

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

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
No, here

Rich (BB code):
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

Board Regular
Joined
Jun 14, 2010
Messages
200
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

Legend
Joined
Jun 19, 2002
Messages
63,650
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,509
Messages
5,511,712
Members
408,862
Latest member
sidneybc

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top