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

[davjoh123]

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

 

[paul.robinson]

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

 

[davjoh123]

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)