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: Do While/For Loop to find specific cell contents

Discussion in 'Software Development' started by gmoukled, Nov 1, 2011.

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

    gmoukled Thread Starter

    Joined:
    Oct 17, 2011
    Messages:
    33
    I have an excel sheet which has some data sorted in 5 columns (A,B,C,D,E)

    Column D contains a machine name: either COLDTEST1 or COLDTEST2
    Column E contains a number

    What I want to do is to select the numbers in column E according to the machine name in column D.
    So i basically need a command that looks for all the cells in column D that are called COLDTEST1 and select all there corresponding numbers in the cells in column E.

    Could someone help me with this please?

    I am thinking I would probably need a Do While or a For loop, but since i have limited knowledge in VBA I could not figure it out.

    Thank you
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Have you taken a look a VLOOKUP() I imagine that this is a 1 to 1 relation, if you found the name you have the number?

    Example: VLOOKUP(<value you want to look for>,<the range to look in>,<the column to llok in in the range>,TRUE or FALSE)
    If you check the help with the function you will see thta TRUE is looks like and FALSE means exact match
     
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    A function would look something like this:

    Code:
    Function GetAnswer(tVar As Variant) As Variant
    Dim rng As Range, lstRow As Long
    lstRow = Range("D" & Rows.Count).End(xlUp).Row
    For Each rng In Range("D1:D" & lstRow)
        If rng.Value = tVar Then
            GetAnswer = rng.Offset(0, 1)
            Exit Function
        End If
    Next rng
    End Function
    
    you can do this by putting it in a cell as formula or use it vba code

    in a cell H2=GetAnswer("D1")

    or in your vba code

    myvalue = GetAnswer(the value requested)

    Happy coding.
     
  4. gmoukled

    gmoukled Thread Starter

    Joined:
    Oct 17, 2011
    Messages:
    33
    Hello Keebellah, thanks for your help.
    I was actually able to get this code, it is something different than what you gave me in you replies.



    This is the code that I have so far:
    Code:
    Sub RunAll()
    
    
    Dim ImportFilePath As String, ExportFilePath As String
    Dim File2Import As String, File2Export As String
    ImportFilePath = "C:\Users\TZFTQF\Fall 2011 Coop term\Server Cycle Time\Raw Files"
    ExportFilePath = "C:\Users\TZFTQF\Fall 2011 Coop term\Server Cycle Time\Template"
    File2Import = "copy_2_server_cycle_time.csv"
    File2Export = "Template"
    Call Macro1(ImportFilePath, File2Import, ExportFilePath, File2Export)
    
    
    
     ActiveWorkbook.SaveAs Filename:= _
            "C:\Users\TZFTQF\Fall 2011 Coop term\Server Cycle Time\SpreadSheet.xlsx", _
            FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    End Sub
    
    Sub Macro1(ImportFilePath, File2Import, ExportFilePath, File2Export)
    '
    ' Macro1 Macro
    '
    
    '
        With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;C:\Users\TZFTQF\Fall 2011 Coop term\Server Cycle Time\Raw Files\copy_2_server_cycle_time.csv" _
            , Destination:=Range("$A$1"))
            .Name = "copy_2_server_cycle_time"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
        Range("A1:E19151").Select
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range( _
            "D1:D19151"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range( _
            "A1:A19151"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range( _
            "E1:E19151"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet1").Sort
            .SetRange Range("A1:E19151")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        Dim rngFind As Range
        Dim strValueToPick As String
        Dim rngPicked As Range
        Dim rngLook As Range
        Dim strFirstAddress As String
        
        Set rngLook = Selection
        strValueToPick = "COLDTEST1"
        With rngLook
            Set rngFind = .Find(strValueToPick, .Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
            If Not rngFind Is Nothing Then
                strFirstAddress = rngFind.Address
                Set rngPicked = rngFind
                Do
                    Set rngPicked = Union(rngPicked, rngFind)
                    Set rngFind = .FindNext(rngFind)
                Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
            End If
        End With
        
        If Not rngPicked Is Nothing Then
            rngPicked.Offset(0, 1).Select
            
         
            ActiveSheet.Shapes.AddChart.Select
            ActiveChart.ChartType = xlXYScatter
        End If
        
        
    End Sub
    
    this is working perfectly now, but i also need to do the graph for the COLDTEST2 numbers in the same code, how do i do that?

    I tried to repeat the same lines of code but that is not working.
     
  5. gmoukled

    gmoukled Thread Starter

    Joined:
    Oct 17, 2011
    Messages:
    33
    After End If i am repeating this:
    Code:
     Set rngLook = Selection
        strValueToPick = "[COLOR="Red"]COLDTEST2[/COLOR]"
        With rngLook
            Set rngFind = .Find(strValueToPick, .Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
            If Not rngFind Is Nothing Then
                strFirstAddress = rngFind.Address
                Set rngPicked = rngFind
                Do
                    Set rngPicked = Union(rngPicked, rngFind)
                    Set rngFind = .FindNext(rngFind)
                Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
            End If
        End With
        
        If Not rngPicked Is Nothing Then
            rngPicked.Offset(0, 1).Select
            
            
            ActiveSheet.Shapes.AddChart.Select
            ActiveChart.ChartType = xlXYScatter
        End If
    
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I can't picture this without a sample, so if it's possible...

    Included a clear explanation of what you need / expect.
    I only have one day for this since I'll be out of the counttry for about 2 1/2 weeks
     
  7. gmoukled

    gmoukled Thread Starter

    Joined:
    Oct 17, 2011
    Messages:
    33
    In column D i have many cells which contains either COLDTEST1 or COLDTEST2.

    In the code I have , I am selecting the numbers in column E corresponding to all COLDTEST1 's in column D, and make a chart with those numbers.

    What I would also like to do in the same code is to select the numbers corresponding to COLDTEST2 as well and make another chart with those numbers.
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    What kind of chart.
    X-axis : what values
    Y-axis: what values?
    Labels, you name it
     
  9. gmoukled

    gmoukled Thread Starter

    Joined:
    Oct 17, 2011
    Messages:
    33
    The chart is not a problem !
    my problem is how to select the numbers corresponding to COLDTEST2 after i finish generating the first chart for COLDTEST1

    I already got the chart for the first selection, Now how can i make another selection?
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Still again, how is the tabel for which you have the cahrt built?
    You just want a tabel or a new chart too?
    I have no idea how the dtat looks like, I'm not a seer I just do some vba coding
     
  11. gmoukled

    gmoukled Thread Starter

    Joined:
    Oct 17, 2011
    Messages:
    33
    I figured it out!

    Thanks anyway
     
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    :) You, see, you can do it! (y)
     
  13. 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/1024964

  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