Autofill VBA Q
 

I have part of VBA code below that Places "1" in B2; then moves to B3 and inserts a formula, essentially if T30,1, B2+1. It then copies this formuala down to last row, but if last Row is B3 I get an error. How do I get around this?

I've tried replacing B3 with B4 but that just puts a value of 3 in B4 when it should be <blank


Range("B2").Formula = "1"
Range("B3").Select
With Selection
.Formula = "=IF(T30,1,B2+1)"
.AutoFill Destination:=Range("B3:B" & Range("B500").End(xlUp).Row)

Autofill VBA Q
 

Hi Sean,

Am Wed, 16 Nov 2016 05:48:28 -0800 (PST) schrieb :

I have part of VBA code below that Places "1" in B2; then moves to B3 and inserts a formula, essentially if T30,1, B2+1. It then copies this formuala down to last row, but if last Row is B3 I get an error. How do I get around this?

I've tried replacing B3 with B4 but that just puts a value of 3 in B4 when it should be <blank

Range("B2").Formula = "1"
Range("B3").Select
With Selection
.Formula = "=IF(T30,1,B2+1)"
.AutoFill Destination:=Range("B3:B" & Range("B500").End(xlUp).Row)

so column B is empty you can't calculate the last row in that column.
Try:

Sub AutoFill()
Dim LRow As Long

With ActiveSheet
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("B2") = 1
.Range("B3:B" & LRow).Formula = _
"=IF(T30,1,B2+1)"
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Autofill VBA Q
 

Claus, you saved the day again, thanks

One further twist I've just spotted, if I have only 2 Rows, your Autofill will show 0 in B3, instead of Blank, if I have 3 rows or more its perfect

Note on above A3 is empty

Autofill VBA Q
 

Hi Sean,

Am Wed, 16 Nov 2016 06:22:05 -0800 (PST) schrieb :

One further twist I've just spotted, if I have only 2 Rows, your Autofill will show 0 in B3, instead of Blank, if I have 3 rows or more its perfect

Note on above A3 is empty

then search the last row in column T:

LRow = .Cells(.Rows.Count, "T").End(xlUp).Row


Regards
Claus B.
--
Windows10
Office 2016

Autofill VBA Q
 

Can't get my head around this, with formula below, if there is only 2 rows of data (row 1 is header stuff), B2 will = 0 (its embedded with formula =IF(T30,1,B2+1), it should be 1 and B3 = 0, it should be <blank. Note there is a value in A1 & A2, hence the check on Column A

If there are more than 2 rows, formula works perfectly

If I am specifying B2 to be 1 and Autofillng from B3, how can B2 be populated with a formula, if Row2 is the last row of data?



With ActiveSheet
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("B2") = 1
.Range("B3:B" & LRow).Formula = "=IF(T30,1,B2+1)"

Autofill VBA Q
 

Hi Sean,

Am Wed, 16 Nov 2016 07:17:13 -0800 (PST) schrieb :

Can't get my head around this, with formula below, if there is only 2 rows of data (row 1 is header stuff), B2 will = 0 (its embedded with formula =IF(T30,1,B2+1), it should be 1 and B3 = 0, it should be <blank. Note there is a value in A1 & A2, hence the check on Column A

If there are more than 2 rows, formula works perfectly

I hope I understand your correctly.
Try:

Sub AutoFill()
Dim LRow As Long

With ActiveSheet
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("B2") = IIf(LRow 2, 1, 0)
If LRow 2 Then
.Range("B3:B" & LRow).Formula = _
"=IF(T30,1,B2+1)"
End If
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Autofill VBA Q
 

On Wednesday, November 16, 2016 at 3:41:11 PM UTC, Claus Busch wrote:
Hi Sean,

Am Wed, 16 Nov 2016 07:17:13 -0800 (PST) schrieb :

Can't get my head around this, with formula below, if there is only 2 rows of data (row 1 is header stuff), B2 will = 0 (its embedded with formula =IF(T30,1,B2+1), it should be 1 and B3 = 0, it should be <blank. Note there is a value in A1 & A2, hence the check on Column A

If there are more than 2 rows, formula works perfectly

I hope I understand your correctly.
Try:

Sub AutoFill()
Dim LRow As Long

With ActiveSheet
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("B2") = IIf(LRow 2, 1, 0)
If LRow 2 Then
.Range("B3:B" & LRow).Formula = _
"=IF(T30,1,B2+1)"
End If
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Claus, thats it, you've cracked it. Many thanks