how to display an error from macro

macro_user

Board Regular
Joined
Mar 9, 2009
Messages
101
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...
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try this

Code:
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

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

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Another possibility:

Code:
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

Board Regular
Joined
Mar 9, 2009
Messages
101

ADVERTISEMENT

Thank you... this works!!!

Also, i use FOR loops to run through the cells in sheet1 and move them to an array.. so suppose,
Code:
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Why not just loop around the range?

Code:
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

Board Regular
Joined
Mar 9, 2009
Messages
101
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Maybe this is what you want:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,477
Messages
5,601,893
Members
414,479
Latest member
Beau the dog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top