Sleeper: Help working with ranges doing text comparisons

[everytime]

hi there , is there any try end try statement .. ?? it gives me an error when using end try ...

another thing is how do i go back to the last active worksheet ??

the method below compares with 2 words and selects everything in between , then how do i select the last few rows with nothing to compare to ?

Sub test2() 
	Dim r1 As Range, r2 As Range, rSelect As Range 
	Dim x As Long, y As Long 
	With ActiveSheet.UsedRange 
		Set r1 = .Find("on", LookIn:=xlValues) 
		If Not r1 Is Nothing Then 
			x = r1.Row 
		End If 
		Set r2 = .Find("off", LookIn:=xlValues)
		If Not r2 Is Nothing Then 
			y = r2.Row 
		End If 
		Set rSelect = Range("a" & x, Range("a" & y)) 
		rSelect.EntireRow.Select 
	End With 
 
End Sub

[xCav8r]

Are you asking about Try...Catch from VB? If so, that's not supported in VBA. If not, I didn't understand what you're asking.

Anyway, are you asking a question about Access or Excel? It looks like Excel. Would you like me to move this thread to the Excel forum?

[everytime]

sry posted in wrong section .. it would be nice to move it .. thanks ..

ya i was asking about the try and catch in vb .. is there some way to catch error in vba then ?

[xCav8r]

Function ProcedureName(ArgumentList) As DataType
   ' Procedure comments.
   ' Declare local variables and constants.
On Error GoTo ProcedureName_Err
   ' Procedure code.
ProcedureName = True (or some other return value)
ProcedureName_End:
   ' Cleanup code that closes open files and sets object variables = Nothing.
   Exit Function
ProcedureName_Err:
   ProcedureName = False
   Select Case Err.Number
      Case AnticipatedError#1
         ' Handle error #1.
      Case AnticipatedError#2
         ' Handle error #2.
      Case UnAnticipatedErrors
         ' Handle unanticipated error.
      Case Else
         ' Handle unforseen circumstances.
   End Select
   Resume ProcedureName_End
End Function

http://msdn.microsoft.com/library/de...lingerrors.asp

Moving to Excel forum as requested.

[everytime]

thx for the reply and move .. may i also add how come there are 2 end functions with only 1 function being declared ... ?

[xCav8r]

I'll let the Excel guys help you with the Excel part. As for VBA error handling, you should read the article I linked to if you haven't already (NB: VBA error handling is crap). BTW, I only see one End Function in the example.

[xCav8r]

[everytime]

dont think so ..

btw about the select cases .. can the cases be in a seperate sub / function instead of being in the place where the coding are .. ??

can show an example of it in action ??

just to add is there a way to create menu and assign macros them them when the file is open ?? i tried using the macro recorder but it does not work ??

another thing i tried the code below in the so called gobal variable part of the form but it gives me an error .. do i have to import something ??

Dim a As Date 
a = Year(TODAY()) 

[Bob Phillips]

dont think so ..

Think what?

btw about the select cases .. can the cases be in a seperate sub / function instead of being in the place where the coding are .. ??

The case action can call a sub or function just as anywhere else.

just to add is there a way to create menu and assign macros them them when the file is open ?? i tried using the macro recorder but it does not work ??

An example

Sub PartsMenu()
Dim HelpMenu As CommandBarControl
Dim MainMenu As CommandBarPopup
Dim MenuItem As CommandBarControl
Dim Submenuitem As CommandBarButton
    '   Deletes menu if it exits
    Call DeleteMenu
    '   Find the help menu
    Set HelpMenu = CommandBars(1).FindControl(ID:=30010)
    If HelpMenu Is Nothing Then
    '       Add the menu to the end
    Set MainMenu = CommandBars(1).Controls. _
        Add(Type:=msoControlPopup, temporary:=True)
    Else
        '     Add menu before help
    Set MainMenu = CommandBars(1).Controls. _
        Add(Type:=msoControlPopup, before:=HelpMenu.Index, _
            temporary:=True)
    End If
    '   Add caption
    MainMenu.Caption = "&Parts Utility"
    '   Searching for parts
    Set MenuItem = MainMenu.Controls.Add _
        (Type:=msoControlButton)
    With MenuItem
        .Caption = "&Search Parts..."
        .FaceId = 48
        .ShortcutText = "Ctrl+Shift+S"
        .OnAction = "SetupSearch"
    End With
    '   LO / Remaining printout
    Set MenuItem = MainMenu.Controls.Add _
        (Type:=msoControlButton)
    With MenuItem
        .Caption = "&Generate Parts Review..."
        .FaceId = 285
        .ShortcutText = "Ctrl+Shift+D"
        .OnAction = "LORemaining"
    End With
    '   View summary sheet
    Set MenuItem = MainMenu.Controls.Add _
        (Type:=msoControlPopup)
    With MenuItem
        .Caption = "Sub menu"
    End With
Set Submenuitem = MenuItem.Controls.Add _
        (Type:=msoControlButton)
    With Submenuitem
        .Caption = "&View Summary..."
        .FaceId = 592
        .OnAction = "Summary"
    End With
    '   Print summary sheet
    Set Submenuitem = MenuItem.Controls.Add _
        (Type:=msoControlButton)
    With Submenuitem
        .Caption = "Print Summary"
    '    .Application = 364
        .OnAction = "PrintSummary"
    End With
End Sub


Sub DeleteMenu()
    On Error Resume Next
    Application.CommandBars(1).Controls("&Parts Utility").Delete
    On Error GoTo 0
End Sub

I would add this to the worrkbook open event. To do this, follow these steps
- goto into the VB IDE
- in the explorer pane on the left, select the 'mymacro.xls' workbook
- double-click the 'ThisWorkbook' class module under the 'Microsoft Excel
Objects' folder (may need to to click a plus sign beside Microsoft Excel
Objects to expoand it)
- a code window should open, and in the 'General' dropdown, select Workbook
- this should create a Workbook_Open event, paste the above code into that.

another thing i tried the code below in the so called gobal variable part of the form but it gives me an error .. do i have to import something ??

Dim a As Date
a = Year(TODAY())

The assighnment to a has to be in a procedure, Userfoprm_Activate looks good.

[everytime]

may i ask what is .FaceId = 48 ...??

and the assignment can only be done in each procedure ( as in sub or function ) ?

[Bob Phillips]

may i ask what is .FaceId = 48 ...??

it is an assignment of an icon to tyhe menu item

and the assignment can only be done in each procedure ( as in sub or function ) ?

Don't understand what you are asking.