Passing arguments to a module in a Cell

[Jeff]

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

 

[Jim Thomlinson]

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...

 

[Bob Phillips]

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]

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

 

[Jim Thomlinson]

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

 

[Jeff]

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