Solved: Convert to function

[justdriving]

Hi,

I have been suggested following program to replace values in Col G.
Is this possible to convert this procedure into a function, so that I can use it for any Column (not only for Col G). This idea will help me convert remaining procedures.


[vba]

Public Sub ReplaceColG()
Dim lLR As Long
Dim r As Range
lLR = Range("G" & Rows.Count).End(xlUp).Row
For i = 2 To lLR
If Range("G" & i).Value <> "" Then
Set r = Columns("C:C").Find(What:=Range("G" & i).Value, _
LookIn:=xlValues, LookAt:=xlWhole)
If Not r Is Nothing Then
Range("G" & i).Value = r.Offset(, -1).Value
End If
End If
Next i
End Sub

[/vba]

[Kenneth Hobs]

A function returns a value. What value did you expect to return?

[VBA]
Sub Test_ReplaceCol()
ReplaceCol "G"
End Sub

Public Sub ReplaceCol(col As String)
Dim lLR As Long
Dim r As Range
lLR = Range(col & Rows.Count).End(xlUp).Row
For i = 2 To lLR
If Range(col & i).Value <> "" Then
Set r = Columns("C:C").Find(What:=Range(col & i).Value, _
LookIn:=xlValues, LookAt:=xlWhole)
If Not r Is Nothing Then
Range(col & i).Value = r.Offset(, -1).Value
End If
End If
Next i
End Sub[/VBA]

[justdriving]

Hi, you are right. Apologies, I could not write it well. I thank that you have given it a form of Sub... Excellent... I have a query for which I need your advice ...

[vba]

Public Sub ReplaceCol(col As String)
[/vba]

If I make above as Private, then can following access that Private Sub?

[VBA]
Sub Test_ReplaceCol()
[/VBA]


How can I make this sub to be "private"?

[GTO]

...[vba]

Public Sub ReplaceCol(col As String)
[/vba]

If I make above as Private, then can following access that Private Sub?

[vba]
Sub Test_ReplaceCol()
[/vba]

Call it from the same module. Or to (at least IMO) make it more unnecessarily complicated, use .Run.

How can I make this sub to be "private"?

PLease refer to vba Help. Look up Sub or Function Statement.

Cat-killin' curiousity has the best of me it appears. Why would you want/need the sub Kenneth provided to be Private? It has a required parameter, its not as if its going to be accidently accessed.

[justdriving]

Thanks GTO, actually Alt+F8 can display Public Sub, which I wish to avoid. Apologies, I should have changed this in my question. Your help is appreciated.

[GTO]

I am the one to apologize as I may have misunderstood. Are you wanting to make Sub Test_ReplaceCol() private? Or Public Sub ReplaceCol(col As String) Private?

[justdriving]

Call it from the same module. Or to (at least IMO) make it more unnecessarily complicated, use .Run.

I see that you answered above. Both Private Sub can be called, if they reside in same Module. I am not aware about RUN. Can you please help how it could be used?

[Bob Phillips]

Look at Application.Run in VBA Help.

[justdriving]

A function returns a value. What value did you expect to return?

[vba]
Sub Test_ReplaceCol()
ReplaceCol "G"
End Sub

Public Sub ReplaceCol(col As String)
Dim lLR As Long
Dim r As Range
lLR = Range(col & Rows.Count).End(xlUp).Row
For i = 2 To lLR
If Range(col & i).Value <> "" Then
Set r = Columns("C:C").Find(What:=Range(col & i).Value, _
LookIn:=xlValues, LookAt:=xlWhole)
If Not r Is Nothing Then
Range(col & i).Value = r.Offset(, -1).Value
End If
End If
Next i
End Sub[/vba]

I thank for your help.

Is there any way, that I could modify above program like this: -

[vba]
Sub Test_ReplaceCol()

ReplaceCol "G" , "Sheet1", "ABC.xls"
End Sub
[/vba]

By doing this, I can ensure that all operations happen on Sheet1 of ABC.xls file. I also will have option to change sheet name and file name, when required.

Your advice is requested.