Solved: VBA logical test

[blackie42]

Hi,

Is there a way to copy a logical test (using VBA) without having to drag it down a big worksheet?

Headers on WS so range would be from row 2 to variable

Test is - in cell F "if(B2>0,1,0), so if B2 has +ve number then 1 in F2 and if not then 0

Is there also a way to do K2 = I2 + J2 - over the used range, again without having to drag it all the way down?

many thanks for any help
Jon

[BrianMH]

Click the cell and then instead of dragging it down double click the bottom corner and it will automatically expand down the used range.

[blackie42]

Thanks for that - something I wasn't aware of.

However I'd still like to do this in the macro I'm writing. Just not sure about the loop.

[VBA]Sub upfill()
Worksheets("Sheet3").Cells(Rows.Count, 2).End(xlUp).Select
ActiveCell.Offset(0, 5).Select
ActiveCell.FormulaR1C1 = "=IF(RC[-5]>0,1,0)"
ActiveCell.Offset(-1, 0).Select
End Sub[/VBA]

This finds the last used cell in col B and offsets 5 columns and sticks in the logical test. Then moves up to the next cell - just having a block on how to make it repeat.

Any help appreciated
thanks
Jon

[blackie42]

[VBA]Sub upfill()
Application.ScreenUpdating = False
Worksheets("Sheet3").Cells(Rows.Count, 2).End(xlUp).Select
ActiveCell.Offset(0, 5).Select
Do
ActiveCell.FormulaR1C1 = "=IF(RC[-5]>0,1,0)"
ActiveCell.Offset(-1, 0).Select
Loop Until ActiveCell.Value = "Count 1"
End Sub[/VBA]

Count 1 being the header cell