Consulting

Results 1 to 10 of 10

Thread: Select Current Row when a Condition is True in a For Each Loop

  1. #1
    VBAX Regular
    Joined
    Feb 2018
    Location
    Portland
    Posts
    13
    Location

    Select Current Row when a Condition is True in a For Each Loop

    Hello Everyone,

    I am trying to format a row in my database when a condition evaluates as true in a For Each Loop for a range. I am close but cannot figure out how to select from the current cell in the loop to the first column in the database. Once I figure that out, I will write some code to format with the appropriate color.

    The two parameters being passed to the procedure come from the values contained in two ranges of the same datatype and size. My code is:

    Public Sub UpdateTargetData(X As String, Y As String)
        Dim Cell As Range
        Dim CellAddress As String
        For Each Cell In Range("GeneratedColorCodes")
            If Cell.Value = Y Then
                Cell.Value = X
                CellAddress = Cell.Address
                Range("CellAddress").End(xlToLeft).Select
                'Perform some formatting
            End If
        Next Cell
    End Sub
    When I debug, I get error that the Range("CellAddress").Select cannot be evaluated. However, CellAddress has a value of $K$266 when I hover my mouse over the variable after setting a breakpoint.

    So why doesn't Range("CellAddress").Select work whereas typing in Range("K266").Select in the Immedate window does? Thanks

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,645
    Location
    Try it without the quotes around CellAddress

    The variable CellAddress is a string containing "K266", but by putting quotes around it you make it a string literal

    BTW, it's usually not necessary to .Select something to work on it
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Regular
    Joined
    Feb 2018
    Location
    Portland
    Posts
    13
    Location
    Hello Paul, that did it. My revised code is:

    Public Sub UpdateTargetData(X As String, Y As String)
        Dim Cell As Range
        Dim CellAddress As String
        For Each Cell In Range("GeneratedColorCodes")
            If Cell.Value = Y Then
                Cell.Value = X
                CellAddress = Cell.Address
                Range(CellAddress, Range(CellAddress).End(xlToLeft)).Select
                'Perform some formatting
            End If
        Next Cell
    End Sub
    However, I do have some blank columns in my database so will need the selection to extend to column A. I do have a named range for my column headings named "ColumnHeadings".

    How would I modify my code to not use a Select and work on the range from my defined CellAddress leftwards to column A? Thanks

  4. #4
    VBAX Expert
    Joined
    Apr 2005
    Posts
    756
    Location
    Before you posted Post#3, I wanted to ask you this.
    Are the Cell Values X and Y actual alphabetical capital letters? If so, they need to be like "Y" and "X"
    Is the Range "GeneratedColorCodes" in a single Column?
    You say "Select Current Row when a Condition is True in a For Each Loop" Are you sure you need 16,000 Cells (Excel 2007 and up)
    What kind of formatting do you want to perform? And for how many Columns (Assuming not all 16,000+ Cells)
    In you code you have "End(xlToLeft)". Do you here mean to go to Column A in the same Row? Or can it be any other Column?




    With Range(Cells(CellAddress.Row, 1), Cells(CellAddress.Row, CellAddress.Column))
    'Do what needs to be done here
    End With

  5. #5
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,645
    Location
    Not exactly following.

    Can you attach a small sample workbook?

    Realistic input data, and clearly identify where you want to go
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    VBAX Regular
    Joined
    Feb 2018
    Location
    Portland
    Posts
    13
    Location

    Excel code and Image are Attached

    Hello John and Paul

    Thanks for your replies. I have attached my workbook for reference and have included an image of my Excel application. When done, it will contain about 350 rows of data but have deleted most of these for simplicity.

    I have a color key in A40:A46. The user can change these to any color they want but need to click the button, "Update Color Codes", to generate the new Hex codes in the range B40:B46

    My code finds and replaces the hex codes in column K with the new values that are generated in the range B40:B46. Notice the range F40:F46. These are the hex codes before the button is clicked so my code can identify what has been changed. Notice I changed the last color to blue and the code automatically updated the hex code in B46 to #0070C0 and updated the codes in K31:K35 to this same value. However, the interior color of these rows is still orange.

    Now, to my original question in my first post. How do I select from the CellAddress variable that I have to column A? Once I do this, I willl run the hexa_color function against the selection to change the color to blue.

    Thank you very much for your assistance.

    Public Sub UpdateTargetData(X As String, Y As String)
        Dim Cell As Range
        Dim CellAddress As String
        For Each Cell In Range("GeneratedColorCodes")
            If Cell.Value = Y Then
                Cell.Value = X
                CellAddress = Cell.Address
                Range(CellAddress, Range(CellAddress).End(xlToLeft)).Select
                'Perform some formatting
            End If
        Next Cell
    End Sub



    Capture.JPG
    Attached Files Attached Files

  7. #7
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,645
    Location
    Still not 100% sure I understand, but maybe ....



    Option Explicit
    
    
    Public Sub UpdateTargetData(X As String, Y As String)
        Dim rCell As Range, rColA As Range
        For Each rCell In Range("GeneratedColorCodes").Cells
            If rCell.Value = Y Then
                rCell.Value = X
                Set rColA = rCell.EntireRow.Cells(1)
                
                MsgBox rColA.Address
                'do some formatting
                
                Exit For
            End If
        Next
    End Sub
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  8. #8
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    393
    Location
    Maybe...
    Range(CellAddress, Cells(Cell.Row, 1)).Select

  9. #9
    VBAX Regular
    Joined
    Feb 2018
    Location
    Portland
    Posts
    13
    Location

    Almost There, Loop Aborts Prematurely with No Error

    Hello Paul and 1976,

    I incorporated both of your suggestions into my updated code and can now select and apply the new color to the current row in the loop. However, the procedure aborts prematurely with no error and does not continue down to the next cell in the "For Each Loop".

    Here is my newest code:

    Public Sub UpdateTargetData(X As String, Y As String)
        Dim Cell As Range
        Dim SelectedRange As Range
        Dim CellAddress As String
        For Each Cell In Range("GeneratedColorCodes")
            If Cell.Value = Y Then
                Cell.Value = X
                CellAddress = Cell.Address
                Range(CellAddress, Cells(Cell.Row, 1)).Select
                Set SelectedRange = Selection
                'Perform some formatting
                SelectedRange.Interior.Color = hexa_color(Cell.Value)
                Range(CellAddress).Select
            End If
        Next Cell
    End Sub
    Here is what the worksheet looks like after the first iteration of the loop:

    Capture2.JPG

    Just so my desired result is a little more clear, the application allows the users to change colors that they have already applied to the database. In my trouble-shooting step, The orange color in A46 has been changed to Blue. When the user clicks the button "Update Color Codes", my code creates the Hexadecimal color code for newly applied blue in B46. The previously applied color code for the orange is in F46

    The code then finds all occurrences of #F4B084 (cells in orange) in a range I named "GeneratedColorCodes which is the column you see labeled "Cell Color" containing all the Hex codes for each row.

    Notice that the first cell in the loop was found, changed to the new hex code for blue and the row was formatted blue but the loop aborted without continuing the loop.

    Any ideas? Thanks.

  10. #10
    VBAX Regular
    Joined
    Feb 2018
    Location
    Portland
    Posts
    13
    Location
    Please disregard the post I just submitted. It was user error. It works perfectly.

    I did not reset the Hex Codes in K32:K35. They were for a color that was from my earlier debugging that was not orange so there were no orange codes to be found.

    Thanks for all of your help.

Posting Permissions

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