Using Last Column Variable name instead of Column reference

jan.g · Jun 23rd 2017

Hello All,
This query continues on from a previous post: http://www.ozgrid.com/forum/showthread.php?t=204475 which has been answered perfectly.

The position of the last column and the number of columns that are totalled may change.
Sumifs Range: Cells(i,11), Cells (i,[COLOR="#FF0000"]20[/COLOR]) - i would like to replace 20 with the last column
Paste Range: Range("U" & i & ":Y" & i) - this would be the last column + 1 to totrange (or last column +5 cols)
I have tried posting the variable name into the code (which was suggested elsewhere) but get an error on both lines.

Code

Sub Totals()
     
    Dim lrow As Long
    Dim i As Long
    Dim j As Integer
    Dim lcol As Long
    Dim totrange As Long
    lrow = Cells(Rows.Count, 1).End(xlUp).Row
    lcol = Range("z49").End(xlToLeft).Column
    totrange = lcol + 5


    For i = 11 To lrow
        For j = lcol To totrange
            If Cells(i, 11) <> "Units" Then
                Cells(i, j) = WorksheetFunction.SumIfs(Range(Cells(i, 11), Cells(i, 20)), Range("K10:T10"), Cells(10, j))
            Else
                Cells(i, j) = Cells(i, j - 5)
            End If
        Next j
        Range("k" & i & ":O" & i).Copy
        Range("U" & i & ":Y" & i).PasteSpecial Paste:=xlPasteFormats
        If Range("l" & i) = 0 Or Len(Range("l" & i)) = 0 Then Range("K" & i & ":O" & i) = ""
    Next i

Display More

Many thanks for your help.

**KjBox** · Jun 23rd 2017

Re: Using Last Column Variable name instead of Column reference

Try this

Code

Sub Totals()
     
    Dim lrow As Long
    Dim i As Long
    Dim j As Integer
    Dim lcol As Long
    Dim totrange As Long
    lrow = Cells(Rows.Count, 1).End(xlUp).Row
    lcol = Range("z49").End(xlToLeft).Column
    totrange = lcol + 5
     
    For i = 11 To lrow
        For j = lcol To totrange
            If Cells(i, 11) <> "Units" Then
                Cells(i, j) = WorksheetFunction.SumIfs("Range(Cells(i, 11), Cells(i, " & lrow & ")), Range(""K10:T10""), Cells(10, j)")
            Else
                Cells(i, j) = Cells(i, j - 5)
            End If
        Next j
        Range("k" & i & ":O" & i).Copy
        Cells(lrow + 1, i).Resize(, 5).PasteSpecial Paste:=xlPasteFormats
        If Range("l" & i) = 0 Or Len(Range("l" & i)) = 0 Then Range("K" & i & ":O" & i) = ""
    Next i

Display More

jan.g · Jun 23rd 2017

Re: Using Last Column Variable name instead of Column reference

Hello KjBox,

Thank you for the fabulously swift reply. I have run the code but it stops on the & after lrow with a Type Mismatch Error.

Code

Cells(i, j) = WorksheetFunction.SumIfs("Range(Cells(i, 11), Cells(i, " & lrow & ")), Range(""K10:T10""), Cells(10, j)")

I have the paste working by switching the row and col ref to Cells(i, icol +1)

Many thanks
Jan

**KjBox** · Jun 23rd 2017

Re: Using Last Column Variable name instead of Column reference

I have had a look at your original thread. You have made numerous structural changes to your worksheet. Can you please attach the new workbook.

Are you trying to make the code dynamic to account for any number of "Stages"?

jan.g · Jun 23rd 2017

Re: Using Last Column Variable name instead of Column reference

Hi KjBox,

The original post was an example of what I was trying to achieve. But for my work file I need to make it more dynamic. I have only very slightly modified the code to update some column and cell values as there were a few more columns before the data columns and a few more rows in the header.
Yes I am trying to make the code more dynamic to account for additional stages which are added through a checkbox. I can update the original file to match the actual file if required. Thanks for your help. It is awesome.

Cheers Jan

jan.g · Jun 23rd 2017

Re: Using Last Column Variable name instead of Column reference

Hello Again,
Attached is the spreadsheet in the same format as my data. Columns A-J are mostly text and static data. I have copied the new code and run but still gives a Mismatch error.
PS I am working on Mac Excel 2016.
Cheers Jan

**KjBox** · Jun 23rd 2017

Re: Using Last Column Variable name instead of Column reference

One change to the code that you have made, which I do not understand, is that you now start at Row 11 not Row 4.

