CODEWITHSUNDEEP
excelvba
user2094096
user2094096

Reputation: 75

Need assistance with Macro - Excel

I have a =SUMIF formula that needs to be adjusted each time the macro is run. The 3rd part of the formula, the sum range, needs to shift over 1 column each time. I will be running this once a month (each column represents 1 month)

I got some advice to use a the LngCnt variable which worked well. But now I was forced to add 3 more sections to the same worksheet that all use the =SUMIF formula, all need to be adjusted each time the macro is run, but have different sum ranges (so they are pulling from different columns)

I tried to run the same LngCnt variable but quickly ran into the issue where the count is saved so the 2nd section's count started at 2 before the calculation. I could adjust the starting point so that +2 gets me the appropriate column, but the problem is that I need to do this every month. Since the count gets reset every time I close the workbook, it would not run properly when I go and run it in the 2nd month.

Here is the link of my original post: Excel-Macro Help: LngCnt

Here is a part of the code (for 2 of the 4 sections)

    ActiveCell.FormulaR1C1 = "=SUMIF('Worksheet1'!R5C1:R159C1,RC1,'Worksheet1'!R5C" & 13 + LngCnt & ":R159C" & 13 + LngCnt & ")"
LngCnt = LngCnt + 1
Range("B13").Select
Selection.AutoFill Destination:=Range("B13:B17"), Type:=xlFillDefault
Range("B13:B17").Select
Range("B23").Select

ActiveCell.FormulaR1C1 = _
    "=SUMIF('Worksheet2'!R3C6:R47C6,RC1,'Worksheet2'!R3C" & 16 + LngCnt & ":R47C" & 16 + LngCnt & ")"
LngCnt = LngCnt + 2
Range("B23").Select
Selection.AutoFill Destination:=Range("B23:B32"), Type:=xlFillDefault
Range("B23:B32").Select

Update

> Public LngCnt As Long
Sub Actual()
'
' Actual Macro
'
LngCount = ActiveWorkbook.Names("state").Value

    ActiveCell.FormulaR1C1 = "=SUMIF('Workbook1'!R5C1:R159C1,RC1,'Workbook1'!R5C" & 13 + LngCnt & ":R159C" & 13 + LngCnt & ")"
    LngCnt = LngCnt + 1
    Range("B13").Select
    Selection.AutoFill Destination:=Range("B13:B17"), Type:=xlFillDefault
    Range("B13:B17").Select
ActiveWorkbook.Names("state").Value = LngCnt
End Sub

Upvotes: 1

Views: 234

Answers (2)

David Zemens
David Zemens

Reputation: 53663

Another way of keeping this variable in your workbook, without having to assign it directly in to a cell, is to use a named variable. From the Formula ribbon, open the names manager, and add a new name called "state". You can give it any value at this point. We will modify your VBA to update it.

Names Manager

Then, in your code, you can assign a value to this "variable" by doing:

ActiveWorkbook.Names("state").Value = LngCnt

You can retrieve the value by:

LngCount = ActiveWorkbook.Names("state").Value

Update

Public LngCnt As Long
Sub Actual()
'
' Actual Macro
Dim startRange as Range: Set startRange = Range("B13")
    LngCnt = Replace(ActiveWorkbook.Names("state").Value, "=", "")
    startRange.FormulaR1C1 = "=SUMIF('Workbook1'!R5C1:R159C1,RC1,'Workbook1'!R5C" & 13 + LngCnt & ":R159C" & 13 + LngCnt & ")"
    LngCnt = LngCnt + 1
    startRange.AutoFill Destination:=Range("B13:B17"), Type:=xlFillDefault
    'Range("B13:B17").Select  '## I comment this line because I don't think its necessary.
    ActiveWorkbook.Names("state").Value = LngCnt

End Sub

After running the macro twice, the saved value of Name("state") is 2. I save & close the workbook, re-open it, and confirm it is still 2:

Screenshot

Upvotes: 4

exussum
exussum

Reputation: 18578

to keep a "state" one of the best ways is to have a cell in which the state is stored and then

sheets("state").range("a1").Value

to get the value and

sheets("state").range("a1").Value = 1

to set the value to one.

You dont need a new sheet if you have a space in the original sheet, Just update it accordingly

Upvotes: 1

Related Questions