Finding Cell address 1

[vba317]

I am looking for a max value within a range I have sucessfully done this. What I need to do now is to find out how to calculate the Cell address of the cell that the max value is in.

So for example say I am sampling cells C10 - C16 and the max value is in cell C14 I need to have a way of getting the value 14 returned, is this possible?

 

[PHV]

I have sucessfully done this
Which code ?

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

 

[N1GHTEYES]

Are you trying to do this in code, or on the worksheet? If the latter, then you'd be better asking in the Office forum.

If you want to do it in code, then - as PHV suggests - you should post here the code you already have by which you found the max.

We can then suggest how you could modify the code to find the location of the max. Actually, it is very easy, so when you post your code I expect you'll probably get answers quite quickly.

Tony

 

[MazeWorX]

=CELL("address",INDEX(A1:A100,MATCH(MAX(A1:A100),A1:A10,0)))

This will give you the address of the first max value for the range a1-a10, however if you have multiple instances of the max value you will need to build an array to return all of the locations



HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MazeWorX]

here is a function you can check a range of cells and build a string of the results - does not use an array

modify as needed

Function MaxAdr(rng As Range)
    Dim c As Range
    Dim MaxNum As Double
    Dim strData As String

    MaxNum = Application.WorksheetFunction.Max(rng)

    For Each c In rng
        If c.Value = MaxNum Then
            strData = strData & ", " & c.Address
        End If
    Next c
    MaxAdr = Trim(Right(strData, Len(strData) - 2))
End Function

add this formula to a cell =MaxAdr(A1:A10)

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[vba317]

Sorry,

I did not post the code, The part in red is where I am having the problem

Sub NewArray1()

'Did not include the Dim statements

LastRow = ActiveSheet.UsedRange.Rows.Count

ReDim Preserve LowCellValue(0 To LastRow, 0 To LastRow)
ReDim Preserve HighCellValue(0 To LastRow, 0 To LastRow)
ReDim Preserve Diff(0 To 2, 0 To LastRow)
'Set up LowLoopCount LoopStart and LoopEnd values for initial loop


If LowValueCount = 0 Then LowValueCount = 2
If LowLoopCount = 0 Then LowLoopStart = 2
If LowLoopCount = 0 Then LowLoopEnd = 9

'Buy, Sell and stop signals

Buy1 = 1
Buy2 = 1.236
Buy3 = 1.382
Buy4 = 1.5

Sell1 = 0.618
Sell2 = 0.786
Sell3 = 1
Sell4 = 1.236

Stop1 = 1.236
Stop2 = 1.382
Stop3 = 1.5
Stop4 = 1.618


For LowLoopCount = LowLoopStart To LowLoopEnd

'Set up loop LowLoopCount LoopStart and LoopEnd values for all loops

If LowLoopCount > 2 Then LowLoopStart = HighLoopEnd + 1
If LowLoopCount > 2 Then LowLoopEnd = HighLoopEnd + 7
HighLoopStart = LowLoopEnd + 1
HighLoopEnd = LowLoopEnd + 7

Value = Value + 1
If LowLoopEnd >= LastRow Then LowLoopEnd = LastRow

'Calculate Range for MinValue and Maxvalue

MinValue = Application.WorksheetFunction.Min(Range("D" & LowLoopStart & ":" & "D" & LowLoopEnd))
MaxValue = Application.WorksheetFunction.Max(Range("C" & HighLoopStart & ":" & "C" & HighLoopEnd))

'Calculate Cell that Minvalue is in
For Each Cell In Range("D" & LowLoopStart & ":" & "D" & LowLoopEnd)
If Cell.Value = MinValue Then _
Cell.Interior.Color = vbRed

If Cell.Value = MinValue Then _
LowCellValue(1, Value) = MinValue

Next Cell

If HighLoopStart > LastRow Then Exit Sub
If HighLoopEnd > LastRow Then Exit Sub

'Turn Cell green that Maxvalue is in
For Each Cell In Range("C" & HighLoopStart & ":" & "C" & HighLoopEnd)
If Cell.Value = MaxValue Then _
Cell.Interior.Color = vbGreen

'If Cell.Value = MaxValue Then _
'Cells("C",

'.Address("C", True) = MaxCellCount



'Assign MaxValue
If Cell.Value = MaxValue Then _
HighCellValue(1, Value) = MaxValue

'Calculate Difference between Max and Min value
If Cell.Value = MaxValue Then _
Diff(1, Value) = (MaxValue - MinValue)

[Red] 'Calculate First Buy Point
If Cell.Value = MaxValue Then _
Range("G" & Cell.Value).Formula = (((MaxValue - (Diff(1, Value)) * Buy1)))[/Red]

Next Cell



Next LowLoopCount



End Sub

 

[PHV]

I'd try this:

...
For Each Cell In Range("C" & HighLoopStart & ":" & "C" & HighLoopEnd)
If Cell.Value = MaxValue Then
  Cell.Interior.Color = vbGreen
  'Assign MaxValue
  HighCellValue(1, Value) = MaxValue
  'Calculate Difference between Max and Min value
  Diff(1, Value) = (MaxValue - MinValue)
  'Calculate First Buy Point
  Range("G" & Cell.Row).Value = (((MaxValue - (Diff(1, Value)) * Buy1)))
End IF
Next Cell
...

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

 

[vba317]

PHV,
If there was a way to give you a superstar I would. You helped me resolve a problem I have been working on for 3 months. thank you!!!!!


Tom