Subtract the value from corespoding cell from minimum range value|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Hi all

I have following problem with VBA. I want to subtract coresponding value from selected range with column offset (screenshot in attachment).

I came to point where excel return min value from range, but I don't know how to point to subtract corespoding cell.

Sub a()

 

'PURPOSE: Apply A Yellow Fill Color To A Cell Range

 

Dim rng As Range

Dim DefaultRange As Range

Dim FormatRuleInput As String

 

 

 

'Determine a default range based on user's Selection

  If TypeName(Selection) = "Range" Then

    Set DefaultRange = Selection

  Else

    Set DefaultRange = ActiveCell

  End If

  

  

'Get A Cell Address From The User to Get Number Format From

  On Error Resume Next

    Set rng = Application.InputBox( _

      Title:="Cans choice", _

      prompt:="Select desired cans", _

      Default:=DefaultRange.Address, _

      Type:=8)

 

      

Range("H3").Value = Application.WorksheetFunction.Min(rng.Offset(, 1))

Range("I3").Value = Application.WorksheetFunction.Max(rng.Offset(, 2))

 

 

With Worksheets("Sheet1")

 

 

 

MinVal = WorksheetFunction.Min(rng.Offset(, 1))

    

Range("F1").Value = MinVal

 

 

 

End With

 

 

     

      

  On Error GoTo 0

 

'Test to ensure User Did not cancel

  If rng Is Nothing Then Exit Sub

 

End Sub