Increment VBA int variable in-line - like i++ ?

S

shell_l_d

Board Regular
Joined
Jun 25, 2010
Messages
73
Is there a way to increment an Excel VBA integer variable like you can with C++, eg: iCol++ please - in-line?
Want to avoid hard-coded numbers to increment iCol:
Code: 
    Dim iRow As Integer, iCol As Integer
    iRow = 1
    iCol = 1
        ' HEADINGS
        .Cells(iRow, iCol + 1).Value = "Total"
        .Cells(iRow, iCol + 2).Value = "RespTm PASS"
        .Cells(iRow, iCol + 3).Value = "RespTm %"
This works, but double the number of lines, is there a way to do it in-line (combine 2 lines into 1)?
Code: 
        iCol = iCol + 1
        .Cells(iRow, iCol).Value = "Total"
        iCol = iCol + 1
        .Cells(iRow, iCol).Value = "RespTm PASS"
        iCol = iCol + 1
        .Cells(iRow, iCol).Value = "RespTm %"
This in-line attempt fails, causes error '1004 Application-defined or object-defined error':
Code: 
        .Cells(iRow, (iCol = iCol + 1)).Value = "Total"
        .Cells(iRow, (iCol = iCol + 1)).Value = "RespTm PASS"
        .Cells(iRow, (iCol = iCol + 1)).Value = "RespTm %"
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
No, there isn't, but ...

Code: 
.Cells(iRow, iCol + 1).Resize(,3).Value = Array("Total","RespTm PASS","RespTm %")
 
Upvote 0
Hmmm interesting thanks (speedy reply too :) )...
that'd be similar to (better than) using a string array & a for loop too (have >20 columns)...

I also need to fill the table with data for each of the headings, like doing this (again for >20 columns & more complex/longer formulas):)
Code: 
        .Cells(iRow + 1, iCol + 1).FormulaR1C1 = "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """)"
        .Cells(iRow + 1, iCol + 2).FormulaR1C1 = "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[Response Time],""PASS"")"
        .Cells(iRow + 1, iCol + 3).FormulaR1C1 = "=IF(tblOverall[[#This Row],[Total]]>0,tblOverall[[#This Row],[RespTm PASS]]/tblOverall[[#This Row],[Total]],0)"
 
Last edited:
Upvote 0
This works :)
Code: 
    Dim iRow As Integer, iCol As Integer, iTotalCols As Integer
        
    iRow = 1
    iCol = 1
    iTotalCols = 21
    '...etc...
        ' HEADINGS
        ' All & SLA & Non-SLA
        .Cells(iRow, iCol + 1).Resize(, iTotalCols).Value = _
            Array( _
                    "Total", _
                    "RespTm PASS", _
                    "RespTm %", _
                    "ResolTm PASS", _
                    "ResolTm %", _
                    "OVERALL PASS", _
                    "OVERALL %", _
                    "SLA Total", _
                    "SLA RespTm PASS", _
                    "SLA RespTm %", _
                    "SLA ResolTm PASS", _
                    "SLA ResolTm %", _
                    "SLA OVERALL PASS", _
                    "SLA OVERALL %", _
                    "Non-SLA Total", _
                    "Non-SLA RespTm PASS", _
                    "Non-SLA RespTm %", _
                    "Non-SLA ResolTm PASS", _
                    "Non-SLA ResolTm %", _
                    "Non-SLA OVERALL PASS", _
                    "Non-SLA OVERALL %" _
                  )
        Call CreateTable
        
        ' VALUES - Exclude FFS
        ' All & SLA & Non-SLA
        .Cells(iRow + 1, iCol + 1).Resize(, iTotalCols).FormulaR1C1 = _
            Array( _
                    "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """)", _
                    "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[Response Time],""PASS"")", _
                    "=IF(tblOverall[[#This Row],[Total]]>0,tblOverall[[#This Row],[RespTm PASS]]/tblOverall[[#This Row],[Total]],0)", _
      ...etc... 17 more forumulas
                    "=IF(tblOverall[[#This Row],[Non-SLA Total]]>0,tblOverall[[#This Row],[Non-SLA OVERALL PASS]]/tblOverall[[#This Row],[Non-SLA Total]],0)" _
                  )
 
