Run code in last column

[jocker_boy]

Hello,

I'm trying to run the code in last column.
Part of the code is perfect. Until this line it works good: Range(Cells(6, lc), Cells(lr, lc)).FormulaR1C1 = "=R[0]C[-1]*" & Application.ConvertFormula("$J6", xlA1, xlR1C1)

Then in second part of the code i would like to replace the range "EE" with "lc" variable. But i don't know how.

Thanks for the help.
Gonçalo

Sub test()

'Populate Auxiliar Columns
    Dim lr As Long
    Dim lc As Long
  
'Group Columns
    ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=8

'   Find last row in column C with data
    lr = Cells(Rows.Count, "C").End(xlUp).row
    
'   Find last column in row 5 with data
    lc = Cells(5, columns.Count).End(xlToLeft).Column

'Ungroup Columns
    ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1

    Range(Cells(6, lc + 3), Cells(6, lc + 3)).Select
    Range(Cells(6, lc), Cells(lr, lc)).FormulaR1C1 = "=R[0]C[-1]*" & Application.ConvertFormula("$J6", xlA1, xlR1C1)


Dim r As Long, r2 As Long, last_row As Long
Dim next_row As Long, current_len As Long, test_len As Long
Dim Rng As String

With ActiveSheet

last_row = .Cells(Rows.Count, 1).End(xlUp).row

For r = 6 To lr
    next_row = r + 1

    If .Range("B" & next_row) > .Range("B" & r) Then
       current_len = .Range("B" & r)
      
       'create range
       For r2 = r + 1 To last_row
            test_len = .Range("B" & r2)
            If current_len >= test_len Then
                Rng = "EE" & r + 1 & ":" & "EE" & r2 - 1
                Exit For
            End If
        Next
    
        .Range("EE" & r).Formula = "=SUBTOTAL(9," & Rng & ")"
    End If
    
Next

End With

End Sub

 

[Joe4]

Change:

.Range("EE" & r)

to

.Cells(r, lc)

 

[jocker_boy]

Change:

.Range("EE" & r)

to

.Cells(r, lc)

Thanks you very much.

 

[Joe4]

You are welcome.

Using "Cells" instead of "Range" is very helpful here, as with Cells, you can use letters or numbers to represent the column.
The format of Cells is:
Cells(row, column)

 

[jocker_boy]

You are welcome.

I forgot to check one line.

How can i change this:

Rng = "EE" & r + 1 & ":" & "EE" & r2 - 1

To use the variable of last column "lc"?

Thanks.

 

[Joe4]

I would not use a string variable like that.
You can use Cells imbedded in Range, like this:

Range(Cells(r + 1, lc), Cells(r2 - 1, lc))

 

[jocker_boy]

I would not use a string variable like that.
You can use Cells imbedded in Range, like this:

Range(Cells(r + 1, lc), Cells(r2 - 1, lc))

It's not working:

Sub test()

'Populate Auxiliar Columns
    Dim lr As Long
    Dim lc As Long
 
'   Find last row in column C with data
    lr = Cells(Rows.Count, "C").End(xlUp).row
  
'   Find last column in row 5 with data
    lc = Cells(5, columns.Count).End(xlToLeft).Column

    Range(Cells(6, lc + 3), Cells(6, lc + 3)).Select
    Range(Cells(6, lc), Cells(lr, lc)).FormulaR1C1 = "=R[0]C[-1]*" & Application.ConvertFormula("$J6", xlA1, xlR1C1)


Dim r As Long, r2 As Long, last_row As Long
Dim next_row As Long, current_len As Long, test_len As Long
Dim Rng As String

With ActiveSheet

last_row = .Cells(Rows.Count, 1).End(xlUp).row

For r = 6 To last_row
    next_row = r + 1

    If .Range("B" & next_row) > .Range("B" & r) Then
       current_len = .Range("B" & r)
    
       'create range
       For r2 = r + 1 To last_row
            test_len = .Range("B" & r2)
            If current_len >= test_len Then
                Rng = Range(Cells(r + 1, lc), Cells(r2 - 1, lc))
                Exit For
            End If
        Next
  
        .Cells(r, lc).Formula = "=SUBTOTAL(9," & Rng & ")"
    End If
  
Next

End With

End Sub

If i replace with your code, it wont work.
I get the error: Run-time error '13': Type mismatch

 

[Joe4]

I don't think you quite understood what I was suggesting. I was advising you to get rid of Rng altogether.
But I see how you are trying to use it now later in your code.

In that case, try this:

Rng = Range(Cells(r + 1, lc), Cells(r2 - 1, lc)).Address

 

[jocker_boy]

I don't think you quite understood what I was suggesting. I was advising you to get rid of Rng altogether.
But I see how you are trying to use it now later in your code.

In that case, try this:

Rng = Range(Cells(r + 1, lc), Cells(r2 - 1, lc)).Address

Sorry, i understand but i don't know to much of VBA. So i didn't know the alternative.

Thanks for your help.

 

[Joe4]

You are welcome.