Comparing every cell in a range to another cell

Question

I'm trying to write a macro that will look at a series of times, compare each one with a benchmark time and then generate a note next to each compared time. When I run the macro it will do this for the first cell in the range, but not for the rest.

Cell C8 in the "ActivityData" worksheet is the benchmark time. The times to be compared start in cell F12 of the "SIS" worksheet and vary in number.

Here is the code in its entirety:

Sub TimeCalc()

Dim wb As Workbook
Dim SIS As Worksheet
Dim Act As Worksheet

Set wb = ActiveWorkbook
Set SIS = wb.Worksheets("SIS")
Set Act = wb.Worksheets("ActivityData")

'Navigate to start times
wb.Worksheets("SIS").Select
Range("f12").Select
SIS.Range(Selection, Selection.End(xlDown)).Select

'Compare start times
For Each rng In Selection
If Act.Range("C8").Value < rng.Value Then
    ActiveCell.Offset(0, -1).Range("A1").Value = "Missed " & Format(Act.Range("c8").Value, "Medium time") & " - " & Format((rng.Value - 0.000694444), "Medium Time")
    Else
    End If
    On Error Resume Next
    Next rng

End Sub

Thanks for your help!

Hi Siddharth, this is only a piece of the code where I'm having problems. There is other stuff beyond it, but it is working as it should. — user2237763, Apr 03 '13 at 15:14
I would generally advise against using `OERN` Ok, I saw your code here is a suggestion again which actually could help you identify the error. See this link http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select/10718179#10718179 So amend your code and work with the objects instead of `selection` That ways you can debug it and see if the code is looping through every cells of that range :) — Siddharth Rout, Apr 03 '13 at 15:18
Thank you for this advice. I will change the code to reflect more object interaction. — user2237763, Apr 03 '13 at 15:38

score 1 · Accepted Answer · answered Apr 03 '13 at 15:34

Assuming you want to place the result in the column to the left of the data you are examining:

Option Explicit

Sub TimeCalc()

    Dim wb As Workbook
    Dim SIS As Worksheet
    Dim Act As Worksheet
    Dim myBaseLineRng As Range
    Dim compareRng As Range
    Dim Rng As Range

    On Error GoTo 0

    Set wb = ActiveWorkbook
    Set SIS = wb.Worksheets("SIS")
    Set Act = wb.Worksheets("ActivityData")
    Set myBaseLineRng = Act.Range("C8")
    Set compareRng = SIS.Range(SIS.Range("F12"), SIS.Range("F12").End(xlDown))

    'Compare start times
    For Each Rng In compareRng.Cells
        If myBaseLineRng.Value < Rng.Value Then
            Rng.Offset(0, -1).Value = "Missed " & Format(myBaseLineRng.Value, "Medium time") & " - " & Format((Rng.Value - 0.000694444), "Medium Time")
        End If
    Next Rng

End Sub

score 0 · Answer 2 · answered Apr 03 '13 at 15:29

0

You are only comparing the values in your benchmark list against the first value in your activity list as shown in the If statement. you will need to loop through your activity list to check each row if i understand your question right

ie Loop through the activity list For each value in the activity list lOop through your benchmark times to check against that Next value in activity list.

Unless I misunderstood your question.

answered Apr 03 '13 at 15:29

David

144
6

Yes that is what I'm trying to do David. I'm comparing the If statements to wrap my head around structuring these. – user2237763 Apr 03 '13 at 15:42

Comparing every cell in a range to another cell

2 Answers2