An updated file that matches the actual file would help.

I am looking at doing an alternative method which would make the code completely dynamic.

**KjBox** · Jun 23rd 2017

Re: Using Last Column Variable name instead of Column reference

Thanks for the file.

Quote

Columns A-J are mostly text and static data

Do you mean Columns A to I? Your data that needs totalling starts in Column J on the sample workbook. Or should data for totalling start in Column K?

What about Rows 1 to 10, do they contain text too?

jan.g · Jun 23rd 2017

Re: Using Last Column Variable name instead of Column reference

Hello KjBox,

My apologies. I should have shifted 1 more column to the right. Actual data starts summing from column K. Rows 1 to 10 are headers and logo.
To upload actual file, I would need to desensitise the data which might take a little bit of time, but I am happy to do this if required. Pls let me know if that would be better.

Cheers
Jan

**KjBox** · Jun 23rd 2017

Re: Using Last Column Variable name instead of Column reference

All should be OK with the sample file as it is (modified for data starting in Column K). If not then I will let you know.

**KjBox** · Jun 23rd 2017

Re: Using Last Column Variable name instead of Column reference

Try the attached.

I have made K10:O10 a new defined range (named "Headers"), this is used by the code to add the headers to the Totals columns in appropriate rows.

I also added a button to the Totals Columns header, click it to populate the Totals Coumns. The code first clears anything in the Totals columns so you can update/edit the data then click the button again, no need to manually clear any Totals data.

Code assigned to the button is:

Code

Sub Totals()
    Dim x, i As Long, ii As Long, iii As Long, iv As Long, lRow As Long, lCol As Long
    
    With ActiveSheet
        lRow = .Cells(.Rows.Count, 12).End(-4162).Row - 10
        lCol = .Cells(8, .Columns.Count).End(-4159).Column - 6
        .Cells(11, lCol + 6).Resize(lRow + 50, 5).Clear
        x = .Cells(11, 11).Resize(lRow, lCol)
        For i = 1 To UBound(x, 1)
            If x(i, 1) <> "Units" Then
                For ii = 1 To UBound(x, 2) - 5 Step 5
                    iv = 5
                    For iii = ii To ii + 4
                        iv = iv - 1
                        If x(i, iii) <> "" Then x(i, UBound(x, 2) - (iv)) = x(i, UBound(x, 2) - (iv)) + x(i, iii)
                        If x(i, UBound(x, 2) - (iv)) = 0 Then x(i, UBound(x, 2) - (iv)) = Empty
                    Next
                Next
            End If
        Next
        .Cells(11, 11).Resize(UBound(x, 1), UBound(x, 2)) = x
        Application.ScreenUpdating = 0
        For i = 1 To UBound(x, 1)
            If x(i, 1) = "Units" Then [Headers].Copy .Cells(i + 10, lCol + 6)
        Next
        .Cells(10, 11).Resize(UBound(x, 1) + 1, 5).Copy: .Cells(10, lCol + 6).PasteSpecial -4122
        With Application
            .CutCopyMode = False
            .Goto .Cells(8, lCol + 6)
        End With
    End With
    
End Sub

Display More

**KjBox** · Jun 23rd 2017

Re: Using Last Column Variable name instead of Column reference

To answer the question you asked in your original post regarding IF statements.

If the result of an IF statement is included in the same line of code as the IF statement criteria then "End If" is not needed.

So

Code

If i > 0 Then
    s = "Abc"
End If

can be written as

Code

If i > 0 Then s = "Abc"

Also,

Code

If i > 0 Then
    s = "Abc"
Else
    s = "Xyz"
End If

can be written as

Code

If i > 0 Then s = "Abc" Else s = "Xyz"

Alternatively the last example could be written as

Code

s = IIf(i > 0, "Abc", "Xyz")

The IIF Function is a special VBA Function which is like the IF worksheet function, they both have 3 elements: Criteria, Value if True, Value if False

jan.g · Jun 24th 2017

Re: Using Last Column Variable name instead of Column reference

Good Afternoon KjBox,
Thank you so much for the script. It works perfectly. It may take me a little time to understand the code as I am quite new to VBA but it's all part of the learning journey. This site is a fabulous resource and I would have been struggling for ages without your support.
Once again, thank you for your time and patience.
Regards
Jan
Thanks also for the IF statement explanation.

**KjBox** · Jun 24th 2017

Re: Using Last Column Variable name instead of Column reference

You're welcome.

Using Last Column Variable name instead of Column reference

Participate now!

Share