Min range function compile error 1

[vba317]

I am trying to develop a function to sample a range in vba to find a minimum value and once this value is determined put that value in an adjacent column. I am getting a compile error. I am trying to define a range starting at cell 2 going to cell 10. I am doing a loop so I am assigning the cells to a variable.

Dim myLowestRange As Range

Set myLowestRange = Range("D" & R1 + 1) [red]":" [/red] Range("D" & R1 + 10)

WorksheetFunction.Min (myLowestRange)

Also how do I put the myLowestRange into the adjacent cell?

 

[SkipVought]

hi,

Set myLowestRange = Range(Cells(R1 + 1,"D"), Cells(R1 + 10, "D"))

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

Did you try:

Set myLowestRange = Range("D" & R1 + 1 & ":D" & R1 + 10)

Have fun.

---- Andy

 

[SkipVought]

Also how do I put the myLowestRange into the adjacent cell?

you cannot put a multi-cell range into any cell. Don't know what you mean???

Why not use the MIN() function directly on the sheet?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

What I was hoping to do was the following:
High Low high value low value
74.55 73.00
73.53 72.52
73.96 72.75
74.99 74.39
76.06 75.06
75.54 74.82
76.28 75.02
75.03 71.19
74.29 73.76
75.24 73.97
76.43 75.01
77.89 76.66
78.97 78.12
78.72 78.05
78.47 77.52

In the low column my min formula would pick 71.19 and put 71.19 in the next column labeled low value.
In the high column my max formula would pick 78.97 and put 78.97 in the next column labeled high value.

Tom

 

[PHV]

No VBA needed.
Say your data is on A216
In D2 you enter this formula:
=IF(A2=MAX(A$2:A$16),A2,"")
and if C2 this one:
=IF(B2=MIN(B$2:B$16),B2,"")
copy down each formula et voilà.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

PHV,
If I wanted to use vba because I had 8 different sheets to go through how could I do that? I have been trying to do research on the DMAX function, but I can't quite grasp how I would apply it to my situation. My max column is the C column and I want to sample 15 cells at a time. The worksheet I am working on currently has 1132 rows in it and it gets added to every day. I already have a loop set up to calculate the maximum amount of rows that are populated in the worksheet. Any help would be apprecaited.

 

[SkipVought]

8 different sheets ...My max column is the C column and I want to sample 15 cells at a time...

So what's the REAL objective? Suppose you tell us WHAT you need to do rather than HOW you think it ought to be accomplished?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

Skip,
You are right, I have a spreadsheet that I have been working on for over a year. I am hoping that I can finish this project in the last couple of weeks. My goal is to have a formula that will loop through two columns of numbers. One column labeled high and another column labeled low. The goal is to create a formula that will pick the highest number in a column once that column changes direction and subtract that value against that value in the low column as that changes direction. The current formula is about 80% accurate I am trying to develop a formula that is 90% accurate.

Example:



High Low High Low
74.55 73.00
73.53 72.52
73.96 72.75 72.75
74.99 74.39
76.06 75.06
75.54 74.82
76.28 75.02 76.28
75.03 71.19
74.29 73.76
75.24 73.97
76.43 75.01
77.89 76.66
78.97 78.12 78.97
78.72 78.05
78.47 77.52
77.01 70.93
75.61 72.52
74.63 71.53
74.46 73.44
73.99 71.99
76.31 72.85
76.96 76.23 76.23
78.91 77.81
79.56 77.57 79.56
78.68 76.82
79.29 76.82 79.29
77.66 75.18
77.66 76.46 77.66
77.15 75.00
76.21 72.51
74.06 70.96
75.60 74.82
77.11 75.62
78.34 77.41 77.41
79.04 78.54
79.28 78.15
80.54 79.21
81.59 80.23 81.59
80.29 78.40
80.00 79.14
83.50 82.46 82.46
85.96 84.17
85.85 83.95
86.28 85.18
86.03 84.03
86.71 85.72 86.71
85.55 82.88
84.92 83.58
82.89 79.35
81.13 75.83
75.52 70.00
73.32 69.51
73.16 69.74
75.80 71.81 75.80
72.26 68.85
72.60 70.74 72.60
70.77 67.59
68.40 64.87
69.45 67.53
73.34 71.05 73.34
70.90 68.55
69.42 62.67 62.67
69.76 68.18 69.76
67.63 64.96
65.14 60.61


Tom

 

[SkipVought]

Your discriptions have been woefully scant and incoherent!

High or Low based on WHAT LOGIC?

If you are using the "The goal is to create a formula that will pick the highest number in a column once that column changes direction" then your example does not support THAT logic, as can be seen in the first 5 samples.
[tt]
High Low High Low
74.55 73.00
73.53 [highlight]72.52[/highlight]
73.96 72.75 72.75
74.99 74.39
[highlight]76.06[/highlight] 75.06
75.54 74.82
76.28 75.02 76.28
[/tt]
So I am confused!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

Skip,
You are right My formula is wrong and that is what I am working on. The value I am looking for is 72.52. Here is what I have right now. I have all the proper Dim statements

