Data got getting returned from Sub Routine

Hwyn · #1 01-18-2012, 03:59 AM

Hello, I'm using Excel 2007 and have some code which works fine, basically opening some work books from a list on a sheet and using "Union" to get some Non contiguous ranges and return the range data back to the originating book.
My problem is that if I put the "Get Some Ranges" code into a sub routine it fails to return any SrcRng data to the Main Sub saying SrcRng is empty. ( I need to put it into a sub routine as some of the work books have the same type of data but in different columns )
Eventually I intend using an if statement to decide what ranges to get depending on the open workbook.
Hope I've explained it ok, many thanks Robert

The following code works fine.

Code:

 
Sub OpenBookTest3()
 
Dim Obook As Workbook
Dim Dbook As Workbook
Dim sTrVal As String
Dim sTrVal2 As String
 
' OBook is already open
Sheets("Supplier Feeds").Activate
Set Obook = ActiveWorkbook
 
‘ Col B Contains a list of Suppliers workbook names
Range("b2").Select
Do Until IsEmpty(Cells(ActiveCell.Row, "B"))
 
sTrVal = ActiveCell.Value ‘ Supplier workbook
sTrVal2 = ActiveCell.Offset(0, -1).Value ‘ Supplier Name
Set Dbook = Workbooks.Open("C:\Users\Bob\Desktop\DataFolder\" & sTrVal & ".xlsx")
 
‘Get Some Ranges
LastRow = Cells(Rows.Count, "a").End(xlUp).Row
 
Set RngA = Range("a2:a" & LastRow)
Set RngE = Range("e2:e" & LastRow)
Set RngF = Range("f2:f" & LastRow)
Set RngG = Range("g2:g" & LastRow)
Set RngY = Range("Y2:y" & LastRow)
Set RngZ = Range("z2:z" & LastRow)
 
'Grab Certain Columns
Set SrcRng = Application.Union(RngA, RngE, RngF, RngG, RngY, RngZ)
 
Set destRng = Obook.Sheets(sTrVal2).Cells(Rows.Count, "A").End(xlUp)(2)
Dbook.Activate
SrcRng.Copy _
Destination:=destRng
 
Dbook.Close SaveChanges:=False
Application.DisplayAlerts = False
 
ActiveCell.Offset(1, 0).Select
Loop
 
End Sub

The following is the bit of code I hope to put into a sub routine

Code:

 
 
‘Get Some Ranges
LastRow = Cells(Rows.Count, "a").End(xlUp).Row
 
Set RngA = Range("a2:a" & LastRow)
Set RngE = Range("e2:e" & LastRow)
Set RngF = Range("f2:f" & LastRow)
Set RngG = Range("g2:g" & LastRow)
Set RngY = Range("Y2:y" & LastRow)
Set RngZ = Range("z2:z" & LastRow)
 
'Grab Certain Columns
Set SrcRng = Application.Union(RngA, RngE, RngF, RngG, RngY, RngZ)
 
' Return to Sub OpenTest3

Pecoflyer · #2 01-18-2012, 05:13 AM

Might I suggest you wrap your code with code tags ( makes it easier to copy) ?

Just edit your post, click "go advanced", select your code and click the # button, then save.
Thx

Hwyn · #3 01-18-2012, 06:56 AM

Ooops Sorry ..Thanks for the Quote Tip
Robert

Catalin.B · #4 01-18-2012, 07:43 AM

search will not work if the range is not contiguous, you need another approach, you range can be split in 3 contiguous ranges.

Catalin.B · #5 01-18-2012, 07:44 AM

Same for copy-paste ranges...

Hwyn · #6 01-18-2012, 08:02 AM

Quote:

Originally Posted by Catalin.B

search will not work if the range is not contiguous, you need another approach, you range can be split in 3 contiguous ranges.

Thanks for the reply Catalin.B, but what I can't grasp is why it works if it's in the Main Sub but not if it's in a separate sub routine.
Regards Robert

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro or routine required	evanhughes	Outlook	0	11-18-2011 02:56 AM
Email returned from a different address then the original destination	Webfaction	Outlook	1	04-25-2011 03:16 PM
returned mail put in tray and not in inbox	tcreek	Outlook	0	10-19-2010 12:06 PM
The Message interface returned an unknown error. If the problem presists, restart out	RolandVet	Outlook	2	04-18-2010 04:54 AM
Word crashing on "sort" routine	Kayale	Word	0	04-12-2010 04:54 PM