Fill down for all the cells

[singh]

Hi

Is there any way to fill down all the cells through macro only for filled
cells?

Simple Example

I have ten number in column A and same in column B in Column C i need sum of
both the columns. column D multiplication.

For auto fill am using the below code apart from formula in cell C.
c=2
while range("A" & c).value <> ""
range("C" & c).select
selection.filldown
c=c+1
wend

For D column I have to write the same code again. and if i have lots of
formulas in different columns the same thing continues.

Any solution.

 

[Conan Kelly]

singh,

if you have a number in every single cell in column B down to the last row
of your data, select C11 (or C22 if your data starts in row 2) and
double-click the fill handle.

HTH,

Conan

 

[INTP56]

'Try this

Public Sub TestFillDown()
Dim rngCurrent As Range
'I'm assuming the first value of interest is in .Cells(1,1)
With ThisWorkbook.Worksheets(1)
Set rngCurrent = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
rngCurrent.Offset(0, 2).FormulaR1C1 = "=RC[-2]+RC[-1]"
rngCurrent.Offset(0, 3).FormulaR1C1 = "=RC[-2]*RC[-1]"
End With
End Sub

Bob

 

[INTP56]

'If you don't want formulas in there,
'this will actually put the sum and product values in the cell

Public Sub TestFillDown()
Dim rngCurrent As Range
'I'm assuming the first value of interest is in .Cells(1,1)
With ThisWorkbook.Worksheets(1)
Set rngCurrent = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
rngCurrent.Offset(0, 2).FormulaR1C1 = "=RC[-2]+RC[-1]"
rngCurrent.Offset(0, 2).Copy
rngCurrent.Offset(0, 2).PasteSpecial xlPasteValues
rngCurrent.Offset(0, 3).FormulaR1C1 = "=RC[-2]*RC[-1]"
rngCurrent.Offset(0, 3).Copy
rngCurrent.Offset(0, 3).PasteSpecial xlPasteValues
End With
End Sub

Bob

 

[singh]

This thing is really working. Thanks

Can we do for next sheet also?

I need some calculation in sheet2 using data in sheet1. I do not think
autofill will work in this case.. Is there any way we can resolve it?

'Try this

Public Sub TestFillDown()
Dim rngCurrent As Range
'I'm assuming the first value of interest is in .Cells(1,1)
With ThisWorkbook.Worksheets(1)
Set rngCurrent = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
rngCurrent.Offset(0, 2).FormulaR1C1 = "=RC[-2]+RC[-1]"
rngCurrent.Offset(0, 3).FormulaR1C1 = "=RC[-2]*RC[-1]"
End With
End Sub

Bob

Hi

Is there any way to fill down all the cells through macro only for filled
cells?

Simple Example

I have ten number in column A and same in column B in Column C i need sum of
both the columns. column D multiplication.

For auto fill am using the below code apart from formula in cell C.
c=2
while range("A" & c).value <> ""
range("C" & c).select
selection.filldown
c=c+1
wend

For D column I have to write the same code again. and if i have lots of
formulas in different columns the same thing continues.

Any solution.

 

[singh]

Hi Kelly

Really very simple answer. But i can not use mouse while running the macro
to fill down by double click. In case I have to copy those data and paste it
somwhere then my entire macro is gone.