Copy a formula to a range of cells via VB6 using .Range(Cells(row,col), Cells(row,col)).Formula= sta

[Kevin]

I need to copy a single formula cell to a range of cells. I can do it with
a range statement I learned of yesterday but I want to use cell notation so
I can make the cell copy dynamic. How can I do it? For reference, the
formula in my cell to copy from looks like this:
=IF(SectionData!$I3='Transformed data'!BG$2,1,0).

Thanks in advance!
Kevin

This works great but I'd like to use cell notation instead.
' Copies a single formula cell to a range of cells.
oBook.Worksheets("Transformed data").Range("BG4", "CE321").Formula =
_
oBook.Worksheets("Transformed data").Range("BG3").Formula

In this experiment I get the error "Object variable or With Block variables
not set". I haven't figured how to solve the error message but this is the
formula I really need to make work
oBook.Worksheets("Transformed data").Range(Cells(3, 58), Cells(320,
58 + iCitizenshipCount)).Formula = _
oBook.Worksheets("Transformed data").Range("BG3").Formula

In this experiment the cells copied but it copied the exact formula to each
cell, the formula cell references did not change. Also, this is very slow!
For X = 3 To 320
For Y = 1 To iCitizenshipCount
oSheet.Cells(X, 58 + Y).Formula =
oBook.Worksheets("Transformed data").Range("BG3").Formula
Next
Next

Here is how I clear and copy data to the spreadsheet with no problems:
' Clear Parameters: Citizenship Section
Set oSheet = oBook.Worksheets("Transformed data")
oSheet.Range("BG2:CE2").Value = ""
oSheet.Range("BH3:CE3").Value = ""
oSheet.Range("BG4:CE320").Value = ""

'Transfer the array to the worksheet
oSheet.Range("BG2:CE2").Resize(1, iCitizenshipCount).Value =
aCitizenshipTransformedData

 

[Don Guillett]

Either of these should work.

Sub copyformula()
x = 3
'Sheets("sheet13").Range("k1:k3").Formula = _
Sheets("sheet14").Range("d5").Formula

Sheets("sheet13").Range(Cells(1, "k"), Cells(x, "k")).Formula = _
Sheets("sheet14").Cells(5, "d").Formula

End Sub

 

[Tom Ogilvy]

With oBook.Worksheets("Transformed data").Range( _
.Cells(3, 58), .Cells(320, 58 + iCitizenshipCount)).Formula = _
.Range("BG3").Formula
end With

If "Transformed data' is not the activesheet, then you would need to use a
construct like the above. In any event, there is no reason the above would
not work. (unless 58 + iCitizenshipCount > 256) or the formula in BG3 goes
bad when converted for the cells you are working with.

 

[Kevin]

Don,

I still get the "Object variable or With Block variables not set"
error. It must be the way I'm referencing the spreadsheet?
It's unclear to me because my non 'cell-reference' code works fine, but I
just can't get past the '...variables not set' error when trying to use
..Range(cell) references.

I'll show more of how I'm working the code in case someone sees something
obvious.

Thanks for the help and anyone else who can shed some light on this.

Kevin
==============

Works:
oBook.Worksheets("Transformed data").Range("BG4", "CE321").Formula =
_
oBook.Worksheets("Transformed data").Range("BG3").Formula

Doesn't
Sheets("Transformed data").Range(Cells(3, 58), Cells(320, 58 +
iCitizenshipCount)).Formula = _
Sheets("Transformed data").Range("BG3").Formula

Sheets("Transformed data").Range(Cells(3, "BG"), Cells(320, "BG" +
iCitizenshipCount)).Formula = _
Sheets("Transformed data").Range("BG3").Formula

oBook.Worksheets("Transformed data").Range(Cells(3, 58), Cells(320,
58 + iCitizenshipCount)).Formula = _
oBook.Worksheets("Transformed data").Range("BG3").Formula

Sheets("Transformed data").Range(Cells(3, 58), Cells(320, 58 +
iCitizenshipCount)).Formula = _
Sheets("Transformed data").Range("BG3").Formula

==============================================================================
Private Sub ExportCitizenshipData()
On Error GoTo Error_Handler

Dim cnLocalConnection As New ADODB.Connection
Dim rsLocal As New ADODB.Recordset
Dim strConn As String
Dim sSQL As String
Dim X As Integer
Dim Y As Integer
Dim iCitizenshipCount As Integer
Dim oBook As Object
Dim oSheet As Object
ReDim aCitizenshipParameterData(40, 2)

