auto fill code
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 |
auto fill code
Hi,
Try this modified code for the autofill. xlsheet.Range("C1:C2").AutoFill Destination:=xlsheet.Range("C1:C25") Cheers Andy balu wrote: 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 |
auto fill code
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" wrote: Hi, Try this modified code for the autofill. xlsheet.Range("C1:C2").AutoFill Destination:=xlsheet.Range("C1:C25") Cheers Andy balu wrote: 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 |
auto fill code
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 balu wrote: 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" wrote: Hi, Try this modified code for the autofill. xlsheet.Range("C1:C2").AutoFill Destination:=xlsheet.Range("C1:C25") Cheers Andy balu wrote: 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 |
All times are GMT +1. The time now is 04:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com