CODEWITHSUNDEEP
vbaexcel
Ricardo Cavaco
Ricardo Cavaco

Reputation: 11

Decrementing a value in VBA

I'm new here and to VBA, and joined because a friend told me this community is helpful. I'm currently having a problema in VBA that is as follow:

-The user chooses a code from a list in the Worksheet "Book Rent"; -This code is kept in Cell D10 and is saved by a Macro in another Sheet "Renting" where several data is saved form the form; -Then in another Worksheet called "Books" there is the book code in column B, the title in column C and the quantity of books available in D; -What I wanted is when the user rents the book, the quantity decrements by 1(I don't need a validation if the number is zero or not right now, so just the decrement is enough);

I have tried doing Sheets("Book Rent").Cells(10, "D") = Sheets("Books").Range("D5") - 1 but it gives me an error. I am right now, trying to use a for loop but it also gives me na error. If someone could give me some help or if you need more information I would be glad to help.

Here's my for loop:

Dim Looper As Integer
    For Looper = 5 To Sheets("Livros").Cells(Looper, 2) = Sheets("Form Aluguer").Cells(10, "D")
        Sheets("Livros").Cells(Looper, "D").Select
        Cells(Looper, "D") = Cells(Looper, "D") - 1
    Next Looper

Thank you by the way.

Upvotes: 1

Views: 2330

Answers (2)

Brandon Barney
Brandon Barney

Reputation: 2392

If you want to loop from X to Y (where X > Y) just use this:

For X = 5 to 0 Step - 1

Next

The Step command tells it how to increment X, and 0 is how long we are incrementing X until. Keep in mind that the For loop needs some number as the second argument, so you could do:

For X = 5 to Sheets("Form Aluguer").Cells(10, "D")

But you can only do this if Sheets("Form Aluguer").Cells(10, "D") contains a number.

If you want to loop while Sheets("Livros").Cells(Looper, 2) = Sheets("Form Aluguer").Cells(10, "D") you would need to do something like this:

Dim looper as Long
Do While Sheets("Livros").Cells(Looper, 2) = Sheets("Form Aluguer").Cells(10, "D")
    looper = looper -1
Loop

Upvotes: 1

wrslphil
wrslphil

Reputation: 258

EDIT:

Here is what I think you are looking for, I've left my original answer at the bottom of this post. But this code is more of a working version of what I think you need. It just may need to be adapted slightly depending on the layout of your workbook.

I have added more comments than I usually would due to you being new to VBA.

Hope this helps

Sub Decrement()
'Declare a BookCount variable to hold the value of the cell containing the number of books
Dim BookCount As Integer
'Declare a range to hold the range of the Books Quantity column
Dim QuantityRng As Range
'Dim two ranges to use during the loops
Dim RentCell As Range, BookCell As Range
'Declare and set a variable named RentRng to the Rental list and BookRng to the Book list's Code column
Dim RentRng As Range, BookRng As Range
Set RentRng = Worksheets("Renting").Range("A:A")
Set BookRng = Worksheets("Books").Range("B:B")

'For every cell in the Renting list
For Each RentCell In RentRng
'Stop the subroutine when the loop encounters a blank cell
    If RentCell.Value = "" Then
        Exit Sub
    End If
'Check every cell in the Book code list
    For Each BookCell In BookRng
'Exit the loop when encounters a blank cell so can look for the next book in the outer loop
        If BookCell.Value = "" Then
            Exit For
        End If
'Check if the Rental worksheet Code Matches the Books worksheet code, and if so then decrements the field by one
        If RentCell.Value = BookCell.Value Then
            Set QuantityRng = BookCell.Offset(0, 2)
            BookCount = QuantityRng.Value
            BookCount = BookCount - 1
            QuantityRng.Value = BookCount
        End If
    Next BookCell
Next RentCell

End Sub

I'm not sure whether I understood correctly, but I would start by declaring a variable named BookCount to hold the value of the cell in question. Then you can use BookCount = BookCount - 1

The problem you have there, is that you're trying to take 1 away from a range, when it's actually the value of the range that you are wanting to amend.

Then you can set the value of the range, to BookCount

Here's a small example

Sub Decrement()
Dim BookCount As Integer
BookCount = Range("A1").Value
BookCount = BookCount - 1
Range("A1").Value = BookCount
End Sub

Upvotes: 0

Related Questions