How to keep the value of a variable between 2 Sub (same module)

[ValerieT]

Hello

I want to define a value to some variable (columns positioning) and keep this value for all subs I have in a module
I thought I could use " Static".. but I can't find how it work... I tried to put it everywhere :-)

In this example, I want the value of Col_Name (defined in Sub My_Columns) being available in Sub My_Nameprocess(). I know I could store it into a cells, but is there another simple way?

Static Sub My_Columns()
Static Col_Name as integer
Static Col_ID as integer
For i = 1 To 35
 If Cells(1, i) = "Name" Then
    Col_Name = i
    Exit For
 End If
Next i
If Col_Name = 0 Then
     MsgBox ("Column ""Name"" not found, check your column header")
End If
End Sub



Sub My_Nameprocess()
For i = 1 To Endlign
 If Cells(i, Col_Name) = 0 Then
    ....
    .....
 End If
Next i
End Sub

Thanks in advanced!

[Kenneth Hobs]

What is the scope of use? Is it current session use or current and future use?

For the first case, I generally just pass the value or object to the other Sub(s) or Functions. Or, I Dim it as Public in a Module. For the 2nd case, storing in a cell is one of 5 methods that I can think of offhand.

[SamT]

Declare the variable at the top of the module, before any subs

Dim Col_Name As Long

Sub My_Columns()
Dim Found As Range

Set Found = Rows(1).Find("Name")
If Found Is Nothing Then
    MsgBox ("Column ""Name"" not found, check your column header")
Else
Col_Name = Found.Column
End If

End Sub

Sub My_Nameprocess()
  With Columns(Col_Name)
    For i = 1 To Endlign
        If Cells(i) = 0 Then
            ....
            .....
        End If
    Next i
End Sub

[ValerieT]

I think I did that already (both suggestion of PUBLIC before any other sub), but COl_Name keep = 0 in Sub My_Nameprocess...

I'll try again, and by the way, thanks for the nicest

 Found = Rows(1).Find("Name")

[ValerieT]

I apologizes to every one, I must be very tired. It works perfectly with Public (my first try) if you THINK about running the 1st macro !!!

(5:00 PM, South of France, temperature warmer than summer time, and Italian restaurant during lunch time = No more brain available)

Thans everyone!

[Aussiebear]

Perfectly understandable.