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"?
...
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.
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.