I am having a major problem with the application.worksheetfunction.min()
approach, and I believe it is b/c the range I am referencing is not sorted,
and can not be for my application.

I have tried the following to find the minimum value in the range without
success:

Function MyMin(minvaluerange)
For Each c In minvaluerange
If c < c + 1 Then
MyMin = c
Else
MyMin = c + 1
End If
Next c
End Function


Help would be greatly appreciated.

Thanks,

Jason

Why not just use this. No sorting required
Sub findmin()
MsgBox Application.Min([J:j])
End Sub

--
Don Guillett
SalesAid Software

"TxRaistlin" wrote in message
...
I am having a major problem with the application.worksheetfunction.min()
approach, and I believe it is b/c the range I am referencing is not
sorted,
and can not be for my application.

I have tried the following to find the minimum value in the range without
success:

Function MyMin(minvaluerange)
For Each c In minvaluerange
If c < c + 1 Then
MyMin = c
Else
MyMin = c + 1
End If
Next c
End Function


Help would be greatly appreciated.

Thanks,

Jason

This should do it

Function MyMin(minvaluerange)
Dim c
MyMin = 9.99999999999999E+307
For Each c In minvaluerange
If c < MyMin Then
MyMin = c
End If
Next c
End Function

But why not just use Excel's built-in function

Function mymin2(minvaluerange)
mymin2 = Application.Min(minvaluerange)
End Function

--

HTH

RP
(remove nothere from the email address if mailing direct)


"TxRaistlin" wrote in message
...
I am having a major problem with the application.worksheetfunction.min()
approach, and I believe it is b/c the range I am referencing is not
sorted,
and can not be for my application.

I have tried the following to find the minimum value in the range without
success:

Function MyMin(minvaluerange)
For Each c In minvaluerange
If c < c + 1 Then
MyMin = c
Else
MyMin = c + 1
End If
Next c
End Function


Help would be greatly appreciated.

Thanks,

Jason

That doesn't work. example of numbers in list:

-0.0001
-0.0000001
-0.0000001
-0.0001
-0.26206
-0.27286
0

The last run returned -0.28286 as a result of using the following:

myminvalue = Application.Min(minlongrange)

I got similar results with application.workseetfunction.min().

I have edited the list to try error checking, adding smaller numbers at the
top, and the returned result kept being the value after the changes I made
(for the above I got -.26206 for the worksheetfunction approach).

This is why I was hoping to use a cell by cell comparison approach,
returning the minimum value once all cells have been compared.

I have supplied my full code below, with a few edits of variations I have
tried. One additional thing, I have noticed that the min result doesn't
always get transferred to the summary page. On multiple occasions, the
worksheetfunction.index(...) was returning values not in the list, or
anywhere for that matter, such as adding " -" to the end of an actual
value. Haven't quite figured out why it stopped yet, at least the last few
runs, and that is even more annoying, b/c I didn't do anything to stop it!

Thanks, Jason

Sub read_long(loadstep, counter)
Dim myrange, mytable, mysheet, mylookup, maxlongrange, mycolumn,
myfirstrange, maxrow, _
maxlookup, maxvaluerow, minlookup, minvaluerow, minrow, minlongrange
Summary.Activate
Cells(117 + counter + loadstep, 1).value = "Load Step " & loadstep
Cells(120 + 2 * counter + loadstep, 1).value = "Load Step " & loadstep
mysheet = "LONGIT" & loadstep
myrange = "a:f"
myfirstrange = Worksheets(mysheet).Range("a:a")
mytable = Worksheets(mysheet).Range(myrange)

For step = 0 To 4
mycolumn = 2 + step
maxlookup = "maximum"
maxrow = Application.WorksheetFunction.Match(maxlookup,
myfirstrange, 0)
maxvaluerow = maxrow + 2
Worksheets(mysheet).Select
maxlongrange = Range(Cells(maxrow - 201, mycolumn), Cells(maxrow -
7, mycolumn))
Worksheets(mysheet).Cells(maxvaluerow, mycolumn) =
Application.WorksheetFunction.Max(maxlongrange)
Summary.Select
Cells(117 + counter + loadstep, mycolumn) =
Application.WorksheetFunction.Index(mytable, maxvaluerow, mycolumn)

