VBA retrieving cell value from variable within a loop

Question

I've been having some issues getting that data I want from macro in VBA. I'm very new to VBA so I'm likely missing some option that's much easier but for the life of me I can't find it.

So what I need to be able to do is the following:

Pull the value of the cell if the datediff is less than 60. I use today's date - one year and set that to the variable LDate. Compare LDate to the cell value (These cells will always be dates so I don't think I will need to check to see if they're dates). If the cell value is <= 60 then I need to pull the info from 4 cells in the row. Each of these cell values are assigned to a public variable and then used to send an email to the proper person later on. This process is repeated on 3 other columns to pull the cell value for the desired cell in each column and then I use datediff on each of these 4 values and check which of these, if any, are less than 60 and construct an email according to what info needs to be sent.

Here's where I'm checking the values to send the correct email:

If ExampleHold_diff <= 60 And ExampleHold1_diff <= 60 And ExampleHold2_diff <= 60 And ExampleHold3 <= 60 Then
    Mail_Result = "Letter 1: " & ExampleHold_diff & vbNewLine & "Letter 2: " & ExampleHold1_diff & vbNewLine & "Letter 3: " & ExampleHold2_diff & vbNewLine & "Letter 4: " & ExampleHold3_diff

The above checks would be repeated using the same options minus one of the example values until all options are exhausted.

Anyways, I've been successful in getting the macro to change all of background colors depending on the datediff (which tells me that it's processing everything correctly), but when it comes to reporting each cell value it only reports the very last cell within the range. I know it's simply processing every cell in the range which is why I'm only getting the last cell value, I just don't understand how to get the values that I need before they're overwritten.

For Each Cell in Example_rng 'This same loop is used 3 other times with the only difference being the variables

    ExampleStart = Cell.Value
    If DateDiff("d", LDate, ExampleStart) <= 60 Then
        ExampleHold = Cell.Value 'This is the most important value that I need to retrieve. It determines the content of my email.
        PriExInf = Range("A" & ActiveCell.Row)
        AltExInf = Range("B" & ActiveCell.Row)
        ExNum = Range("C" & ActiveCell.Row)
        Cell.Interior.Color = RGB(255, 200, 205)
    ElseIf DateDiff("d", LDate, ExampleStart) > 60 And DateDiff("d", LDate, ExampleStart) < 180 Then
        Cell.Interior.Color = RGB(253, 251, 194)
    ElseIf DateDiff("d", LDate, ExampleStart) >= 180 Then
        Cell.Interior.Color = RGB(198, 250, 194)
    End If

Next Cell

I'm thinking I need to do a For Each loop and nest my other For Each loops inside? Or maybe a Do While loop? I've tried both of these as well as using an array to hold all of the cell values and then reference that, which I think could work but I wasn't having much luck calling on the data in an array, which is probably just my lack of experience with this.

Here's my range (These will never change, but the data inside the range will change every week or two):

Dim Example_rng As Range
Set Example_rng = Worksheets("sheet1").Range("D2:D87")

Dim Example1_rng As Range
Set Example1_rng = Worksheets("sheet1").Range("E2:E87")

Dim Example2_rng As Range
Set Example2_rng = Worksheets("sheet1").Range("F2:F87")

Dim Example3_rng As Range
Set Example3_rng = Worksheets("sheet1").Range("G2:G87")

Any help and/or tips would be greatly appreciated. Let me know what information,if any, I need to provide to help you formulate the best possible answer.

DisplayName DisplayName · Accepted Answer · 2018-04-15T07:59:48

Change all your “ActiveCell” references to “Cell”:

PriExInf = Range("A" & Cell.Row)
AltExInf = Range("B" & Cell.Row)
ExNum = Range("C" & Cell.Row)

This, since “ActiveCell” references the currently “active” cell in the sheet, and that changes after any “Select” statement

While you are looping through a range using “Cell” as your looping variable, and then that is the reference you need to use

VBA retrieving cell value from variable within a loop

1 Answers