Solved

Excel 2013 VBA 'Range on another worksheet' question

Posted on 2014-12-05
4
287 Views
Last Modified: 2015-01-28
I'm trying to dump the data in an array in a range of cells on the sheet that's not the ActiveSheet.

 
  Sheets("Sheet2").Range(Cells(5, 4), Cells(36, 13)).Value = arrMIDIEvents

Open in new window


I get an 'Application-defined or Object-defined error'.

However, it works if I Activate the other sheet and dump it. But I'd like to avoid leaving the ActiveSheet. Isn't that possible?

Thanks
0
Comment
Question by:hindersaliva
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 

Author Comment

by:hindersaliva
ID: 40484031
Nope. Sorry Simon. It made no difference.
0
 
LVL 18

Accepted Solution

by:
Simon earned 400 total points
ID: 40484037
Sorry, I deleted my suggestion before you responded after testing it.

This does work though:
Sub writeToNonActiveSheet()
arrMIDIEvents = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) 'Sample array to test with
Range(Sheets(2).Cells(5, 4), Sheets(2).Cells(36, 13)).Value = arrMIDIEvents
End Sub

Open in new window


I'd probably use an object variable to refer to the destination worksheet
Sub writeToNonActiveSheet2()
Dim destSheet As Worksheet
Set destSheet = Sheets(2)
arrMIDIEvents = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) 'Array to test with
destSheet.Range(destSheet.Cells(5, 4), destSheet.Cells(36, 13)).Value = arrMIDIEvents
End Sub

Open in new window

0
 
LVL 5

Assisted Solution

by:Hakan Yılmaz
Hakan Yılmaz earned 100 total points
ID: 40484270
You may use With keyword to shorten your references.
With ThisWorkbook.Worksheets("yoursheetname")
    .Range(.Cells(5, 4), .Cells(36, 13)).Value = arrMIDIEvents
End With

Open in new window


And keep in mind, ThisWorkbook.Sheets is not the same as ThisWorkbook.Worksheets. Because some sheets doesn't have Range and Cells properties.
0
 

Author Comment

by:hindersaliva
ID: 40484448
Perfect! Thank you all.
What I missed was, the .Cells(r, c) needs to be fully addressed with the Sheet name. I put it in the wrong place.
0

Featured Post

Turn your laptop into a mobile console!

The CV211 Laptop USB Console Adapter provides a direct Laptop-to-Computer connection for fast and easy remote desktop access with no software to install.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question