minlookup = "minimum"
minrow = Application.WorksheetFunction.Match(minlookup,
myfirstrange, 0)
minvaluerow = minrow + 2
Worksheets(mysheet).Select
'minlongrange = Range(Cells(1, mycolumn), Cells(minrow - 3, mycolumn))
minlongrange = Range(Cells(minrow - 197, mycolumn), Cells(minrow -
3, mycolumn))
'myminvalue = MyMin(minlongrange)
myminvalue = Application.Min(minlongrange)
Worksheets(mysheet).Cells(minvaluerow, mycolumn) = myminvalue
'Worksheets(mysheet).Cells(minvaluerow, mycolumn) =
Application.WorksheetFunction.Min(minlongrange)
Summary.Select
Cells(120 + 2 * counter + loadstep, mycolumn) = myminvalue
'Cells(120 + 2 * counter + loadstep, mycolumn) =
Application.WorksheetFunction.Index(mytable, minvaluerow, mycolumn)
Next step
End Sub





"Don Guillett" wrote:

Why not just use this. No sorting required
Sub findmin()
MsgBox Application.Min([J:j])
End Sub

--
Don Guillett
SalesAid Software

"TxRaistlin" wrote in message
...
I am having a major problem with the application.worksheetfunction.min()
approach, and I believe it is b/c the range I am referencing is not
sorted,
and can not be for my application.

I have tried the following to find the minimum value in the range without
success:

Function MyMin(minvaluerange)
For Each c In minvaluerange
If c < c + 1 Then
MyMin = c
Else
MyMin = c + 1
End If
Next c
End Function


Help would be greatly appreciated.

Thanks,

Jason

uuh, isn't -0.28286 the smallest?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"TxRaistlin" wrote in message
...
That doesn't work. example of numbers in list:

-0.0001
-0.0000001
-0.0000001
-0.0001
-0.26206
-0.27286
0

The last run returned -0.28286 as a result of using the following:

myminvalue = Application.Min(minlongrange)

I got similar results with application.workseetfunction.min().

I have edited the list to try error checking, adding smaller numbers at
the
top, and the returned result kept being the value after the changes I made
(for the above I got -.26206 for the worksheetfunction approach).

This is why I was hoping to use a cell by cell comparison approach,
returning the minimum value once all cells have been compared.

I have supplied my full code below, with a few edits of variations I have
tried. One additional thing, I have noticed that the min result doesn't
always get transferred to the summary page. On multiple occasions, the
worksheetfunction.index(...) was returning values not in the list, or
anywhere for that matter, such as adding " -" to the end of an actual
value. Haven't quite figured out why it stopped yet, at least the last
few
runs, and that is even more annoying, b/c I didn't do anything to stop it!

Thanks, Jason

Sub read_long(loadstep, counter)
Dim myrange, mytable, mysheet, mylookup, maxlongrange, mycolumn,
myfirstrange, maxrow, _
maxlookup, maxvaluerow, minlookup, minvaluerow, minrow, minlongrange
Summary.Activate
Cells(117 + counter + loadstep, 1).value = "Load Step " & loadstep
Cells(120 + 2 * counter + loadstep, 1).value = "Load Step " & loadstep
mysheet = "LONGIT" & loadstep
myrange = "a:f"
myfirstrange = Worksheets(mysheet).Range("a:a")
mytable = Worksheets(mysheet).Range(myrange)

For step = 0 To 4
mycolumn = 2 + step
maxlookup = "maximum"
maxrow = Application.WorksheetFunction.Match(maxlookup,
myfirstrange, 0)
maxvaluerow = maxrow + 2
Worksheets(mysheet).Select
maxlongrange = Range(Cells(maxrow - 201, mycolumn), Cells(maxrow -
7, mycolumn))
Worksheets(mysheet).Cells(maxvaluerow, mycolumn) =
Application.WorksheetFunction.Max(maxlongrange)
Summary.Select
Cells(117 + counter + loadstep, mycolumn) =
Application.WorksheetFunction.Index(mytable, maxvaluerow, mycolumn)

minlookup = "minimum"
minrow = Application.WorksheetFunction.Match(minlookup,
myfirstrange, 0)
minvaluerow = minrow + 2
Worksheets(mysheet).Select
'minlongrange = Range(Cells(1, mycolumn), Cells(minrow - 3,
mycolumn))
minlongrange = Range(Cells(minrow - 197, mycolumn), Cells(minrow -
3, mycolumn))
'myminvalue = MyMin(minlongrange)
myminvalue = Application.Min(minlongrange)
Worksheets(mysheet).Cells(minvaluerow, mycolumn) = myminvalue
'Worksheets(mysheet).Cells(minvaluerow, mycolumn) =
Application.WorksheetFunction.Min(minlongrange)
Summary.Select
Cells(120 + 2 * counter + loadstep, mycolumn) = myminvalue
'Cells(120 + 2 * counter + loadstep, mycolumn) =
Application.WorksheetFunction.Index(mytable, minvaluerow, mycolumn)
Next step
End Sub





"Don Guillett" wrote:

Why not just use this. No sorting required
Sub findmin()
MsgBox Application.Min([J:j])
End Sub

