0

I have this formula that when I enter it manually I get the result I need =COUNTIF(Start!H2: H517,'Final!A2)+COUNTIF(Start!AJ2: AJ517,'Interdiction Review'!A2) However, all the time the data on the sheet Start will be different so I need to find a way to use the last row on the place of H517 inside the formula

When I put the formula inside a VBA I am not getting an error but I don't get the result as well.

I believe my formula inside the VBA is wrong maybe due to the last row, but I don't get any error so I am not sure.

intrlastrow = Sheets("Start").Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
   intrlastrow1 = Sheets("Final").Cells(Cells.Rows.Count, "B").End(xlUp).Row + 1
    If Sheets("Start").Range("H2").Value <> "" Then
      Sheets("Final").Range("E2").Formula = "=Countif(Start!H2 & intrlastrow,'Final'!A2)+Countif(Start!AJ2 & intrlastrow,'Final'!A2)"
      Sheets("final").Range("E2").AutoFill Sheets("Final").Range("E2:E" & intrlastrow1)
        ElseIf Sheets("Start").Range("H2").Value = "" Then
      Sheets("Final").Range("E2").Formula = "=Countif(Start!G2 & intrlastrow,'Final'!A2)+Countif(Start!AI2 & intrlastrow,'Final'!A2)"
      Sheets("Final").Range("E2").AutoFill Sheets("Final").Range("E2:E" & intrlastrow1)
     End If

My questions are:

how can I use the last row inside a countif formula?

how can I correct use the formula inside a VBA?

Fah
  • 207
  • 3
  • 16
  • 1
    The variable should not fall inside the quotes. You need to concatenate with `&` but outside the quotes. `"=Countif(Start!H2:H" & intrlastrow & ",'Final'!A2)+Countif(Start!AJ2:AJ" & intrlastrow & ",'Final'!A2)"`. Plus you need the column letter before the last row. – BigBen Sep 23 '20 at 14:17
  • 1
    Also, you don't need to autofill, you can write the formula to the entire range in one line: `Sheets("Final").Range("E2:E" & intrlastrow1).Formula = ...` – BigBen Sep 23 '20 at 14:20

0 Answers0