How to combine last column and last row in variant?

[288enzo]

Greetings,

I need some assistance with incorporating last column into a formula.

The output is - =IF(J31=0,"-",COUNTIFS(Report!$C$2:$C$1106,C31,Report!$H$2:$H$1106,"Specialist",Report!$N$2:$N$1106,3))

My trouble is that column N can move depending on the number of courses available to be completed.

The VB code for the above is

    Set area = Sheets("Report").Range("A2:A" & Sheets("Report").Range("A" & Rows.Count).End(xlUp).Row)
    Set subarea = Sheets("Report").Range("B2:B" & Sheets("Report").Range("A" & Rows.Count).End(xlUp).Row)
    Set code = Sheets("Report").Range("C2:C" & Sheets("Report").Range("A" & Rows.Count).End(xlUp).Row)
    Set job = Sheets("Report").Range("H2:H" & Sheets("Report").Range("A" & Rows.Count).End(xlUp).Row)
    Set total = Sheets("Report").Range("N2:N" & Sheets("Report").Range("A" & Rows.Count).End(xlUp).Row) ' total is what can move depending on the total number of courses

    Range("F2") = "=if(b2="""",countifs(" & area.Address(external:=True) & ",A2," & job.Address(external:=True) & ",""<>Specialist"")-countifs(" & area.Address(external:=True) & ",A2," & job.Address(external:=True) & ",""<>Specialist""," & total.Address(external:=True) & ","">0""),countifs(" & subarea.Address(external:=True) & ",B2," & job.Address(external:=True) & ",""<>Specialist"")-countifs(" & subarea.Address(external:=True) & ",B2," & job.Address(external:=True) & ",""<>Specialist""," & total.Address(external:=True) & ","">0""))"

Has_Has_Not_Taken_Report_04202022.xlsx.xls
	I	J	K	L	M	N	O
1	619155M5B	619155M6B	619155M7B	619156B	619158B	VCIWB01SSFIA22	Total
2	1	1	1	1	1	1	6
Report

Cell Formulas
Range	Formula
I2	I2	=COUNTIFS(Sheet0!$H$4:$H$6633,G2,Sheet0!$A$4:$A$6633,Report!$I$1,Sheet0!$D$4:$D$6633,"Completed")
J2	J2	=COUNTIFS(Sheet0!$H$4:$H$6633,G2,Sheet0!$A$4:$A$6633,Report!$J$1,Sheet0!$D$4:$D$6633,"Completed")
K2	K2	=COUNTIFS(Sheet0!$H$4:$H$6633,G2,Sheet0!$A$4:$A$6633,Report!$K$1,Sheet0!$D$4:$D$6633,"Completed")
L2	L2	=COUNTIFS(Sheet0!$H$4:$H$6633,G2,Sheet0!$A$4:$A$6633,Report!$L$1,Sheet0!$D$4:$D$6633,"Completed")
M2	M2	=COUNTIFS(Sheet0!$H$4:$H$6633,G2,Sheet0!$A$4:$A$6633,Report!$M$1,Sheet0!$D$4:$D$6633,"Completed")
N2	N2	=COUNTIFS(Sheet0!$H$4:$H$6633,G2,Sheet0!$A$4:$A$6633,Report!$N$1,Sheet0!$D$4:$D$6633,"Completed")
O2	O2	=SUM(I2:N2)

The formula worked perfectly when there were 5 courses, now there are six and I didn't account for that. Without having to change

    Set total = Sheets("Report").Range("N2:N" & Sheets("Report").Range("A" & Rows.Count).End(xlUp).Row)

to

    Set total = Sheets("Report").Range("O2:O" & Sheets("Report").Range("A" & Rows.Count).End(xlUp).Row) ' total is what can move depending on the total number of courses

Can I use offset or

lc = Range("I2").End(xlToRight)

somehow?

Thank you

 

[Fluff]

How about

    Dim UsdRws As Long
    With Sheets("Report")
      UsdRws = .Range("A" & Rows.Count).End(xlUp).Row
      Set area = .Range("A2:A" & UsdRws)
      Set subarea = .Range("B2:B" & UsdRws)
      Set code = .Range("C2:C" & UsdRws)
      Set job = .Range("H2:H" & UsdRws)
      Set Total = .Cells(1, .Columns.Count).End(xlToLeft).Offset(1).Resize(UsdRws - 1)
    End With

    Range("F2") = "=if(b2="""",countifs(" & area.Address(external:=True) & ",A2," & job.Address(external:=True) & ",""<>Specialist"")-countifs(" & area.Address(external:=True) & ",A2," & job.Address(external:=True) & ",""<>Specialist""," & Total.Address(external:=True) & ","">0""),countifs(" & subarea.Address(external:=True) & ",B2," & job.Address(external:=True) & ",""<>Specialist"")-countifs(" & subarea.Address(external:=True) & ",B2," & job.Address(external:=True) & ",""<>Specialist""," & Total.Address(external:=True) & ","">0""))"

 

