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

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Copy a formula to a range of cells via VB6 using .Range(Cells(row,col), Cells(row,col)).Formula= statement (https://www.excelbanter.com/excel-programming/312520-copy-formula-range-cells-via-vb6-using-range-cells-row-col-cells-row-col-formula%3D-statement.html)

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

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

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

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.

--
Regards,
Tom Ogilvy

"Kevin" wrote in message
...
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

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

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").For mula = _
oBook.Worksheets("Parameters").Range("K4:L4").Form ula

'-------------------------------------------------------------------------
' 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

"Don Guillett" wrote in message
...
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

--
Don Guillett
SalesAid Software

"Kevin" wrote in message
...
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

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

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

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Kevin" wrote in message
...
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

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

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

I found the answer he 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

"Kevin" wrote in message
...
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

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

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

--
Regards,
Tom Ogilvy

"Kevin" wrote in message
...
Eureka!!!! I found it! It works!

I found the answer he 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

"Kevin" wrote in message
...
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

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

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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Kevin" wrote in message
...
Eureka!!!! I found it! It works!

I found the answer he 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

"Kevin" wrote in message
...
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