ShowMe function works fine except I need the offset to adjustdepending on column
 

Can someone point me in the right direction for this?

Function Showme(rngSrc As Range) As String
Dim rng As Range, rngMin As Range, dbl As Double, rngLeft As Range
Dim intOffset As Integer

Set rngMin = rngSrc.Cells(1, 1)
dbl = rngMin.Value
For Each rng In rngSrc
If rng.Value < dbl Then
Set rngMin = rng
dbl = rngMin.Value
End If
Next

'Showme = rngMin.Address
Set rngLeft = rngMin.Offset(, -3).Cells(1, 1)
'MsgBox rngMin.Address
'MsgBox rngLeft.Address
'MsgBox rngLeft.Value

Showme = rngLeft.Value



End Function

ShowMe function works fine except I need the offset to adjustdepending on column
 

Hi
I could not find a question....
regards
Paul

On Nov 25, 6:07*pm, " wrote:
Can someone point me in the right direction for this?

Function Showme(rngSrc As Range) As String
* *Dim rng As Range, rngMin As Range, dbl As Double, rngLeft As Range
* *Dim intOffset As Integer

* *Set rngMin = rngSrc.Cells(1, 1)
* *dbl = rngMin.Value
* *For Each rng In rngSrc
* *If rng.Value < dbl Then
* * * Set rngMin = rng
* * * dbl = rngMin.Value
* *End If
* *Next

* *'Showme = rngMin.Address
* *Set rngLeft = rngMin.Offset(, -3).Cells(1, 1)
* *'MsgBox rngMin.Address
* *'MsgBox rngLeft.Address
* *'MsgBox rngLeft.Value

* *Showme = rngLeft.Value

End Function

ShowMe function works fine except I need the offset to adjustdepending on column
 

On Nov 25, 10:07*am, "
wrote:
Can someone point me in the right direction for this?

Function Showme(rngSrc As Range) As String
* *Dim rng As Range, rngMin As Range, dbl As Double, rngLeft As Range
* *Dim intOffset As Integer

* *Set rngMin = rngSrc.Cells(1, 1)
* *dbl = rngMin.Value
* *For Each rng In rngSrc
* *If rng.Value < dbl Then
* * * Set rngMin = rng
* * * dbl = rngMin.Value
* *End If
* *Next

* *'Showme = rngMin.Address
* *Set rngLeft = rngMin.Offset(, -3).Cells(1, 1)
* *'MsgBox rngMin.Address
* *'MsgBox rngLeft.Address
* *'MsgBox rngLeft.Value

* *Showme = rngLeft.Value

End Function

I solved the problem with the following

intOffset = rngMin.Column
intOffset = "-" & (intOffset - 1)
'Showme = rngMin.Address
Set rngLeft = rngMin.Offset(, intOffset).Cells(1, 1)