Solved: Do While/For Loop to find specific cell contents

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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.
 

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.
 

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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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
 

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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
What kind of chart.
X-axis : what values
Y-axis: what values?
Labels, you name it
 

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?
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top