PDA

View Full Version : Solved: Convert to function



justdriving
09-24-2011, 12:42 PM
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.




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

Kenneth Hobs
09-24-2011, 02:10 PM
A function returns a value. What value did you expect to return?


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

justdriving
09-24-2011, 02:15 PM
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 ...



Public Sub ReplaceCol(col As String)


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


Sub Test_ReplaceCol()



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

GTO
09-24-2011, 02:51 PM
...

Public Sub ReplaceCol(col As String)


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


Sub Test_ReplaceCol()



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
09-24-2011, 03:09 PM
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
09-24-2011, 03:17 PM
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
09-24-2011, 03:57 PM
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
09-25-2011, 02:52 AM
Look at Application.Run in VBA Help.

justdriving
09-25-2011, 05:11 AM
A function returns a value. What value did you expect to return?


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

I thank for your help.

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


Sub Test_ReplaceCol()

ReplaceCol "G" , "Sheet1", "ABC.xls"
End Sub


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.