Upvote 0
Bugger... I added line continuations in the code to make it more readable... however I need to add another 6 columns (headings & formula's) however I can only add 1 extra (25 line continuations) if I keep it in the format above, as I get the error: Too many line continuations.

Might have to split it into 3 lots (all, sla, non-sla).
 
Last edited:
Upvote 0
Why not put some of the headers on the same row?

Then you wouldn't need all that line continuation.

You could even 'group' related headers, something like this.
Code: 
arrValues = Array("Total", "RespTm PASS", "RespTm %", "ResolTm PASS", "ResolTm %", _
                        "OVERALL PASS", "OVERALL %", "SLA Total", _
                        "SLA RespTm PASS", "SLA RespTm %", _
                        "SLA ResolTm PASS", "SLA ResolTm %", _ 
                        "SLA OVERALL PASS", "SLA OVERALL %", _
                        "Non-SLA Total", "Non-SLA RespTm PASS",  "Non-SLA RespTm %", _
                        "Non-SLA ResolTm PASS", "Non-SLA ResolTm %", _
                        "Non-SLA OVERALL PASS", "Non-SLA OVERALL %")

PS Why line continuation before/after the closing/opening parantheses?
 
Upvote 0
Hi Norie...
1) because I like to make my code more readable/tidy & I also have formula's which are very long, not just headings, to do this with.
2) for readability, I could have left them out :)
 
Upvote 0
I understand about readability but a separate line for each item in quite a large array seems, to me anyway, a bit much.

Whenever I use Array with arrays that size I try and make them readable, but I also try to make sure they fit in 1 screen.

You could end up with a column of string values going down the centre with nothing to show what they actually are.

I wouldn't recommend using what I suggest for much else.

For example if it was a long SQL statement in the code, building it up by line by line using concatentation can be a good idea.

In that case it can actually help debugging the code - it's much easier to work with an SQL statement that's been broken down to it's individual parts/clauses.

By the way what are those formulas meant to do?
 
Upvote 0
This is what I ended up doing... :)
Code: 
...
          Dim iRow As Integer, iCol As Integer, iTotalCols As Integer
              
2         iRow = 1
3         iCol = 1
          
          ' Array of Headings
          ' All
4         Call AddToArray(sHeadings, "Total")
5         Call AddToArray(sHeadings, "RespTm PASS")
6         Call AddToArray(sHeadings, "RespTm %")
7         Call AddToArray(sHeadings, "ResolTm PASS")
8         Call AddToArray(sHeadings, "ResolTm %")
9         Call AddToArray(sHeadings, "OVERALL PASS")
10        Call AddToArray(sHeadings, "OVERALL %")
11        Call AddToArray(sHeadings, "ReWorks")
12        Call AddToArray(sHeadings, "CSR PASS")
          ' SLA
13        Call AddToArray(sHeadings, "SLA Total")
14        Call AddToArray(sHeadings, "SLA RespTm PASS")
15        Call AddToArray(sHeadings, "SLA RespTm %")
16        Call AddToArray(sHeadings, "SLA ResolTm PASS")
17        Call AddToArray(sHeadings, "SLA ResolTm %")
18        Call AddToArray(sHeadings, "SLA OVERALL PASS")
19        Call AddToArray(sHeadings, "SLA OVERALL %")
20        Call AddToArray(sHeadings, "SLA ReWorks")
21        Call AddToArray(sHeadings, "SLA CSR PASS")
          ' Non-SLA
22        Call AddToArray(sHeadings, "Non-SLA Total")
23        Call AddToArray(sHeadings, "Non-SLA RespTm PASS")
24        Call AddToArray(sHeadings, "Non-SLA RespTm %")
25        Call AddToArray(sHeadings, "Non-SLA ResolTm PASS")
26        Call AddToArray(sHeadings, "Non-SLA ResolTm %")
27        Call AddToArray(sHeadings, "Non-SLA OVERALL PASS")
28        Call AddToArray(sHeadings, "Non-SLA OVERALL %")
29        Call AddToArray(sHeadings, "Non-SLA ReWorks")
30        Call AddToArray(sHeadings, "Non-SLA CSR PASS")
          
          ' Array of Formulas
          ' All
