CODEWITHSUNDEEP
vbaexcel
Batteredburrito
Batteredburrito

Reputation: 589

Increment Cell Values in a Range by 1 with VBA Excel

Im currently trying to implement an insert new row value and a automatic checkbox inserter.

I currently have the following code spread over different buttons and therefore different Subs. I have boldened the key information that i will need to increment by 1 cell. This will occur after clicking the "InsertNewBill" button.:

Private Sub InsertNewBill_Click()
    'I AM USING i TO STORE THE CELL INCREMENT, IT CURRENTLY DOES NOTHING**
    Dim i As Integer
    '**range("A30:AC30").Select**
    '**range("AC30").Activate**
    Selection.Copy
    Selection.Insert Shift:=xlDown
End Sub

Private Sub DeleteTickBoxes_Click()
    'Variables
    Dim c As CheckBox
    Dim CellRange As Range
    Dim cel As Range
    Set CellRange = ActiveSheet.Range("E7:**F30**")    
    'Delete Checkboxes within the specified range above on the ActiveSheet Only
    For Each c In ActiveSheet.CheckBoxes
        If Not Intersect(c.TopLeftCell, CellRange) Is Nothing Then
            c.Delete
        End If
    Next    
    'Insert New Checkboxes and Assign to a specified link cell using the offset
    For Each cel In CellRange
        'you can adjust left, top, height, width to your needs
        Set c = ActiveSheet.CheckBoxes.Add(cel.Left, cel.Top, 30, 6)
        With c   'Clears the textbox so it has no text
            .Caption = ""
            'Offset works by offsetting (Row offset, Column Offset) and accepts
            'positive for down/right and negative for left/up,
            'keep in not that the linked cells will automatically populate with true/false
            .LinkedCell = cel.Offset(0, -4).Address
        End With
    Next
    Call CentreCheckbox_Click
End Sub

I need all boldened values to increase by one. I.e from F30 to F31 and A30:AC30 to A31:AC31. This value also needs to be carried across from the InsertNewBill_Click sub to the DeleteTickBoxes_Click sub.

I assume i will need to remove the Private sub and possibly have a public integer variable? Im just not sure how to implement increasing only the number by 1 after each button click.

All your help is appreciated

Upvotes: 2

Views: 20072

Answers (1)

Vityata
Vityata

Reputation: 43575

Sub TestMe()

    Dim unionRange As Range
    Dim ws As Worksheet
    Set ws = Worksheets(1)

    With ws
        'as an alternative -> Set unionRange = ws.Range("A30:AC31")
        Set unionRange = Union(.Range("F30:F31"), .Range("A30:AC30"), .Range("A31:AC31"))
    End With

    Dim myCell As Range
    For Each myCell In unionRange
        If myCell.Font.Bold Then
            myCell = myCell + 1
        End If
    Next

End Sub
  • unionRange is a Union() of the 3 ranges;
  • myCell is a Range and used to loop through all the cells in unionRange;
  • myCell = myCell + 1 increments the value by 1.
  • If myCell.Font.Bold Then checks whether the cell is bold.

Upvotes: 3

Related Questions