--
Don Guillett
SalesAid Software

"TxRaistlin" wrote in message
...
I am having a major problem with the
application.worksheetfunction.min()
approach, and I believe it is b/c the range I am referencing is not
sorted,
and can not be for my application.

I have tried the following to find the minimum value in the range
without
success:

Function MyMin(minvaluerange)
For Each c In minvaluerange
If c < c + 1 Then
MyMin = c
Else
MyMin = c + 1
End If
Next c
End Function


Help would be greatly appreciated.

Thanks,

Jason

Wow, now I feel stupid, lol.

Definitely need more coffee. Thanks for pointing out the obvious.

Jason

"Bob Phillips" wrote:

uuh, isn't -0.28286 the smallest?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"TxRaistlin" wrote in message
...
That doesn't work. example of numbers in list:

-0.0001
-0.0000001
-0.0000001
-0.0001
-0.26206
-0.27286
0

The last run returned -0.28286 as a result of using the following:

myminvalue = Application.Min(minlongrange)

I got similar results with application.workseetfunction.min().

I have edited the list to try error checking, adding smaller numbers at
the
top, and the returned result kept being the value after the changes I made
(for the above I got -.26206 for the worksheetfunction approach).

This is why I was hoping to use a cell by cell comparison approach,
returning the minimum value once all cells have been compared.

I have supplied my full code below, with a few edits of variations I have
tried. One additional thing, I have noticed that the min result doesn't
always get transferred to the summary page. On multiple occasions, the
worksheetfunction.index(...) was returning values not in the list, or
anywhere for that matter, such as adding " -" to the end of an actual
value. Haven't quite figured out why it stopped yet, at least the last
few
runs, and that is even more annoying, b/c I didn't do anything to stop it!

Thanks, Jason

Sub read_long(loadstep, counter)
Dim myrange, mytable, mysheet, mylookup, maxlongrange, mycolumn,
myfirstrange, maxrow, _
maxlookup, maxvaluerow, minlookup, minvaluerow, minrow, minlongrange
Summary.Activate
Cells(117 + counter + loadstep, 1).value = "Load Step " & loadstep
Cells(120 + 2 * counter + loadstep, 1).value = "Load Step " & loadstep
mysheet = "LONGIT" & loadstep
myrange = "a:f"
myfirstrange = Worksheets(mysheet).Range("a:a")
mytable = Worksheets(mysheet).Range(myrange)

For step = 0 To 4
mycolumn = 2 + step
maxlookup = "maximum"
maxrow = Application.WorksheetFunction.Match(maxlookup,
myfirstrange, 0)
maxvaluerow = maxrow + 2
Worksheets(mysheet).Select
maxlongrange = Range(Cells(maxrow - 201, mycolumn), Cells(maxrow -
7, mycolumn))
Worksheets(mysheet).Cells(maxvaluerow, mycolumn) =
Application.WorksheetFunction.Max(maxlongrange)
Summary.Select
Cells(117 + counter + loadstep, mycolumn) =
Application.WorksheetFunction.Index(mytable, maxvaluerow, mycolumn)

minlookup = "minimum"
minrow = Application.WorksheetFunction.Match(minlookup,
myfirstrange, 0)
minvaluerow = minrow + 2
Worksheets(mysheet).Select
'minlongrange = Range(Cells(1, mycolumn), Cells(minrow - 3,
mycolumn))
minlongrange = Range(Cells(minrow - 197, mycolumn), Cells(minrow -
3, mycolumn))
'myminvalue = MyMin(minlongrange)
myminvalue = Application.Min(minlongrange)
Worksheets(mysheet).Cells(minvaluerow, mycolumn) = myminvalue
'Worksheets(mysheet).Cells(minvaluerow, mycolumn) =
Application.WorksheetFunction.Min(minlongrange)
Summary.Select
Cells(120 + 2 * counter + loadstep, mycolumn) = myminvalue
'Cells(120 + 2 * counter + loadstep, mycolumn) =
Application.WorksheetFunction.Index(mytable, minvaluerow, mycolumn)
Next step
End Sub





"Don Guillett" wrote:

Why not just use this. No sorting required
Sub findmin()
MsgBox Application.Min([J:j])
End Sub

--
Don Guillett
SalesAid Software

"TxRaistlin" wrote in message
...
I am having a major problem with the
application.worksheetfunction.min()
approach, and I believe it is b/c the range I am referencing is not
sorted,
and can not be for my application.

I have tried the following to find the minimum value in the range
without
success:

Function MyMin(minvaluerange)
For Each c In minvaluerange
If c < c + 1 Then
MyMin = c
Else
MyMin = c + 1
End If
Next c
End Function


Help would be greatly appreciated.

Thanks,

Jason