Check my code please

[George Too]

I have this piece of code:

-----------------------------------
Dim Row, C As Integer
Dim Column As String

Row = 22
Column = Chr(67)
C = 0

While Counter <> 9
For Each Control In Me.childfrmMstrRollInfo.Controls
.Range(Column & Row).Select
.ActiveCell = Control.Value
C = C + 1
Column = Chr(67 + C)
Next Control
Wend
-----------------------------------

It is populating a spreadsheet based on a subform in my main form. The problem is in the red line the second time the loop runs. It generates the '438' error "Object doesn't support this property or method." Again, it gets the value to the first cell (C22) but won't do it for the second (D22).
Any workarounds would be greatly appreciated.

Thanks for your time,
George

 

[WayneRyan]

George,

Don't know what this is doing, but here are some
thoughts...

What is Counter? Not declared or altered.
Can't be "Column & Row" --> Column & ":" & Row

Dim Row, C As Integer
Dim Column As String

Row = 22
Column = Chr(67)
C = 0

While Counter <> 9   '
For Each Control In Me.childfrmMstrRollInfo.Controls
  .Range(Column & Row).Select
  .ActiveCell = Control.Value
  C = C + 1
  Column = Chr(67 + C)
  Next Control
Wend

Wayne

 

[George Too]

Sorry to be so vague about it. The code is just a section of the whole. What this part does is populate a spreadsheet with with the values of a subform on my main form. It is supposed to loop through each field in that subform and move the values to a cell in the spreadsheet. All my variables are declared (counter is just a typo, same variable as c). Row and column determine the rows and columns on the spreadsheet.

My problem is the line

.ActiveCell = Control.Value

the second time the loop runs it stops here and generates the error.

 

[George Too]

Any takers on this?

 

[George Too]

I want to loop through the fields in a subform, grab the values and dump them in a spreadsheet by way of the above code. Aside from declaring all my variables and things like that, would that loop work as is or do I need to make changes to it?

 

[Mile-O]

Dimension an object variable:

Dim ctl As Control
For Each ctl In MyForm

You can't use an actual object.

Row and Column are also reserved words so you should not use them as variable names.

I'm also sure you meant:

Dim Row As Integer, C As Integer

instead of:

Dim Row, C As Integer

 

[George Too]

Thanks Mile-O-Phile,
I fixed the code a bit but it still hangs when strColumn = E and intRow = 22. I just dont' understand why it won't go further than that. Here is the code:


Dim ctl As Control
Dim intRow As Integer, C As Integer, R As Integer
Dim strColumn As String

intRow = 22
strColumn = Chr(67)
C = 0
R = 0

For R = 1 To 8
For C = 1 To 4
For Each ctl In Me.childfrmMstrRollInfo.Controls
.Range(strColumn & intRow).Select
.ActiveCell = ctl.Value
C = C + 1
strColumn = Chr(67 + C)
Next ctl
Next C
C = 0
R = R + 1
intRow = intRow + C

Next R

 

[Mile-O]

Is there more code you are not showing as I can't see any reference to the With...End With commands in order to do this:

.Range(strColumn & intRow).Select
.ActiveCell = ctl.Value

So, you must have an Excel Object variable defined that you are not mentioning.



Aside, you don't need to do this:

C = 0
R = 0

as an integer's default value is 0 anyway.

 

[George Too]

Yes Mile-O-Phile, this is just that section of code that takes care of a subform. The rest of the code takes care of the main form. I have the following at the beginning of my on_Click event:

Dim ExcelApp As Object
Set ExcelApp = CreateObject("Excel.Application")
With ExcelApp
.Workbooks.Open strTemplate
.Visible = True
.
.
.
.ActiveWorkbook.SaveAs FileName:=strSaveToPath

.ActiveWindow.Close
End With

 

[George Too]

If I have 4 text boxes in my subform (childfrmMstrRollInfo), why this code is exiting after it exports the value of the first one only?


Dim ctl As Control
Dim intRow As Integer, C As Integer, R As Integer
Dim chrColumn As String

intRow = 22
chrColumn = Chr(67)

For Each ctl In Forms![frmMain]![childfrmMstrRollInfo].Controls
.Range(chrColumn & intRow).Select
.ActiveCell = ctl.Value
C = C + 1
chrColumn = Chr(67 + C)
Next ctl


Thanks for your help,
George

 

[Mile-O]

Shouldn't you be moving to a new record in the subform? How many controls do you have on it?

 

[George Too]

There are only four text boxes. I removed the code to change records just to debug to see where the code is breaking. By doing that I found out that I get error "438:Object doesn't support this property or method" after the first cell has been writen then skips one cell and writes on the next cell, produces the erro again and skips that cell then moves on to the other. At the end I have every other cell written and I have no clue why...