Public Variables

[Avionics Man]

All right, My sub works fine with a locally declared variable. When I try to make it a public declaration, I get the error "object required". The line

Public MR2 as integer
is at the top of the module with a line between it and the sub. It won't let the line be in the same procedure area.

I want to use MR2 in all the subs, not just the one

Help!

 

[dk]

Hi

I am assuming that this is a run-time error (as opposed to a compile time error from choosing Debug, Compile)? If so, can you please post the line that the error is occurring on (and preferably the entire procedure if not too large).

DK

 

[Avionics Man]

Ok, It's actually a compile error after I try making a public variable. Here's the code:
Sub AME_COPY()
'
' AME_COPY Macro
' This bit copies the relevant line from the AME sheet to the sort page.
'
Sheets("SORT").Select
Range("A1:Z200").Select
Selection.ClearContents
Range("A5").Select
Set MR2 = ActiveCell
Sheets("AME").Select
Range("A5").Select
Set MyRange = ActiveCell
Dim MyTitle As Object
Do
MyRange.Select
If ActiveCell.Offset(0, 3) Like "[Y,y][E,e][S,s]" Then
Set MyTitle = ActiveCell
Do
MyRange.Select
If ActiveCell.Offset(0, 4) Like "[X,x]" Then
Range(ActiveCell.Offset(0, 5), ActiveCell.Offset(0, 7)).Select
Selection.Copy
Sheets("SORT").Select
MR2.Select
ActiveCell = MyTitle
Selection.Offset(0, 4).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
MR2.Select
Set MR2 = ActiveCell.Offset(1, 0)
Sheets("AME").Select
MyRange.Select
ActiveCell.Offset(1, 0).Select
Set MyRange = ActiveCell
Else
MyRange.Select
ActiveCell.Offset(1, 0).Select
Set MyRange = ActiveCell
End If
Loop Until Not (IsEmpty(ActiveCell))
Else
Selection.End(xlDown).Select
Set MyRange = ActiveCell
End If
Loop Until IsEmpty(ActiveCell.Offset(0, 5))
Sheets("SORT").Select
Range("A5").Select

End Sub

I would like to make MR2 public or at least available to the whole module

 

[shg]

Public variables get declared at the top of a standard code module, before any procedures -- right after Option Explicit.

Also, and integer is a scalar variable; you can't "Select" it, you can't Set it. Maybe you want it to be a Range.

 

[Kenneth Hobson]

The first line of your code should be:

Option Explicit

Declare the public variable in a Module. It can not be declared in your Sub then. You may have to declare it in a Private object as well if used there but the Module declaration is required as well.

I recommend that you try not to use Select so much.

Please use code tags. In the Go Advanced button, click the # button and paste your code.

 

[Avionics Man]

Thanks for the advice, I will try these couple o things and get back. You might've guessed that I am a complete and utter newbie doing what I have to to get the job done here at work. Writing these Excel VBAs are just a small portion of my workday. I do very much appreciate the help from professional people.

 

[baitmaster]

I make it something like this:

Option Explicit
Public MyRange As Range, MR2 As Range
Sub AME_COPY()
' This bit copies the relevant line from the AME sheet to the sort page.
Dim MyTitle As Object
Sheets("SORT").Range("A1:Z200").ClearContents
Set MR2 = Sheets("SORT").Range("A5")
Set MyRange = Sheets("AME").Range("A5")
With MyRange
    Do
        If .Offset(0, 3) Like "[Y,y][E,e][S,s]" Then
        Set MyTitle = MyRange
        
            Do
            
                If .Offset(0, 4) Like "[X,x]" Then
                    Range(.Offset(0, 5), .Offset(0, 7)).Copy
                    
                    
                    MR2 = MyTitle
    
                    MR2.Offset(0, 4).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
                        xlNone, SkipBlanks:=False, Transpose:=False
    
    
                    Set MR2 = MR2.Offset(1, 0)
                    
                    Set MyRange = MyRange.Offset(1, 0)
                Else
                    Set MyRange = MyRange.Offset(1, 0)
                End If
            Loop Until Not (IsEmpty(ActiveCell))
        Else
        Set MyRange = MyRange.End(xlDown)
    
    End If
Loop Until IsEmpty(ActiveCell.Offset(0, 5))
End Sub

Apologies if I've got muddled somewhere along the line. Hopefully this is something you can work through and understand ok - look at how I've structured it all, you should find it helpful... and not a SELECT in sight