MACRO In a never ending loop

**madbrit** · 03-18-2012, 05:41 PM

Someone very kindly gave me this macro
It works great for what I want, but if i enter a job number that does not exsist in the directory it goes into a never ending loop

Can someoone help, I need to error trap this macro so if i enter a number that does not exsist in the directory it will put up a message box telling me, and exit the Macro, or ask me to put a correct number in instead of going into a loop looking for a non exsisting file.

Thanks

MadBRiT

Sub Replace_Job_Number()

    Dim rngOld As Range, rngNew As Range
    
    Set rngOld = Range("A1")   'old job number
    Set rngNew = Range("A2")   'new job number
    
    If rngOld = "" Or rngNew = "" Then
    
        MsgBox "Missing job number. ", , "Cannot Update Links"

    Else

        Cells.Replace What:=rngOld.Value & ".xls", _
                      Replacement:=rngNew.Value & ".xls", _
                      LookAt:=xlPart, _
                      SearchOrder:=xlByRows, _
                      MatchCase:=False
    
        rngOld.Value = rngNew.Value
        rngNew.ClearContents
        
    End If

End Sub

**Richard Buttrey** · 03-18-2012, 06:32 PM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**royUK** · 03-19-2012, 02:37 AM

Give more details on what you are oing

**madbrit** · 03-19-2012, 09:38 AM

I have 3 people going out getting jobs for me
when they come back they have an excel sheet with the quote data in cells B14:L39

I save these Excel workbooks as a job No's like.... 1111

At the moment I cut and paste B14:L39 into my Workboox sheet, it then calculates the sizes i have to cut things to, what length of material I have to order, and various other variables.

What I am trying to do is one of 2 things

First a MACRO, that when i start it up it askes me for the job No, it then finds it and places the data I want from that workbook (B14:L39)
into my Worksheet.

The other thing I have been toying with is opening the quote sheet and it Auto places the quote data ( B14:L39)
into my sheet, I have taken one line of this formula and pasted it below.

=SUM('[1111.xls]QUOTE SHEET'!$B$14)

This works when I open the workbook No 1111 it transfers all data I want, BUT...

How do I make the No 1111 variable, so If i put 2222 in cell A1 all the variables formulas will now look for 2222
=SUM('[variable(A1).xls]QUOTE SHEET'!$B$14)

Someone came up with this, and it works great, unless you know of a better way?

Sub Replace_Job_Number()

    Dim rngOld As Range, rngNew As Range, strPath As String
    
    Set rngOld = Range("A1")    ' old job number
    Set rngNew = Range("A2")    ' new job number
    strPath = "C:\Test\"        ' Path where files are located
    
    If rngOld = "" Or rngNew = "" Then
    
        MsgBox "Missing job number. ", , "Cannot Update Links"
        
    Else
    
        If Len(Dir(strPath & rngNew & ".xls")) Then

            Cells.Replace What:=rngOld.Value & ".xls", _
                          Replacement:=rngNew.Value & ".xls", _
                          LookAt:=xlPart, _
                          SearchOrder:=xlByRows, _
                          MatchCase:=False
        
            rngOld.Value = rngNew.Value
            rngNew.ClearContents
        
        Else
            MsgBox "Cannot locate file: " & vbCr & vbCr & _
            strPath & rngNew.Value & ".xls", , "File Not Found"
        End If
    End If

End Sub

Thanks

G