Hi Anil, Try using the same data types in the calling routine ( PowerReturnOutput ? ) and the called routine ( EnterPN ).
> PNArray() and OutputRng have values in the immediate window. When you say they have values, do you mean they have the expected values? Did you check the elements of the array? > After the line: > > For Each PN In PNArray() > > The sub exits and returns to the parent of the parent routine from > which it was called (i.e the routine which called PowerReturnOutput())! If the sub exits early, it usually indicates a runtime error. With default error handling (no ON ERROR statements, or the last one was ON ERROR GOTO 0), code execution will stop and you'll enter debug mode with the code line generating the error highlighted and a message box indicating the error. However, if any procedure in the call chain implements an error handler that is active, then when an error occurs, execution will continue at that error handler, and debug mode will not automatically be entered. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Anil Pandit Sent: Sunday, July 15, 2012 3:04 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Failure of code when trying to break a section out into a seperate routine Hi all, I have the following code in a sub routine which works (please see code in bold font), but which I want in a separate routine, so I can re-use the code. ------------------------------------------------------ Sub PowerReturnOutput() ' This sub procedure looks up the part number on sheet 4 and returns the ' part number & description to sheet 2 Dim PwrAndGnd() As Variant Dim Distance As Long Dim CableType As String Dim PwrColour As String Dim RtnColour As String Dim PartNumber(1 To 6) As String Dim PN As Variant Dim Cell As Range Dim TOPwrGndPN As Range Dim TOPwrGndDesc As Range Dim XGPwrAndGnd As Range Set TOPwrGndPN = Range("TOPwrGndPN") Set TOPwrGndDesc = Range("TOPwrGndDesc") Set XGPwrAndGnd = Worksheets("Eagle XG Input").Range("XGPwrAndGnd") ' Return an array with the XGArray function. PwrAndGnd = XGArray(XGPwrAndGnd) ' Extract elements into variables, adding 10 to BDFB distance. Distance = PwrAndGnd(1) + 10 PwrColour = PwrAndGnd(3) RtnColour = PwrAndGnd(4) CableType = PwrAndGnd(5) ' Extract part numbers into an array PartNumber(1) = AWGxPwrRtnPN(Distance, PwrColour, CableType) PartNumber(2) = AWGxPwrRtnPN(Distance, RtnColour, CableType) PartNumber(3) = AWG1GndPN(CableType) PartNumber(4) = AWG6PwrRtnPN(PwrColour, CableType) PartNumber(5) = AWG6PwrRtnPN(RtnColour, CableType) PartNumber(6) = AWG6GndPN(CableType) ' Enter the part numbers in sheet 2 Call EnterPN(PartNumber, TOPwrGndPN) ' For Each PN In PartNumber() ' For Each Cell In TOPwrGndPN ' If IsEmpty(Cell) Then ' Cell.Value = PN ' Exit For ' End If ' Next Cell ' Next PN ' Enter the descriptions in sheet 2 For Each PN In PartNumber() For Each Cell In TOPwrGndDesc If IsEmpty(Cell) Then If PN = "Not Available" Then With Cell .Value = "EAAA Brown AWG 2/0 cable is not available." .Font.Italic = True .Font.Color = vbRed .Offset(0, -1).Font.Italic = True .Offset(0, -1).Font.Color = vbRed End With Exit For Else Cell = AWGxPNDesc(PN) Exit For End If End If Next Cell Next PN End Sub ------------------------------------------------------ I created another sub-routine which is called from the PowerReturnOutput routine: ------------------------------------------------------ Sub EnterPN(PNArray, OutputRng) ' This sub procedure enters part numbers into the output range. Dim PN As Variant Dim Cell As Range For Each PN In PNArray() For Each Cell In OutputRng If IsEmpty(Cell) Then Cell.Value = PN Exit For End If Next Cell Next PN End Sub ------------------------------------------------------ The code does not work. When I set a break-point and step through the code, I can see that the EnterPN routine is called and that PNArray() and OutputRng have values in the immediate window. After the line: For Each PN In PNArray() The sub exits and returns to the parent of the parent routine from which it was called (i.e the routine which called PowerReturnOutput())! I hope someone can help me understand and hopefully fix this behaviour. Regards, Anil ------------------------------------------------------ -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
