Help on Error Handler

[rence19]

Dear Excellent Friends!

My existing macro comprises of different modules to perform specific task such as "LOOP_THROUGH_FOLDER_OPEN_FILE" - "PROCESS_FILE" - "SAVE_WORKBOOK"


To be specific:

1. Macro "LOOP_THROUGH_FOLDER_OPEN_FILE" to look for files with .XLS extension ( (say file1.xls, file2.xls....fileN.xls if there is as such) then CALL "PROCESS_FILE" to do the "process" and CALL SAVE_WORKBOOK to save the workbook.

2. "PROCESS_FILE" does the following process:

Example:
If file1.xls is opened, Range("M4") gets "VLOOKUP" value reference from other Workbook_WITH_LIST.XLS file (this contains list of data).


code:

Range("M1").Formula = "=VLOOKUP(M1,'C:\BACKUP \[Workbook_WITH_LIST.XLS]Sheet1'!$A:$N,2,FALSE)"

ActiveSheet.Cells(1, 1) = "Mr. John is" & Range("M4") & "yrs. old."

This procedure seems to work flawlessly provided that all files on that particular folder is listed on the Workbook_WITH_LIST.XLS.
All file on that folder is processed and I obtain the results i need.

However.....
My problem occurs when some files on that source folder is not on the list and "VLOOKUP" value referenced to Workbook_WITH_LIST.XLS returns #N/A. and the the macro hangs up.


I'm a novice on vb coding and I read that ERRORHANDLING will do provide the solution on my problem.


Please help me. What I need is that when that file is not on the list say: "VLOOKUP" value = #N/A, then:

a. macro will save that file to other directory
b. bypass "PROCESS_FILE and SAVE_WORKBOOK" process,
c. delete that file on that source folder
d. continue to process all files until all files on that folder is processed.

By that I can check those files that are not processed.

Btw, the source folder may contain more than 300 files for processing

Appreciate you help and patience on helping.

thanks in advance!

 

[jbeaucaire]

Something like this logic:

Range("M1").Formula = "=VLOOKUP(M1,'C:\BACKUP \[Workbook_WITH_LIST.XLS]Sheet1'!$A:$N,2,FALSE)"
If WorksheetFunction.IsErr(Range("M1")) = True Then
    'new save function to other directory
    'close the file
    'delete from original directory
Else
    'your current code continues
End If

 

[rence19]

dear jbeaucaire,

thanks for the reply. I tried the code but apparently it cant recognize the "#N/A" (file opened is not on the list) result of cell M1.


i have this more detailed code hope this helps:

'''''''''''''''''''''
Sub Loop folder_open_file

'this will loop through the folder and look for files with .xls extension until
all files are looped

call Process() 'calls Process to do processing

end sub

''''''''''''''''
Sub Process()

Range("M1").Formula = "=VLOOKUP(M1,'C:\BACKUP \[Workbook_WITH_LIST.XLS]Sheet1'!$A:$N,2,FALSE)"
ActiveSheet.Cells(1, 1) = "Mr. John is" & Range("M4") & "yrs. old."

'if the value of cell M1 is not on the list then the opened workbook should be saved to other directory and delete the file from the source folder and 'proceed to next file for the loop folder procedure
'
'
'

Call save() 'calls save macro if file is successfully processed and will save to 'specified saving directory then proceed to next file for the loop folder 'procedure

End sub

--------------------------------
sub save()
'save directory is set here

end sub

 

[jbeaucaire]

Maybe....:

Sub Process()

Range("M4").Formula = "=VLOOKUP(M1,'C:\BACKUP \[Workbook_WITH_LIST.XLS]Sheet1'!$A:$N,2,FALSE)"

If IsNumeric(Range("M4")) Then
    ActiveSheet.Cells(1, 1) = "Mr. John is" & Range("M4") & "yrs. old."
Else
    'do your other stuff
    '
    '
End If

 

[rence19]

dear jbeaucaire,

The IsNumeric(Range("M4")) works if i have numerical results only. But what if the cell has other valid value aside from numbers (combination of letter and number)?

May I ask relative questions also:

1. If the condition is satisfied by "IsNumeric(Range("M4")": I can save the activeworkbook(using same filename from the source) to other directory and close the activeworkbook, but I have the problem on deleting the activeworkbook from its source directory.
Can you help me on this?

2. Lastly, how can I allow the macro to continue to do the routine for all files
to be checked and processed say:

"Sub Loop folder_open_file

'this will loop through the folder and look for files with .xls extension until
all files are looped

call Process() 'calls Process to do processing

end sub"



Thank you and appreciate your help!

 

[jbeaucaire]

Personally, I would keep fiddling until I got the ISERROR test working somehow since that's what you're really checking for, right?

Anyway, you could check for number or text:

If IsNumeric(Range("M4")) Or IsText(Range("M4")) Then
    ActiveSheet.Cells(1, 1) = "Mr. John is" & Range("M4") & "yrs. old."

1) Don't save elsewhere, save itself back in its original spot, then use the NAME method to move the file.

 Name MyPath & MyBook As MyOTHERPath & MyBook

2) Hmm, so you don't have that part either?

Option Explicit

Sub Process()
Dim MyPath As String, MyOTHERPath As String
Dim MyBook As String, wbDATA As Workbook

MyPath = "C:\My Documents\Excel Files\New\"         'don't forget the \ at the end
MyOTHERPath = "C:\My Documents\Excel Files\Done\"   'don't forget the \ at the end

MyBook = Dir(MyPath & "*.xls")                      'get first filename

    Do While Len(MyBook) > 0                         'once for each file
        Set wbDATA = Workbooks.Open(MyPath & MyBook)
        Range("M4").Formula = _
            "=VLOOKUP(M1,'C:\BACKUP \[Workbook_WITH_LIST.XLS]Sheet1'!$A:$N,2,FALSE)"
        
        If IsNumeric(Range("M4")) Or IsText(Range("M4")) Then
            ActiveSheet.Cells(1, 1) = "Mr. John is" & Range("M4") & "yrs. old."
        Else
            'do your other stuff
            '
            '
        End If
        
        wbDATA.Close True           'close opened book, saving changes
        Name MyPath & MyBook As MyOTHERPath & MyBook    'move the file
    
        MyBook = Dir                'get next filename
    Loop

End Sub

 

[rence19]

Dear jbeaucaire,

Sorry for the late reply. I already got it! thank you so much for the help!

Cheers!!!!!