For Each seems stuck on spot

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • I'm trying to modify a bit of code I have to cycle through a column and if the cell is empty, pick a value along the row. - The modification is to allow for the fact that in this case it's not a fixed column but driven by the ActiveCell.


    I have two issues. I have been playing around with defining the range. What I have now doesn't throw up an error but it doesn't seem to work either because the result is the same many times over.

    • Issue 1 seems to be that the For Each function doesn't cycle through the range.
    • Issue 2 is that the value it gives me is one it shouldn't, i.e. the cell isn't empty.

    It's a very short bit of code and I would have thought fairly simply - so two issues is quite impressive!


    Thanks!


    Code
    Dim myRange As Range
    Dim myEmail As String
    For Each myRange In sh2.Range(ActiveCell, Cells(ActiveCell.Row + 20, ActiveCell.Column)) 'sh2.Range(ActiveCell, ActiveCell.Offset(20, 0))
            If IsEmpty(myRange) = True Then
               myEmail = myEmail & sh2.Cells(r, 2).Value & ", " & sh2.Cells(r, 1).Value & ";"
            End If
    Next myRange


    This is my original code. The columns are fixed so I work with offset to find the email address.

    Code
    Dim myRange As Range
    Dim myEmail As String
    lastrow = sh3.Cells(Rows.Count, 1).End(xlUp).Row 'counts the number of rows in use
    For Each myRange In sh3.Range("A1:A" & lastrow)
    If myRange = "x" Then
    myEmail = myEmail & myRange.Offset(0, 3).Value & ";"
    End If
    Next myRange
  • It might be worth adding an OR statement and using CLEAN/TRIM.... Ive seen sheets that had some noise in before which messed a similar routine up.

  • What is "myEmail"? What is "r" in "Cells(r, 2)"?
    You are missing a bunch of Sheet references also.
    When you say that it is driven from a selected Cell, the Sheet with that selected Cell should be the ActiveSheet, right?
    If you're working on your ActiveSheet, this might work.

    Code
    Dim c As Range
        For Each c In Range(Cells(Selection.Row, Selection.Column), Cells(Cells(Rows.Count, Selection.Column).End(xlUp).Row, Selection.Column))
            If Len(c) = 0 Then     'Do here what you need to do
        Next c


    Maybe explain in detail what you want to achieve.

  • Hi jolivanes


    I'm including the entire code but I just figured out the problem but not sure what to do about it. - The spreadsheets returns that come in and I have to send a reminder to everyone who hasn't sent their badge back by a certain date. So for each month, I move to a different column which is why I need to keep myRange variable. - MyEmail is a container that collects who to send the reminder to.


    r is the row of the ActiveCell but the ActiveCell of course won't move which is why I get the same result over. - So I need to activate the row the For Each statement is looking at but I'm not quite sure how that is done.


  • Am I right in thinking that if you cycle through for instance Column R (Agust 2018) and you encounter an empty cell you need to get the values of Column 2 and Column 1 of that row?


    If you change this

    Code
    myEmail = myEmail & sh2.Cells(r, 2).Value & ", " & sh2.Cells(r, 1).Value & ";"


    to

    Code
    myEmail = myEmail & sh2.Cells(myRange.Row, 2).Value & ", " & sh2.Cells(myRange.Row, 1).Value & ";"


    Would that work?

  • Hi Jolivanes


    It works. It was still playing up depending on where my cursor (ActiveCell) was but I've included a line to send it to the top so it's working perfectly now.


    So the portion of the code now looks like this:


    Code
    Dim myRange As Range
    Dim myEmail As String
    sh2.Cells(1, c).Select
    For Each myRange In sh2.Range(ActiveCell, ActiveCell.Offset(20, 0))
            If IsEmpty(myRange) = True Then
              myEmail = myEmail & sh2.Cells(myRange.Row, 2).Value & ", " & sh2.Cells(myRange.Row, 1).Value & ";"
            End If
    Next myRange


    I had tried

    Code
    ActiveCell.End(xlUp).Select


    to send it to the top but for some reason that wouldn't work.


    Thanks for your help, as usual! I am learning. (I'm also having fun.)


    Christine

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!