help with printing to cells

Orongo

Board Regular
Joined
Nov 6, 2011
Messages
83
Hi, for some reason my command to write to cells("A1:D3") is not working with (toPrint is a Variant)

Worksheets("show").Range(Cells(1.1), Cells(3, 4)).Value = toPrint

so I have now made a loop to force the macro to write out toPrint like

Sub Write2Sheet(toPrint As Variant, position As Variant, sheet As String)
Dim iRow As Integer, iCol As Integer, i As Integer, j As Integer
iRow = position(0)
iCol = position(1)

For i = 1 To UBound(toPrint, 1)
For j = 0 To UBound(toPrint, 2)
Set Worksheets("show").Range(Cells(iRow, iCol), Cells(iRow + i, iCol + j)).Value = toPrint(i, j)
Next j
Next i

Both the tries have generated the same error "Run-time error '1004' - Application-defined or object-defined error". What is that I dont see? it feels so stupid, and I'm sure its something data defined. Please tell me what have been me sleepless during the night.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

obiron

Active Member
Joined
Jan 22, 2010
Messages
469
Worksheets("show").Range(Cells(1.1), Cells(3, 4)).Value = toPrint

this should be

Worksheets("show").Range(Cells(1,1), Cells(3, 4)).Value = toPrint
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Why the dot in the first call to the Cells property here?

Rich (BB code):
Worksheets("show").Range(Cells(1.1), Cells(3, 4)).Value = toPrint

If Worksheets("show") isn't the active sheet you need to qualify the Cells property:

Rich (BB code):
With Worksheets("show")
    .Range(.Cells(1, 1), .Cells(3, 4)).Value = toPrint
End With

The same applies to your loop code.
 

Orongo

Board Regular
Joined
Nov 6, 2011
Messages
83
Thank you for your reply, unfortunately its not working. I get an error message 'Expected identifier or bracketed expression' with the with and end with function. I looked into the help F1 and couldn't see any miss formualtion.

with (Worksheets("show").
Range(Cells(1,1), Cells(3,4)).Value = toPrint
End With
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

I didn't write this:

Rich (BB code):
with (Worksheets("show").

I wrote:

Rich (BB code):
With Worksheets("show")
 

Orongo

Board Regular
Joined
Nov 6, 2011
Messages
83
Sorry, my bad. I tried to fix the error by putting () but it didnt help. The error occurs evan without the (). I think its weird that VBA recognizes End With but not with (hence the capital W)

with Worksheets("show").
Range(Cells(iRow, iCol), Cells(20, 2).Value = toPrint
End With
 

Orongo

Board Regular
Joined
Nov 6, 2011
Messages
83

ADVERTISEMENT

Sorry, I see that I have pasted the wrong code, it should be

With Worksheets("show").
Range(Cells(iRow, iCol), Cells(20, 2)).Value = toPrint
End With
 

Orongo

Board Regular
Joined
Nov 6, 2011
Messages
83
THANK YOU! It works perfectly. Sorry about my clumsy miss spells. Out of curiousity, why have . front of Cells? I havnt seen it before and cant really relate to what it does.

with worksheets("show")
.Range(.Cells(iRow, iCol), .Cells(20, 2)).Value = toPrint
End With
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
It qualifies the property with its worksheet. Without the With construct it would be:

Code:
Worksheets("show").Range(Worksheets("show").Cells(1, 1), Worksheets("show").Cells(3, 4)).Value = toPrint

If you don't qualify the Cells property Excel will use the active sheet, and if that's not Worksheets("show") you will get an error.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,230
Messages
5,594,947
Members
413,953
Latest member
Arthur1471

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top