how do I pass values to sub's?

[sup191]

I'm having trouble trying to figure out how to pass information to
subroutine. I'm hoping to cut down on a little code for cleaniness
but mostly to learn more about how to use VB in Excel. I have fou
columns that automatically have certain characters stripped out o
them. As of now, I'm calling the subs using:

If Not (Intersect(Target, Range("F:F")) Is Nothing) Then
Application.EnableEvents = False
NARemovalF
End If


And the sub looks like:


Sub NARemovalF()
Dim lLastRow As Long
Dim Sh1 As Worksheet, rng As Range

Set Sh1 = ThisWorkbook.Worksheets("Form")
lLastRow = Sh1.Cells(Rows.Count, "F").End(xlUp).Row
Set rng = Sh1.Range("F2", "F2" & lLastRow)

rng.Replace _
What:="N/A", Replacement:="", _
searchorder:=xlByRows, MatchCase:=True

rng.Replace _
What:="n/a", Replacement:="", _
searchorder:=xlByRows, MatchCase:=True

rng.Replace _
What:="NA", Replacement:="", _
searchorder:=xlByRows, MatchCase:=True

rng.Replace _
What:="na", Replacement:="", _
searchorder:=xlByRows, MatchCase:=True

Set Sh1 = Nothing
Set rng = Nothing
End Sub



I have the same sub copied three other times for columns J, L, and N.
I know there has to be an easy way to pass the column letter to the su
so it will replace the "F" and "F2" information with the other colum
letters. How do I pass the correct info and use that info to replac
the cell ranges?


I know I've been posting tons of questions lately, but everybody ha
been very helpful and I'm learning so much from these forums. Thank
in advance!

 

[Chip Pearson]

There are two steps to passing variables or values between
procedures. The first is to declare the called procedure such
that it accepts arguments. For example,

Sub NARemovalF(Rng As Range)

This specifies that NARemovalF will accept a variable named Rng
of type Range. The second step is to pass the variable itself to
the called procedure. For example in the calling procedure,

NARemovalF Rng:=Range("F:F")

The passes the range F:F to the NARemovalF procedure. This
procedure can the use the variable Rng in any way it sees fit.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Tom Ogilvy]

Sub NARemovalF(icol as Long)
Dim lLastRow As Long
Dim Sh1 As Worksheet, rng As Range

Set Sh1 = ThisWorkbook.Worksheets("Form")
lLastRow = Sh1.Cells(Rows.Count, icol).End(xlUp).Row
Set rng = Sh1.Range(sh1.Cells(2,icol), sh1.Cells(lLastRow,icol))

rng.Replace _
What:="N/A", Replacement:="", _
searchorder:=xlByRows, MatchCase:=True

rng.Replace _
What:="n/a", Replacement:="", _
searchorder:=xlByRows, MatchCase:=True

rng.Replace _
What:="NA", Replacement:="", _
searchorder:=xlByRows, MatchCase:=True

rng.Replace _
What:="na", Replacement:="", _
searchorder:=xlByRows, MatchCase:=True

Set Sh1 = Nothing
Set rng = Nothing
End Sub


If Not Intersect(Target, Range("F:F")) Is Nothing Then
Application.EnableEvents = False
NARemovalF 5
End If

 

[sup191]

Do you guys ever sleep?!?

Talk about a fast response. Thank you both for sheding some light o
how subs work! I now have everything working and am adapting what I'v
learned to a couple other issues I wanted to play with.

Take care and thanks

 

[jamieuk]

Do you guys ever sleep?!?

Tom's always been like this but I'm getting increasingly worried about
Frank Kabel ...

 

[Frank Kabel]

Tom's always been like this but I'm getting increasingly worried about
Frank Kabel ...

why sleep - just wasted time ;-)
To be honest, I'm the best customer of Starbuck's in Frankfurt....

Frank