| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arguments to a module in a Cell
I pass a string from a cell to a module.
Assumming A1 contains the string var 'Procurement' I execute a Function or Sub based on this string in cell A1 =PopulateData("Procurement") However this does not give me the Range value 'Range("D" & LRow).Value'???? If I change the program to a Subroutine is works Function PopulateData(var As Variant) As String Dim ServiceGroup As Variant Dim SName As String Dim SDescription As String Dim LRow As Long Dim LFound As Boolean Sheets("Vendor Management").Select 'ServiceGroup = Range("A1").Value ServiceGroup = var LFound = False LRow = 2 Sheets("Input data").Select GetRng LRow Do While LFound = False If Range("D" & LRow).Value = ServiceGroup Then LFound = True SName = Range("A" & LRow).Value SDescription = Range("G" & LRow).Value Sheets("Service List").Select Range("B8").Value = SName Range("B9").Value = SDescription ElseIf IsEmpty(Range("A" & LRow).Value) = True Then 'MsgBox ("No match was found.") 'Exit Sub End If LRow = LRow + 1 Loop End Function -- Jeff :-) |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Passing arguments to a module in a Cell
Why are you under the impression that you want a function? Functions return
values (in you case of type string) but you do not actually return anything. Are you intending to make this into a UDF called from a cell? If so that is not going to work for you as UDF's can not modify cells other than the value the one that they are placed in... -- HTH... Jim Thomlinson "Jeff" wrote: I pass a string from a cell to a module. Assumming A1 contains the string var 'Procurement' I execute a Function or Sub based on this string in cell A1 =PopulateData("Procurement") However this does not give me the Range value 'Range("D" & LRow).Value'???? If I change the program to a Subroutine is works Function PopulateData(var As Variant) As String Dim ServiceGroup As Variant Dim SName As String Dim SDescription As String Dim LRow As Long Dim LFound As Boolean Sheets("Vendor Management").Select 'ServiceGroup = Range("A1").Value ServiceGroup = var LFound = False LRow = 2 Sheets("Input data").Select GetRng LRow Do While LFound = False If Range("D" & LRow).Value = ServiceGroup Then LFound = True SName = Range("A" & LRow).Value SDescription = Range("G" & LRow).Value Sheets("Service List").Select Range("B8").Value = SName Range("B9").Value = SDescription ElseIf IsEmpty(Range("A" & LRow).Value) = True Then 'MsgBox ("No match was found.") 'Exit Sub End If LRow = LRow + 1 Loop End Function -- Jeff :-) |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Passing arguments to a module in a Cell
Your right I don't need to return a value
but rather pass a cell value to a subroutine as shown in my code. I need to change the function to a subroutine. I can manually step through a subroutine version on my code but can't seem to figure out how to pass a value in a cell to the subroutine? Please help :-) -- Jeff "Jim Thomlinson" wrote: Why are you under the impression that you want a function? Functions return values (in you case of type string) but you do not actually return anything. Are you intending to make this into a UDF called from a cell? If so that is not going to work for you as UDF's can not modify cells other than the value the one that they are placed in... -- HTH... Jim Thomlinson "Jeff" wrote: I pass a string from a cell to a module. Assumming A1 contains the string var 'Procurement' I execute a Function or Sub based on this string in cell A1 =PopulateData("Procurement") However this does not give me the Range value 'Range("D" & LRow).Value'???? If I change the program to a Subroutine is works Function PopulateData(var As Variant) As String Dim ServiceGroup As Variant Dim SName As String Dim SDescription As String Dim LRow As Long Dim LFound As Boolean Sheets("Vendor Management").Select 'ServiceGroup = Range("A1").Value ServiceGroup = var LFound = False LRow = 2 Sheets("Input data").Select GetRng LRow Do While LFound = False If Range("D" & LRow).Value = ServiceGroup Then LFound = True SName = Range("A" & LRow).Value SDescription = Range("G" & LRow).Value Sheets("Service List").Select Range("B8").Value = SName Range("B9").Value = SDescription ElseIf IsEmpty(Range("A" & LRow).Value) = True Then 'MsgBox ("No match was found.") 'Exit Sub End If LRow = LRow + 1 Loop End Function -- Jeff :-) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Passing arguments to a module in a Cell
Your code should work... give this a try though... It uses find which should
be much more efficient... Public Sub PopulateData(ServiceGroup As Variant) dim rngToSearch As Range dim rngFound as range set rngToSearch = Sheets("Input data").columns("D") on error resume next set rngFound = rngtoSearch.Find(What:= ServiceGroup, _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ MatchCase:=True) On Error Goto 0 if rngfound is nothing then msgbox "Sorry, " & ServiceGroup & " was not found." else with Sheets("Service List") .Range("B8").Value = rngfound.offset(0, -3).value .Range("B9").Value = rngfound.offset(0, 3).value end if End Sub -- HTH... Jim Thomlinson "Jeff" wrote: Your right I don't need to return a value but rather pass a cell value to a subroutine as shown in my code. I need to change the function to a subroutine. I can manually step through a subroutine version on my code but can't seem to figure out how to pass a value in a cell to the subroutine? Please help :-) -- Jeff "Jim Thomlinson" wrote: Why are you under the impression that you want a function? Functions return values (in you case of type string) but you do not actually return anything. Are you intending to make this into a UDF called from a cell? If so that is not going to work for you as UDF's can not modify cells other than the value the one that they are placed in... -- HTH... Jim Thomlinson "Jeff" wrote: I pass a string from a cell to a module. Assumming A1 contains the string var 'Procurement' I execute a Function or Sub based on this string in cell A1 =PopulateData("Procurement") However this does not give me the Range value 'Range("D" & LRow).Value'???? If I change the program to a Subroutine is works Function PopulateData(var As Variant) As String Dim ServiceGroup As Variant Dim SName As String Dim SDescription As String Dim LRow As Long Dim LFound As Boolean Sheets("Vendor Management").Select 'ServiceGroup = Range("A1").Value ServiceGroup = var LFound = False LRow = 2 Sheets("Input data").Select GetRng LRow Do While LFound = False If Range("D" & LRow).Value = ServiceGroup Then LFound = True SName = Range("A" & LRow).Value SDescription = Range("G" & LRow).Value Sheets("Service List").Select Range("B8").Value = SName Range("B9").Value = SDescription ElseIf IsEmpty(Range("A" & LRow).Value) = True Then 'MsgBox ("No match was found.") 'Exit Sub End If LRow = LRow + 1 Loop End Function -- Jeff :-) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Passing arguments to a module in a Cell
thank you ...
Jim If Cell A1 contains 'Procurement' this string value should be passed to the subroutine like this.. =PopulateData("Procurement") ?? so I need this instead = PopulateData(A1) so what ever string is entered in A1 will execute the sub. I'm getting hung up on the basic since I new to Excel. Jeff :-) -- Jeff "Jim Thomlinson" wrote: Your code should work... give this a try though... It uses find which should be much more efficient... Public Sub PopulateData(ServiceGroup As Variant) dim rngToSearch As Range dim rngFound as range set rngToSearch = Sheets("Input data").columns("D") on error resume next set rngFound = rngtoSearch.Find(What:= ServiceGroup, _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ MatchCase:=True) On Error Goto 0 if rngfound is nothing then msgbox "Sorry, " & ServiceGroup & " was not found." else with Sheets("Service List") .Range("B8").Value = rngfound.offset(0, -3).value .Range("B9").Value = rngfound.offset(0, 3).value end if End Sub -- HTH... Jim Thomlinson "Jeff" wrote: Your right I don't need to return a value but rather pass a cell value to a subroutine as shown in my code. I need to change the function to a subroutine. I can manually step through a subroutine version on my code but can't seem to figure out how to pass a value in a cell to the subroutine? Please help :-) -- Jeff "Jim Thomlinson" wrote: Why are you under the impression that you want a function? Functions return values (in you case of type string) but you do not actually return anything. Are you intending to make this into a UDF called from a cell? If so that is not going to work for you as UDF's can not modify cells other than the value the one that they are placed in... -- HTH... Jim Thomlinson "Jeff" wrote: I pass a string from a cell to a module. Assumming A1 contains the string var 'Procurement' I execute a Function or Sub based on this string in cell A1 =PopulateData("Procurement") However this does not give me the Range value 'Range("D" & LRow).Value'???? If I change the program to a Subroutine is works Function PopulateData(var As Variant) As String Dim ServiceGroup As Variant Dim SName As String Dim SDescription As String Dim LRow As Long Dim LFound As Boolean Sheets("Vendor Management").Select 'ServiceGroup = Range("A1").Value ServiceGroup = var LFound = False LRow = 2 Sheets("Input data").Select GetRng LRow Do While LFound = False If Range("D" & LRow).Value = ServiceGroup Then LFound = True SName = Range("A" & LRow).Value SDescription = Range("G" & LRow).Value Sheets("Service List").Select Range("B8").Value = SName Range("B9").Value = SDescription ElseIf IsEmpty(Range("A" & LRow).Value) = True Then 'MsgBox ("No match was found.") 'Exit Sub End If LRow = LRow + 1 Loop End Function -- Jeff :-) |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Passing arguments to a module in a Cell
That is because functions called from a worksheet cannot change cells, they
only return a result. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jeff" wrote in message ... I pass a string from a cell to a module. Assumming A1 contains the string var 'Procurement' I execute a Function or Sub based on this string in cell A1 =PopulateData("Procurement") However this does not give me the Range value 'Range("D" & LRow).Value'???? If I change the program to a Subroutine is works Function PopulateData(var As Variant) As String Dim ServiceGroup As Variant Dim SName As String Dim SDescription As String Dim LRow As Long Dim LFound As Boolean Sheets("Vendor Management").Select 'ServiceGroup = Range("A1").Value ServiceGroup = var LFound = False LRow = 2 Sheets("Input data").Select GetRng LRow Do While LFound = False If Range("D" & LRow).Value = ServiceGroup Then LFound = True SName = Range("A" & LRow).Value SDescription = Range("G" & LRow).Value Sheets("Service List").Select Range("B8").Value = SName Range("B9").Value = SDescription ElseIf IsEmpty(Range("A" & LRow).Value) = True Then 'MsgBox ("No match was found.") 'Exit Sub End If LRow = LRow + 1 Loop End Function -- Jeff :-) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sub calling and passing arguments | Excel Programming | |||
| Passing arguments from VBA to DLL | Excel Programming | |||
| passing arguments to events | Excel Programming | |||
| Passing Arguments | Excel Programming | |||
| Passing arguments to a sub routine... | Excel Programming | |||
Hybrid Mode
