Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofill VBA Q
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofill VBA Q
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofill Until | Excel Discussion (Misc queries) | |||
Autofill until | Excel Discussion (Misc queries) | |||
Autofill? | Excel Discussion (Misc queries) | |||
Autofill: Need to autofill one week block, (5) weekday only into cells. | Excel Discussion (Misc queries) | |||
Q. Autofill question: Can I autofill alpha characters like I can numbers? | Excel Programming |