MarkedRed = 0: MarkedGreen = 0


'** START - Main Routine **
For R1 = 2 To LR    '
'Min Value Calculation // A leg Low location



Low1 = Range("D" & R1 + 1) - Range("D" & R1)
Low2 = Range("D" & R1 + 2) - Range("D" & R1)
Low3 = Range("D" & R1 + 3) - Range("D" & R1)
Low4 = Range("D" & R1 + 4) - Range("D" & R1)
Low5 = Range("D" & R1 + 5) - Range("D" & R1)
Low6 = Range("D" & R1 + 2) - Range("D" & R1 + 1)
Low7 = Range("D" & R1 + 3) - Range("D" & R1 + 1)
Low8 = Range("D" & R1 + 4) - Range("D" & R1 + 1)
Low9 = Range("D" & R1 + 5) - Range("D" & R1 + 1)
Low10 = Range("D" & R1 + 3) - Range("D" & R1 + 2)
Low11 = Range("D" & R1 + 4) - Range("D" & R1 + 2)
Low12 = Range("D" & R1 + 5) - Range("D" & R1 + 2)
Low13 = Range("D" & R1 + 4) - Range("D" & R1 + 3)
Low14 = Range("D" & R1 + 5) - Range("D" & R1 + 3)
Low15 = Range("D" & R1 + 5) - Range("D" & R1 + 4)




' Leg LOW validator    '
'Setting up buy signal
'If LowCount = 1 Then End If

If Low2 < Low1 Or Low3 < Low1 Or Low4 < Low1 Or Low5 < Low1 Or Low6 < Low1 Or Low8 < Low1 Or Low9 < Low1 Or Low10 < Low1 Or Low11 < Low1 Or Low12 < Low1 Or Low13 < Low1 Or Low14 < Low1 Or Low15 < Low1 Then



Range("H" & R1).Value = (Range("D" & R1))
Range("D" & R1).Interior.Color = vbRed        '        ' record cell value for later calculations
MarkedRed = Range("H" & R1)        '        ' check other tracking marker to see if

LowCount = 1

End If

Next R1

What I thought I could do, was when the routine found the lowest value from the 15 possibilities it would set lowcount to 1 once the lowcount was 1 the loop would be skipped. This did not happen, any suggestions?

 

[SkipVought]

Cannot help you without a CLEAR, CONCISE and COMPLETE definition of your logic requirements.

Posting code at this time is not the issue. We need to know WHAT YOU WANT YOUR CODE TO DO!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

Skip,
Sorry that I have not explained my logic more clearly. I will attempt. I have copied parts of a table that if my program was working correctly would identify correct high and low points and put those high and low points in an adjacent column. Starting with the first low column. 72.52 is the lowest point when comparing the 73.00 before it , the 72.75,74.39,75.06 after it. In the first high value 76.28 is the highest value when comparing the 5 values before it and the 5 values after it. Once a high value is established than its time to look for the next low value. Which is in the next cell at 71.19, this is the lowest value in the 5 cells before it and the 5 cells after it. the 78.97 is the highest value in the 5 cells before it and after it. so on , so on. I hope this adds clarity to my logic.


High Low High Low
74.55 73.00
73.53 72.52 72.52
73.96 72.75
74.99 74.39
76.06 75.06
75.54 74.82
76.28 75.02 76.28
75.03 71.19 71.19
74.29 73.76
75.24 73.97
76.43 75.01
77.89 76.66
78.97 78.12 78.97
78.72 78.05
78.47 77.52
77.01 70.93 70.93
75.61 72.52
74.63 71.53
74.46 73.44
73.99 71.99
76.31 72.85
76.96 76.23
78.91 77.81
79.56 77.57 79.56
78.68 76.82
79.29 76.82
77.66 75.18
77.66 76.46
77.15 75.00
76.21 72.51
74.06 70.96 70.96
75.60 74.82
77.11 75.62
78.34 77.41
79.04 78.54
79.28 78.15
80.54 79.21
81.59 80.23
80.29 78.40
80.00 79.14
83.50 82.46
85.96 84.17
85.85 83.95
86.28 85.18
86.03 84.03
86.71 85.72 86.71
85.55 82.88
84.92 83.58

 

[SkipVought]

76.28 is the highest value when comparing the 5 values before it and the 5 values after it.

Not really...
[tt]
73.53
73.96
74.99
76.06
75.54
76.28 <--
75.03
74.29
75.24
76.43
[highlight]77.89[/highlight]
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

using your logic on your example, highest/lowest value when comparing the 5 values before it and the 5 values after it, these are the HIGH values
[tt]
78.97
79.56
86.71
[/tt]
these are the LOW values
[tt]
71.19
70.93
70.96
82.88
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

Yes, Skip you understand my logic correctly. You correctly pointed out that If I am truly comparing 5 before and 5 after than my loop should not start till the 10th cell.

Tom

 

[SkipVought]

I did the SIMPLY with a spreadsheet function. CAVEAT: Start your data in row 6
[tt]
=IF(A6=MAX(A1:A11),A6,"")
=IF(B6=MIN(A1:A11),B6,"")
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}