syntax question with VBA

[bdee1]

i have a loop that determines the first blank row in a worksheet - it is as follows:

x = 2
  
  Sheets("eRehab_Download").Select
  Range("A1").Select
  
  Do While Not IsEmpty(Range("A" & x))
    x = x + 1
  Loop

and what i want to do is paste a formula 11 rows after the last row contacting data so i am using the following:

Range("B" & x + 11).Select
  ActiveCell.Formula = "=COUNTA(B4:BX)"

problem is in the line where i say COUNT(B4:BX)

obviously i want X to represent the number of the last row containing data but it is coming out as the letter X in the formula.

what do i need to change to get the to get the formula to have the value of the variable X instead of the letter X?

 

[Joe4]

This should do everything you want without an unnecessary loop:

    Dim myLastRow As Long
    myLastRow = Range("A65536").End(xlUp).Row
    Cells(myLastRow + 11, 2).Formula = "=COUNTA(B4:B" & myLastRow & ")"

 

[bdee1]

i have seen the method you are suggesting (to avoid the loop) but unfortunately it does not work for me because the first 3 rows of the worksheet i am working with are the header and it includes a blank row. so your method always returns the 2nd row... i needed something to start with row 4 and find the next blank row...

but with regard to the notation for setting the formula -
i tried Cells(myLastRow + 11, 2).Formula = "=COUNTA(B4:B" & myLastRow & ")" and it worked! thanks!

before i tried Activecell.Formula = "=COUNTA(B4:B" & X & ")" and it gave me a compile error but for whatever reason your notation worked.

thanks again!

 

[Joe4]

You still don't need a loop. Try:

    Dim myLastRow As Long 
    myLastRow = Range("A4").End(xlDown).Row 
    Cells(myLastRow + 11, 2).Formula = "=COUNTA(B4:B" & myLastRow & ")"

If you can have row 4 as the first blank row, simply change
myLastRow = Range("A4").End(xlDown).Row
to
myLastRow = Range("A3").End(xlDown).Row

 

[bdee1]

of course - i dont know why i didnt see that before - thank you for the tip

you guys (the mr excel community) are the best!