auto fill code

B

balu

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
 
A

Andy Pope

Hi,

Try this modified code for the autofill.

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

Cheers
Andy
 
B

balu

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
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top