how to display an error from macro

[macro_user]

hi...
i have written some macro in the destination sheet, say sheet2... when i click a button, the macro starts which copies data from an existing worksheet say sheet1 to sheet2 in the same workbook... if at all , somehow, the base sheet, sheet1 is deleted, then i would be getting an error saying "Subscript out of range" in the VB interface... but how will i display this error msg in a msgbox in the excel interface and exit from the sub... i hope im making myself clear...

 

[VoG]

Try this

Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name <> ""
On Error GoTo 0
End Function

Then in your macro you can use

If Not WorksheetExists("Sheet1") Then
    MsgBox "Sheet 1 doesn't exist"
    Exit Sub
End If

 

[jim may]

Post your existing code.

 

[Andrew Poulsom]

Another possibility:

Sub Test()
    Dim Sh As Worksheet
    On Error Resume Next
    Set Sh = Worksheets("Sheet1")
    If Err.Number = 9 Then
        MsgBox "Sheet does not exist"
        Exit Sub
    ElseIf Err <> 0 Then
        MsgBox "Unknown error"
        Exit Sub
    End If
    On Error GoTo 0
'   More code
End Sub

 

[macro_user]

Thank you... this works!!!

Also, i use FOR loops to run through the cells in sheet1 and move them to an array.. so suppose,

sub test()
dim arr() as variant
dim lastrow_in_column_F ,i ,j as integer
lastrow_in_column_F = range("F66536").end(xlUp).row   'data from column F starts_
' from row no.3  , 1st two rows are blank rows
arr = Sheets("sheet1").Range("F3:J" & lastrow).Value
for i = 1 to lastrow       'loop for row
     for j = 1 to 5          'loop for column
           .........
           .........   ' some processing
next
next
end sub

so, suppose if i = lastrow (say 10), then there wont be any value in the array at arr(10,1) right? bcoz there's value in the array only till arr(8,5)... so how do i display that error msg and terminate the sub? I think i'm making myself clear about this...

 

[Andrew Poulsom]

Why not just loop around the range?

Sub test()
    Dim Rng As Range
    Dim lastrow_in_column_F, i As Long, j As Integer
    lastrow_in_column_F = Range("F65536").End(xlUp).Row   'data from column F starts_
    ' from row no.3  , 1st two rows are blank rows
    Set Rng = Sheets("sheet1").Range("F3:J" & lastrow_in_column_F)
    With Rng
        For i = 1 To .Rows.Count       'loop for row
            For j = 1 To .Columns.Count          'loop for column
                MsgBox .Cells(i, j).Value
'               .........
'               .........   ' some processing
            Next j
        Next i
    End With
End Sub

 

[macro_user]

hmmm... this sounds lot easier but since i was new to macros, i guess i dint know the easier way to code... actually, i had used the code(the looping through an array part) which i had mentioned above atleast 6 to 10 times in 30 different macros... so, I'm scared to edit tat as i am close to the deadline... I know this may sound silly but is there some way i can display an error with my existing code? (in the part i have mentioned above)... Apologies for this...

 

[Andrew Poulsom]

Maybe this is what you want:

Sub test()
    Dim arr() As Variant
    Dim lastrow_in_column_F, i As Long, j As Integer
    lastrow_in_column_F = Range("F65536").End(xlUp).Row   'data from column F starts_
'   from row no.3  , 1st two rows are blank rows
    arr = Sheets("sheet1").Range("F3:J" & lastrow_in_column_F).Value
    MsgBox LBound(arr) & " " & UBound(arr)
    For i = LBound(arr) To UBound(arr)       'loop for row
        For j = 1 To 5          'loop for column
            MsgBox arr(i, j)
''           .........
''           .........   ' some processing
        Next j
    Next i
End Sub