auto fill code

[Guest]

dim xlbook as excel.workbook
dim xlsheet as excel.worksheet
dim xlapp as excel.application
set xlapp = createobject ( " excel.application")
set xlbook = xlapp.workbook.add
set xlsheet = xlbook.xlsheet(1)
xlapp.visible = true
xlsheet.range("a1").value = 1000
xlsheet.range("b1").value = 200
xlsheet.range("c1").formula = xlsheet.range("a1").value -
xlsheet.range("b1").value
xlsheet.range("c2").formula = xlsheet.range("a2").value -
xlsheet.range("b2").value
'**********************
' actually column A and column b values were query values imported.

xlsheet.range("c1:c2").autofill ( xxlsheet.range("c1"):xlsheet.range("c25"))

' here im failing to autofill the formula for the range through code but
' i can with dragging the curser on the excel sheet on column C why ?
so it must be alike this,
a1=100 a2 = 200 a3 = 300
b1= 10 b2 = 0 c3 = 20
c1 = 110 c2 = 200 c3 = 320

 

[Andy Pope]

Hi,

Try this modified code for the autofill.

xlsheet.Range("C1:C2").AutoFill Destination:=xlsheet.Range("C1:C25")

Cheers
Andy

 

[Guest]

dear sir
thanks for your advise , but still im not getting the right result my code
simply copying the first cell value into all the cells
kindly advise please

 

[Andy Pope]

Hi,

Try this.
The reason the value is being copied is your .Formula command was simply
storing the result of the calculation rather than the formula syntax.

Sub x()

Dim xlsheet As Worksheet

Set xlsheet = Sheet1

With xlsheet
.Range("a1").Value = 1000
.Range("b1").Value = 200
.Range("c1").Formula = "=" & _
.Range("a1").Address(False, False) & _
"-" & .Range("b1").Address(False, False)

.Range("C1:C1").AutoFill Destination:=.Range("C1:C25")
End With

End Sub

Cheers
Andy