31        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """)")
32        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[Response Time],""PASS"")")
33        Call AddToArray(sFormulas, "=IF(tblOverall[[#This Row],[Total]]>0,tblOverall[[#This Row],[RespTm PASS]]/tblOverall[[#This Row],[Total]],0)")
34        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[Resolution Time],""PASS"")")
35        Call AddToArray(sFormulas, "=IF(tblOverall[[#This Row],[Total]]>0,tblOverall[[#This Row],[ResolTm PASS]]/tblOverall[[#This Row],[Total]],0)")
36        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[OVERALL],""PASS"")")
37        Call AddToArray(sFormulas, "=IF(tblOverall[[#This Row],[Total]]>0,tblOverall[[#This Row],[OVERALL PASS]]/tblOverall[[#This Row],[Total]],0)")
38        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>6"",tblGlobalData[ReWork],""Yes"")")
39        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>6"",tblGlobalData[Customer Satisfaction Rating],""Passed"")")
          ' SLA
40        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[SLA],""<>"")")
41        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[SLA],""<>"",tblGlobalData[Response Time],""PASS"")")
42        Call AddToArray(sFormulas, "=IF(tblOverall[[#This Row],[SLA Total]]>0,tblOverall[[#This Row],[SLA RespTm PASS]]/tblOverall[[#This Row],[SLA Total]],0)")
43        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[SLA],""<>"",tblGlobalData[Resolution Time],""PASS"")")
44        Call AddToArray(sFormulas, "=IF(tblOverall[[#This Row],[SLA Total]]>0,tblOverall[[#This Row],[SLA ResolTm PASS]]/tblOverall[[#This Row],[SLA Total]],0)")
45        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[SLA],""<>"",tblGlobalData[OVERALL],""PASS"")")
46        Call AddToArray(sFormulas, "=IF(tblOverall[[#This Row],[SLA Total]]>0,tblOverall[[#This Row],[SLA OVERALL PASS]]/tblOverall[[#This Row],[SLA Total]],0)")
47        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[SLA],""<>"",tblGlobalData[ReWork],""Yes"")")
48        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[SLA],""<>"",tblGlobalData[Customer Satisfaction Rating],""Passed"")")
          ' Non-SLA
49        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[SLA],"""")")
50        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[SLA],"""",tblGlobalData[Response Time],""PASS"")")
51        Call AddToArray(sFormulas, "=IF(tblOverall[[#This Row],[Non-SLA Total]]>0,tblOverall[[#This Row],[Non-SLA RespTm PASS]]/tblOverall[[#This Row],[Non-SLA Total]],0)")
52        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[SLA],"""",tblGlobalData[Resolution Time],""PASS"")")
53        Call AddToArray(sFormulas, "=IF(tblOverall[[#This Row],[Non-SLA Total]]>0,tblOverall[[#This Row],[Non-SLA ResolTm PASS]]/tblOverall[[#This Row],[Non-SLA Total]],0)")
54        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[SLA],"""",tblGlobalData[OVERALL],""PASS"")")
55        Call AddToArray(sFormulas, "=IF(tblOverall[[#This Row],[Non-SLA Total]]>0,tblOverall[[#This Row],[Non-SLA OVERALL PASS]]/tblOverall[[#This Row],[Non-SLA Total]],0)")
56        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[SLA],"""",tblGlobalData[ReWork],""Yes"")")
57        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[SLA],"""",tblGlobalData[Customer Satisfaction Rating],""Passed"")")
58        With ThisWorkbook.Worksheets("Overall")
          
59            .Select
60            Range("A1").Select
              
              ' Fill column A with Header & Unique Values from tblGlobalData tables RegOff column.
61            Sheets("GlobalData").Range("tblGlobalData[[#Headers],[#Data],[RegOff]]").AdvancedFilter _
                             Action:=xlFilterCopy, CopyToRange:=Range("A1"), Unique:=True
              
              ' HEADINGS
62            iTotalCols = UBound(sHeadings) + 1
63            .Cells(iRow, iCol + 1).Resize(, iTotalCols).Value = sHeadings
64            Call CreateTable
              
              ' VALUES - Exclude FFS
65            On Error Resume Next
66            iTotalCols = UBound(sFormulas) + 1
67            .Cells(iRow + 1, iCol + 1).Resize(, iTotalCols).FormulaR1C1 = sFormulas
68            On Error GoTo Error_In_ExtraOverall
...
 
Upvote 0
Are you using a sub/function to add each item to the array(s)?
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Excel to internal application
Replies
2
Views
473
pure vito
P
R
  • Question Question
Formatting using VBA
Replies
0
Views
553
RandyD123
R
S
  • Solved
VBA Parse Data. (do not want title to copy over Every time)
Replies
2
Views
702
Scifo
S
S
  • Solved
Parse Data - Paste Range to new sheets as values only.
Replies
9
Views
799
Scifo
S
T
  • Question Question
Resetting Row Counter Loop?
Replies
2
Views
843
TycExcel
T

Forum statistics

Threads
1,224,550
Messages
6,179,459
Members
452,915
Latest member
hannnahheileen

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top