Finding min value of named range and then posting log entry causes 4 line to be written

[gitmeto]

I am attempting to a check for the min value of an array against and compare this to a value. It correctly determines the condition, posts the correct info on the last entry of the log but it proceeds to do this 4 times which is the same number of elements in the array.

The following table provides the array:

Stim1	17​	-1.07​
Stim2	22​	-1.07​
Stim3	27​	-0.48​
Control	70​	-0.06​

Here is the code that builds this array:

Range("Data[Stim1 Targets]").End(xlDown).Offset(0, 1).Value = Range("Data[Stim1 Targets]").End(xlDown).Offset(0, 0).Value
Range("Data[Stim2 Targets]").End(xlDown).Offset(0, 1).Value = Range("Data[Stim2 Targets]").End(xlDown).Offset(0, 0).Value
Range("Data[Stim3 Targets]").End(xlDown).Offset(0, 1).Value = Range("Data[Stim3 Targets]").End(xlDown).Offset(0, 0).Value
Range("Data[Control Targets]").End(xlDown).Offset(0, 1).Value = Range("Data[Control Targets]").End(xlDown).Offset(0, 0).Value
Range("Data[Shams]").End(xlDown).Offset(0, 1).Value = Range("Data[Shams]").End(xlDown).Offset(0, 0).Value

Here is the code that checks the conditions:

'Enter stimuli values configured in spreadsheet
Range("daily_stimuli[Intensity]").End(xlUp).Offset(1, 0).Value = exp_results.Range("G1").Value
Range("daily_stimuli[Intensity]").End(xlUp).Offset(2, 0).Value = exp_results.Range("G2").Value
Range("daily_stimuli[Intensity]").End(xlUp).Offset(3, 0).Value = exp_results.Range("I1").Value
Range("daily_stimuli[Intensity]").End(xlUp).Offset(4, 0).Value = exp_results.Range("I2").Value

'Enter Calculated dPrime values from spreadsheet
Range("daily_stimuli[dPrime]").End(xlUp).Offset(1, 0).Value = Range("Data[Stim1 dPrime]").End(xlDown).Offset(0, 0).Value
Range("daily_stimuli[dPrime]").End(xlUp).Offset(2, 0).Value = Range("Data[Stim2 dPrime]").End(xlDown).Offset(0, 0).Value
Range("daily_stimuli[dPrime]").End(xlUp).Offset(3, 0).Value = Range("Data[Stim3 dPrime]").End(xlDown).Offset(0, 0).Value
Range("daily_stimuli[dPrime]").End(xlUp).Offset(4, 0).Value = Range("Data[Control dPrime]").End(xlDown).Offset(0, 0).Value

Here is the conditions that it is properly matching:

If Application.WorksheetFunction.Min(Range("daily_stimuli[dPrime]")) < Range("dPrime"). _
    Value And Application.WorksheetFunction.Max(Range("daily_stimuli[dPrime]")) < Range("dPrime").Value Then
        Range("Log[Date]").Offset(1, 0).Value = Date
        Range("Log[Time]").Offset(1, 0).Value = Format(Now(), "h:mm AM/PM")
        Range("Log[Description]").Offset(1, 0).Value = "d' Calculation"
        Range("Log[Notes]").Offset(1, 0).Value = "All stimulus d' values are below the d' threshold."
        'MsgBox "All stimulus d' values are below the d' threshold."
Exit Sub
End If

and finally here is the output that is written to the log after running the macro:

12/10/2021​	5:10 PM​	d' Calculation	All stimulus d' values are below the d' threshold.
	1/0/1900​	d' Calculation	All stimulus d' values are below the d' threshold.
		d' Calculation	All stimulus d' values are below the d' threshold.
			All stimulus d' values are below the d' threshold.

Other than going back and cleaning up the log to delete that last 3 lines, is there something in the syntax of the code that could be written better to eliminate the need to do so?

Any assistance would contribute to the advancement of science.

 

[jasonb75]

Named ranges are useless when debugging something like this if you don't tell us the actual ranges that they refer to.

If you offset from a named range then the offset range will be the same size as the original range. If you want offset to a single cell from a larger range then you need to use the resize command with it as well.