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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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.
 
Upvote 0
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
 
Upvote 0
I didn't write this:

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

I wrote:

Rich (BB code):
With Worksheets("show")
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

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
Back
Top