[288enzo]

How about

    Dim UsdRws As Long
    With Sheets("Report")
      UsdRws = .Range("A" & Rows.Count).End(xlUp).Row
      Set area = .Range("A2:A" & UsdRws)
      Set subarea = .Range("B2:B" & UsdRws)
      Set code = .Range("C2:C" & UsdRws)
      Set job = .Range("H2:H" & UsdRws)
      Set Total = .Cells(1, .Columns.Count).End(xlToLeft).Offset(1).Resize(UsdRws - 1)
    End With

    Range("F2") = "=if(b2="""",countifs(" & area.Address(external:=True) & ",A2," & job.Address(external:=True) & ",""<>Specialist"")-countifs(" & area.Address(external:=True) & ",A2," & job.Address(external:=True) & ",""<>Specialist""," & Total.Address(external:=True) & ","">0""),countifs(" & subarea.Address(external:=True) & ",B2," & job.Address(external:=True) & ",""<>Specialist"")-countifs(" & subarea.Address(external:=True) & ",B2," & job.Address(external:=True) & ",""<>Specialist""," & Total.Address(external:=True) & ","">0""))"

This looks fantastic, and taught me a few other things, thank you.

So I understand this -

      Set Total = .Cells(1, .Columns.Count).End(xlToLeft).Offset(1).Resize(UsdRws - 1)

.Cells(1, .Columns.Count).End(xlToLeft) Is looking at one row down then starts at the last column and looks left for the first column with data (column O), and stops on column P.
Offset(1).Resize(UsdRws - 1) says to then select column O (UsdRws -1) starting on the 2nd row Offset(1).

Does that sound right?

Thanks again!

 

[Fluff]

No quite, it finds the last used column in row (in this case O) , then offsets down one row to O2 & then resizes it to the last used row

 

[288enzo]

No quite, it finds the last used column in row (in this case O) , then offsets down one row to O2 & then resizes it to the last used row

Why does UsdRws need to be resized? UsedRws works properly in all the other variants and they're not resized. I don't understand why in this particular instance Resize is used.

As an example

=COUNTIFS(Report!$C$2:$C$1105,C2,Report!$O$2:$O$1106,6)

Set total = .Cells(1, .Columns.Count).End(xlToLeft).Offset(1).Resize(UsdRws) = 1106

Set code = .Range("C2:C" & UsdRws) = 1105

Thank you!

 

[Fluff]

UsdRws is not being resized, it's being used to resize the range from row 2 to the last last used row.
However you have missed of the -1, which is why they are different sizes.

 

[288enzo]

UsdRws is not being resized, it's being used to resize the range from row 2 to the last last used row.
However you have missed of the -1, which is why they are different sizes.

I had done that on purpose as an experiment to see how removing -1 would impact the end result.

Is UsdRws not already set as the current range of rows based on A? That would mean A1:A1105.

And so I was thinking that why is it I would need to use resize if it's already using data to row 1105.

 

[Fluff]

This Set Total = .Cells(1, .Columns.Count).End(xlToLeft).Offset(1) will return O2 so it needs to be resized to give O2:O1105, but as O2 is on row 2 & not row 1 the UsdRws needs to be reduced by 1

 

[288enzo]

This Set Total = .Cells(1, .Columns.Count).End(xlToLeft).Offset(1) will return O2 so it needs to be resized to give O2:O1105, but as O2 is on row 2 & not row 1 the UsdRws needs to be reduced by 1

Got it, that makes perfect sense. Thank you very much for the explanation!

 

[Fluff]

My pleasure.