'=========================================================================
' Connect to Spreadsheet
'
If FindWindow("XLMAIN", vbNullString) Then
Set oExcel = GetObject(, "Excel.Application")
Else
Set oExcel = CreateObject("Excel.Application")
End If

Set oBook = GetObject(Me.txtSelectedModel)

strConn = gblLocalAccessDatabaseConnect
cnLocalConnection.CursorLocation = adUseClient
cnLocalConnection.Open strConn

sSQL = "my select statement..."
rsLocal.Open sSQL, cnLocalConnection, adOpenStatic,
adLockOptimistic, adCmdText
iCitizenshipCount = rsLocal.RecordCount

' Populate array with data that will be transfered to spreadsheet
X = 0
While Not rsLocal.EOF

aCitizenshipParameterData(X, 0) = rsLocal("Citizenship")
aCitizenshipParameterData(X, 1) = rsLocal("CountOfCitizenship")
aCitizenshipTransformedData(X) = rsLocal("Citizenship")

X = X + 1
rsLocal.MoveNext
Wend

rsLocal.Close
Set rsLocal = Nothing

ReDim Preserve aCitizenshipTransformedData(X)

'=========================================================================
' Transfer data from array to spreadsheet. We use an array for the
transfer
' because it is so much faster than writing to each cell individually.

'-------------------------------------------------------------------------
' Citizenship Parameters section
'
' Clear Parameters: Citizenship Section
Set oSheet = oBook.Worksheets("Parameters")
oSheet.Range("I5:L40").Value = ""

'Transfer the array to the worksheet
oSheet.Range("I4").Resize(iCitizenshipCount, 2).Value =
aCitizenshipParameterData

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

'-------------------------------------------------------------------------
' Citizenship Transformed data section
'
' Clear Parameters: Citizenship Section
Set oSheet = oBook.Worksheets("Transformed data")
oSheet.Range("BG2:CE2").Value = ""
oSheet.Range("BH3:CE3").Value = ""
oSheet.Range("BG4:CE320").Value = ""

'Transfer the array to the worksheet
oSheet.Range("BG2:CE2").Resize(1, iCitizenshipCount).Value =
aCitizenshipTransformedData

oBook.Worksheets("Transformed data").Range("BG4", "CE321").Formula =
_
oBook.Worksheets("Transformed data").Range("BG3").Formula

oBook.Save
oBook.Close
oExcel.Quit

 

[Kevin]

Tom,

Still get the error. While debugging, when I hover the mouse over Cells
the message is the following:

Cells(3, 58) = <Method 'Cells of object '_Global' failed>

I run the following code with now error so I believe 'Transformed data'
sheet is active...

Seems like this would be so straight forward......

Thank you!

Kevin

' Clear Parameters: Citizenship Section
Set oSheet = oBook.Worksheets("Transformed data")
oSheet.Range("BG2:CE2").Value = ""
oSheet.Range("BH3:CE3").Value = ""
oSheet.Range("BG4:CE320").Value = ""

'Transfer the array to the worksheet
oSheet.Range("BG2:CE2").Resize(1, iCitizenshipCount).Value =
aCitizenshipTransformedData

With oBook.Worksheets("Transformed data").Range( _
Cells(3, 58), Cells(320, 58 + iCitizenshipCount)).Formula = _
Range("BG3").Formula
End With

 

[Kevin]

Eureka!!!! I found it! It works!

I found the answer here: Microsoft Knowledge Base Article - 178510



I needed to change: Range(Cells()) to Range(oSheet.Cells())..



It needed to have a explicit sheet reference in the Range.Cells area.



Thanks for everyones help!


Kevin



This code works:



Set oSheet = oBook.Worksheets("Transformed data")
oBook.Worksheets("Transformed data").Range(oSheet.Cells(3, 58),
oSheet.Cells(320, 58 + iCitizenshipCount)).Formula = _
oBook.Worksheets("Transformed data").Range("BG3").Formula

 

[Tom Ogilvy]

Well it is unfortunate that I botched up my suggestion, because that is what
I was suggesting and that this does when there are no typos:

With oBook.Worksheets("Transformed data")
.Range(.Cells(3, 58), .Cells(320, 58 + _
iCitizenshipCount)).Formula = _
.Range("BG3").Formula
end With

this is a more compact way of doing it, but since you already have the
reference to the sheet, you could do

With osheet
.Range(.Cells(3, 58), .Cells(320, 58 + _
iCitizenshipCount)).Formula = _
.Range("BG3").Formula
End with

 

[Kevin]

I'm going to use your compact version of the code. ..so many ways to do the
same thing, so much to learn.
You've helped me immensly.
Thank you!

Kevin