Copy Excel Spreadsheet formula to a range via VB6

[Kevin]

I manipulate a spreadsheet with an external VB program.
My first task is to clear the cells
Next I populate data into the cells
Next to the rows I've copied, there is a formula I'd like to extend to match
the data I put on the worksheet. How can I do this?

Example:

I clear out the range I'm going to populate
' Clear Parameters: Citizenship Section
Set oSheet = oBook.Worksheets("Parameters")
oSheet.Range("I4:J40").Value = ""

Then I populate the spreadsheet from my array
'Transfer the array to the worksheet
oSheet.Range("I4").Resize(iRecordCount, 2).Value = aCitizenshipData

Next I want to extend the formulas that exist on the spreadsheet. I copied
the spreadsheet formulas this way:
' Copy formula cells from spreadsheet.
sFormula1 = oBook.Worksheets("Parameters").Range("K4").Formula
sFormula1 = oBook.Worksheets("Parameters").Range("L4").Formula

formula1 = '=ROUNDUP(J4/5,0)'
formula2 = '=L4'

How can I paste the formulas to the rows below it with VB6. I could write a
looping structure that would autoincrement
the row values but there must be an Excel parameter I can use.

Thanks in advance.

Kevin

Total Max/section Min/section
India 15 3 2
China 8 2 1
PR China Kevin 6 2 1
Japan 4 1 0
Nigeria 4 1 0
South Korea 4 1 0
Canada 3 1 0
Italy 3 1 0
Mexico 3 1 0
Pakistan 3 1 0
Philippines 3 1 0
Singapore 3 1 0
Taiwan 3 1 0
Argentina 2 1 0
Brazil 2 1 0
Peru 2 1 0
Portugal 2 1 0
Republic of Korea 2 1 0
Spain 2 1 0
Turkey 2 1 0
0 -1

 

[Tom Ogilvy]

This code looks kind of crazy Kevin

sFormula1 = oBook.Worksheets("Parameters").Range("K4").Formula
sFormula1 = oBook.Worksheets("Parameters").Range("L4").Formula

formula1 = '=ROUNDUP(J4/5,0)'
formula2 = '=L4'


You don't use sFormula1 or sFormula2

assume you actually do want to extend the formulas in K4 and L4

Range("K4:K40").Formula = Range("K4").Formula


Range("L4:L40").formula = Range("L4").Formula

would extend the formula with appropriate reference adjustments for
non-absolute references.

(without using the 4 original lines you posted).

 

[Kevin]

Thanks Tom I'll give it a try. By the way two of those lines were to
illistrate what the formulas looked like for reference!

Thanks!

Kevin

formula1 = '=ROUNDUP(J4/5,0)'
formula2 = '=L4'

 

[Tom Ogilvy]

But surely L4 couldn't contain the formula =L4 (perhaps that is what is in
K4).

Also, I used my shorthand. You may need to fully qualify with

oBook.Worksheets("Parameters").Range("K4:K40").Formula = _
oBook.Worksheets("Parameters").Range("K4").Formula
oBook.Worksheets("Parameters").Range("L4:L40").Formula = _
oBook.Worksheets("Parameters").Range("L4").Formula

 

[Kevin]

Exactly, works great!
'=========================================================================
' Copy formula cells in spreadsheet.
oBook.Worksheets("Parameters").Range("K4:L40").Formula = _
oBook.Worksheets("Parameters").Range("K4:L4").Formula

Thanks again Tom.