1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Lookup specific values

Discussion in 'Business Applications' started by znekcihc, Mar 15, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. znekcihc

    znekcihc Thread Starter

    Joined:
    Mar 8, 2012
    Messages:
    13
    Ive attached a sample file; I would like to retrieve the values of the highlighted cells and know which Run # they were from. Also, The O2% column is not always permanent it is random. Whats the easiest way of going about this? I appreciate all the help in advanced. Thanks
     

    Attached Files:

  2. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,223
    What do you mean by "highlighted cells"?
    Someone will highlight those cells manually in one process, and you need to retrieve the highlighted data in a separate process? Or you need a code which, in itself, is able to recognize the values that should be highlighted and also retrieve them?

    What are these data, by the way?
     
  3. znekcihc

    znekcihc Thread Starter

    Joined:
    Mar 8, 2012
    Messages:
    13
    I highlight the cells manually and need some programming that can pull the highlighted data for me.

    This is pollution data.
     
  4. DoubleHelix

    DoubleHelix Banned

    Joined:
    Dec 9, 2004
    Messages:
    24,388
    Pull data from where?
     
  5. znekcihc

    znekcihc Thread Starter

    Joined:
    Mar 8, 2012
    Messages:
    13
    Pull data from the highlighted cells from the Sample spreadsheet
     
  6. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,223
    Code:
    Sub PullData()
        Dim ws As Worksheet, Rng As Range, c As Range, rHeader As Range
        
        Set ws = ActiveSheet
        Set Rng = ws.Range("D1", ws.Range("D" & ws.Rows.Count).End(xlUp))
        For Each c In Rng.Cells
            If c.MergeArea.Address <> c.Address Then
                Set rHeader = c.MergeArea
            Else
                If (c.Interior.ColorIndex <> xlNone) And IsNumeric(c.Value) Then
                    MsgBox c.Value & " - " & rHeader.Cells(1).Value
                End If
            End If
        Next
    End Sub
     
  7. znekcihc

    znekcihc Thread Starter

    Joined:
    Mar 8, 2012
    Messages:
    13
    Jimmy: Sometimes the O2 % is not always in Column D and it can be random all the way from Column C to Column K.
     
  8. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,223
    Code:
    Sub PullData()
        Dim ws As Worksheet, Rng As Range, c As Range, rHeader As Range
        
        Set ws = ActiveSheet
        Set c = ws.Cells.Find(what:="O2 %", LookIn:=xlValues, lookat:=xlWhole)
        Set Rng = ws.Range("A1", ws.Range("A" & ws.Rows.Count).End(xlUp))
        Set Rng = Intersect(Rng.EntireRow, c.EntireColumn)
        
        For Each c In Rng.Cells
            If c.MergeArea.Address <> c.Address Then
                Set rHeader = c.MergeArea
            Else
                If (c.Interior.ColorIndex <> xlNone) And IsNumeric(c.Value) Then
                    MsgBox c.Value & " - " & rHeader.Cells(1).Value
                End If
            End If
        Next
    End Sub
     
  9. znekcihc

    znekcihc Thread Starter

    Joined:
    Mar 8, 2012
    Messages:
    13
    Thats pretty cool Jimmy. Thanks.
     
  10. znekcihc

    znekcihc Thread Starter

    Joined:
    Mar 8, 2012
    Messages:
    13
    Jimmy, Is there a way to put those values in specific cells instead of a popup window?
     
  11. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,223
    Of course there is a way.
     
  12. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,223
    But I can't say any more because I can't read your mind.
     
  13. znekcihc

    znekcihc Thread Starter

    Joined:
    Mar 8, 2012
    Messages:
    13
    My ultimate goal is to have something that would pull all the highlighted data from this Sample2 spreadsheet and put it in the corresponding boxes. Seems a bit hard though?
     

    Attached Files:

  14. znekcihc

    znekcihc Thread Starter

    Joined:
    Mar 8, 2012
    Messages:
    13
    Right now I am using:

    =IF(iscolor(INDEX($A:$M,MATCH("1 - F - INT NOX ZERO",M:M,0),MATCH("NOx ppm",$A$8:$M$8,0))),INDEX($A:$M,MATCH("1 - F - INT NOX ZERO",M:M,0),MATCH("NOx ppm",$A$8:$M$8,0)),IF(iscolor(INDEX($A:$M,MATCH("1 - F - INT NOX ZERO",M:M,0)-1,MATCH("NOx ppm",$A$8:$M$8,0))),INDEX($A:$M,MATCH("1 - F - INT NOX ZERO",M:M,0)-1,MATCH("NOx ppm",$A$8:$M$8,0)),""))

    This will find Run #1 - Final - INT NOX ZERO value. I have to use this for each value that I want to find. I am just assuming it would be easier to do this all in VBA?
     
  15. znekcihc

    znekcihc Thread Starter

    Joined:
    Mar 8, 2012
    Messages:
    13
    Also, in the column to the right of Text Labels aka "INT NOX ZERO" i have this:

    =IF(LEFT(A8,5)="RUN #",MID(A8,6,5),IF(LEFT(A8,5)="AVERA",LEFT(M7,1)&"A",IF(MID(M7,2,1)="A","",IF(LEFT(A8,3)="STA",MID(A8,12,1),IF(ISBLANK(L8),LEFT(M7,5),LEFT(M7,5)&" - "&L8)))))

    This is what will signify What run and if its Initials or Final Calibrations. It gives it the 1 - F or 2 - I.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1045316

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice