|
Copy Excel Spreadsheet formula to a range via VB6
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 |
Copy Excel Spreadsheet formula to a range via VB6
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). -- Regards, Tom Ogilvy "Kevin" wrote in message ... 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 |
Copy Excel Spreadsheet formula to a range via VB6
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" wrote in message ... 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). -- Regards, Tom Ogilvy "Kevin" wrote in message ... 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 |
Copy Excel Spreadsheet formula to a range via VB6
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").For mula = _ oBook.Worksheets("Parameters").Range("K4").Formula oBook.Worksheets("Parameters").Range("L4:L40").For mula = _ oBook.Worksheets("Parameters").Range("L4").Formula -- Regards, Tom Ogilvy "Kevin" wrote in message ... 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" wrote in message ... 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). -- Regards, Tom Ogilvy "Kevin" wrote in message ... 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 |
Copy Excel Spreadsheet formula to a range via VB6
Exactly, works great!
'================================================= ======================== ' Copy formula cells in spreadsheet. oBook.Worksheets("Parameters").Range("K4:L40").For mula = _ oBook.Worksheets("Parameters").Range("K4:L4").Form ula Thanks again Tom. "Tom Ogilvy" wrote in message ... 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). -- Regards, Tom Ogilvy "Kevin" wrote in message ... 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 |
| All times are GMT +1. The time now is 04:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com