Excel VBA Increment Range by 1 across Subs

Question

Due to the last question not being very clear on my part. I am novice and will need a bit of guidence in how to implement any answers.

I currently have the following code spread over different buttons and therefore different Subs. For the sake of clarity, I have seperated the ranges that i need to increment by 1 from the code and boldened the values i need to count up by 1 each button press. I appreciate this breaks the format, I apologise.

The number increase 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

Presuming that all your code is in the same module? declare a public variable at the top of the module (i.e. `Public iRowNum as Long`). You can set this variable to a row number and use it across your code (i.e. `Range("A" & iRowNum & ":AC" & iRowNum).Copy`) — Zac, Jul 25 '18 at 15:16
@Zac Thank you! I will see what i can implement. All code is in the same Module. — Batteredburrito, Jul 25 '18 at 15:23

score 2 · Answer 1 · answered Jul 25 '18 at 15:23

2

You should try to stop using Select and Activate - in this case you can just Copy the Range:

Range(cells(30,"A"),cells(30,"AC")).Copy

To add i as an offset, you could use:

Range(cells(30+i,"A"),cells(30+i,"AC")).Copy

Or you could use:

Range(cells(30,"A"),cells(30,"AC")).Offset(i,0).Copy

To make i work across other Subs in the Workbook, declare it Public.

Unless you're sure the value will remain low, I'd suggest declaring it as a Long, not an Integer.

For the Set CellRange = ActiveSheet.Range("E7:F30") setting, you could use:

Set CellRange = ActiveSheet.Range("E7:F" & 30 + i)

answered Jul 25 '18 at 15:23

CLR

11,284
1
11
29

Thank you for the input CLR. is there any reason why using select and Activate is a bad option? Unfortunately, that was all recorded using macro input so may be why it was not ideal. Apologies. I will try to implement your suggestions and see how it goes :) – Batteredburrito Jul 25 '18 at 15:31
1

Have a read of: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – CLR Jul 25 '18 at 15:37
I will, thank you for all your help with this. Thank you for all your support :) – Batteredburrito Jul 25 '18 at 15:38
1

Reason why you don't want to use `Select` and `Activate`.. well simply because they are pointless when it comes to VBA. Because you already have the object, you can reference the cell\range\sheet directly and leave VBA to sort the rest out for you (power of using objects). Also because they take time and resource for no apparent reason. One of the most pointless functionality in VBA – Zac Jul 25 '18 at 15:39

QHarr · Accepted Answer · 2018-07-25T15:28:22.753

1

Use a public variable and concatenate that into your address. Initialize the value as, say 30, in the workbook open event.

ThisWorkbook:

Option Explicit
Public i As Long
Private Sub Workbook_Open()
    i = 30
End Sub

Standard module:

Option Explicit
Private Sub button2_Click()
    ThisWorkbook.i = ThisWorkbook.i + 1
    Debug.Print ActiveSheet.Range("E" & ThisWorkbook.i & ":F" & ThisWorkbook.i).Address
End Sub

edited Jul 25 '18 at 15:28

answered Jul 25 '18 at 15:18

QHarr

83,427
12
54
101

This is amazing! Im going to see if i can squeeze this into my worksheet now. Ill report back – Batteredburrito Jul 25 '18 at 15:24
How would i implement the `i` value in this? `range("A30:AC30").Select`. The way im looking at this needs to be `range("A & ThisWorkbook.i : AC & ThisWorkbook.i ").Address` however you cannot nest that inside the " " marks – Batteredburrito Jul 25 '18 at 15:26
Thank you for the update. It seems thats cracked it. Im going to implement your suggestions and @CLRs to help tidy it up. Out of interest. The `i` value i assume sets itself to 30 whenever the workbook is opened right? Or is that stored permanently? – Batteredburrito Jul 25 '18 at 15:34
Im getting the following error when trying to run the "InsertNewBill" button. Any advice: "Method or data member not found" for `ThisWorkbook.i + 1` – Batteredburrito Jul 25 '18 at 15:41
Have you put the ThisWorkbook code in the ThisWorkbook not in a standard module? – QHarr Jul 25 '18 at 16:26
That solved it, thank you. I feel stupid but thank you so much for this – Batteredburrito Jul 25 '18 at 17:18
Is there any way of getting this value the value of i to store permanently when saving and re-opening the sheet? – Batteredburrito Jul 25 '18 at 17:39
The latest i? Write it to a sheet cell on workbook close event and read it from the same cell in the workbook open event. – QHarr Jul 25 '18 at 17:40
Done, you sir are the best! Thank you – Batteredburrito Jul 25 '18 at 17:46
No worries. Glad it helped. – QHarr Jul 25 '18 at 17:46

score 0 · Answer 3 · answered Jul 25 '18 at 19:06

For those interested. Here is the VBA that i ended up using as it fits my purpose.

Sub InsertNewBill_Click()
Dim rngCopy As range
Dim rngPaste As range
Dim clearCell As range

i = Cells(30, 1) + 1
    Cells(30, 1) = i

ActiveSheet.range("A" & i & ":AD" & i).Insert

Set rngPaste = range("A" & i & ":AD" & i)
Set rngCopy = ActiveSheet.range("A" & i - 1 & ":AD" & i - 1)

rngCopy.Copy
rngPaste.PasteSpecial Paste:=xlPasteAll

With ActiveWorkSheet
    Cells(i, 1).ClearContents
End With

Call DeleteTickBoxes_Click

End Sub

*

Private Sub DeleteTickBoxes_Click()

'Variables
Dim c As CheckBox
Dim CellRange As range
Dim cel As range

i = Cells(30, 1)

Set CellRange = ActiveSheet.range("F" & i & ":G" & i)

'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
    Set c = ActiveSheet.CheckBoxes.Add(cel.Left, cel.Top, 30, 6) 'you can adjust left, top, height, width to your needs
    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

Excel VBA Increment Range by 1 across Subs

3 Answers3