Results 1 to 3 of 3

Thread: using vba or excel find the first reference match
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default using vba or excel find the first reference match

    hi guys,
    i would like to ask how can i resolve this.....

    Below is my actual data.

    date time temp
    1 10/7/2019 0:12 120.3
    2 10/7/2019 0:13 119.5
    3 10/7/2019 0:13 121.3
    4 10/7/2019 0:13 120
    5 10/7/2019 0:13 121.2
    6 10/7/2019 0:13 120
    7 10/7/2019 0:13 121.1
    8 10/7/2019 0:14 120.6
    i have a cutoff or reference value in j3,( eg.. 121). once it happens, then i need k3 to be 10/7/2019 0:13(3 10/7/2019 0:13 121.3)
    how can this happen...can somebody help me on this..
    i used lookup....LOOKUP(J3, C8:C1000, B8:B1000)
    but it showing last cell value that is cell 7.
    also lookup value function is not working when i change the data ....

    i want to find the date time(column 1) of reference value given in j3 (temp, column 2) either in excel or vba excel

  2. #2
    Board Regular
    Join Date
    Oct 2011
    Location
    Pompano Beach, FL
    Posts
    174
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: using vba or excel find the first reference match

    Copy this formula in K3

    =TEXT(INDIRECT("B"&MATCH(TRUE,C1:C8>J3,0)),"mm/d/yyy") & " " & TEXT(INDIRECT("C"&MATCH(TRUE,C1:C8>J3,0)),"h:m")

    It is an array formula so you will need to enter it by selecting CTL SHFT ENTER.

    You will also have to adjust the C1:C8 range in the formula to fit your data
    Last edited by Crystalyzer; Oct 23rd, 2019 at 12:43 AM.
    Using Windows 10, Excel 365. Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel.
    To open the VB editor, press Alt + F11
    To run code from the Excel window, press Alt + F8, double click macro name

  3. #3
    New Member
    Join Date
    Dec 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: using vba or excel find the first reference match

    thanks for ur reply....

    i tried below vba code....it is giving me the search value in column c....
    how can i get the corresponding value in column b

    Private Sub CommandButton4_Click()
    Dim b As Long
    With Worksheets(1).Range("c8:c5000")
    Set c = .Find(b, LookIn:=xlValues)
    If Not c Is Nothing Then
    'firstAddress = c.Address
    firstvalue = c.Value
    Range("o2").Value = c.Value
    'MsgBox c.Address
    'Set c = .FindNext(c)
    'Loop While Not c Is Nothing
    End If
    End With
    end sub

    once i run the code output is: 120.3
    $c$87(in msgbox)

    but i want $b$87 value......
    looking fwd to hearing from u soon....

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •