CODEWITHSUNDEEP
vbaexcelexcel-formulasubtraction
Newbie
Newbie

Reputation: 5

Excel VBA- Subtracting Variable from Active Cell

Here's the code:

Sub Minus()
Dim numsub As Integer
If (D3 <> "") Then
    numsub = Worksheets("Inventario 31-12-2015 ").Range("D3").Value
    Dim FindString As Integer
    Dim Rng As Range
    FindString = ActiveWorkbook.Worksheets("Inventario 31-12-2015 ").Range("C3").Value
        With Sheets("Inventario 31-12-2015 ").Range("C25:C")
        Set Rng = .Find(What:=FindString, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        End With
        If Not Rng Is Nothing Then
            Application.Goto Rng.Offset(0, 4), True
            ActiveCell.Value = ActiveCell.Value - numsub
        End If
End If
End Sub

What I am trying to do is: Find the correct cell (This function should work as I had it already), select it and subtract the value of D3. As i am very new to VBA I can't get the code to work. Any tips, feedback or comments are welcome and appreciated. Thx

Upvotes: 0

Views: 1194

Answers (1)

SJR
SJR

Reputation: 23081

This answer picks up on the comments above, as well as tidying it up a little. (Does your sheet name really have a space at the end?)

Declaration of FindString changed as suggested by OP. Also I changed your Integer to Long, which is good practice (Google it for details).

Sub Minus()

Dim numsub As Long
Dim FindString As String
Dim Rng As Range

With Worksheets("Inventario 31-12-2015 ")
    If .Range("D3") <> vbNullString Then
        numsub = .Range("D3").Value
        FindString = .Range("C3").Value
        With .Range("C25:C100") 'change 100 to suit
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
        End With
        If Not Rng Is Nothing Then
            Application.Goto Rng.Offset(0, 4), True
            Rng.Offset(0, 4).Value = Rng.Offset(0, 4).Value - numsub
        End If
    End If
End With

End Sub

Upvotes: 2

Related Questions