Calling function from cell problem 1

[ensorg]

Hi All,

I have a small VBA function in one of my excel spreadsheets (a simple version of it is pasted below. I pass a part number to this function and it runs through another sheet and collects all instances of this part number.

If I call the procedure via a command button.....
call part("partnumber")
....then the function runs perfectly.

But when I try to call the procedure via a formula....
=part(c16)
.....(where C16 is the cell with the part number) the function doesn't work, it doesn't find anything even though partnumber variable is being passed correctly.

Has anyone seen anything like this before?

Gareth

Public Function part(partnumber As String)

    
    If partnumber = "" Then
        MsgBox "No part entered"
        Exit Function
    End If



With Worksheets("rawpartdata").Range("A1:A1000")
        Set partfind = .Find(partnumber, LookIn:=xlValues)
            If partfind Is Nothing Then
                MsgBox "Invalid Part Number"
                Exit Function
            Else
               stractiverow = partfind.Row
            End If
        
End With


End Function

 

[SkipVought]

Gareth,

Your Function is NOT returning a value. At some place in your function, when you have evaluated to a valid answer, you should have a statement...

  part = TheValue
  Exit Function

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ensorg]

I know it doesn't return anything, like I said its a simplified version.

The problem is when I call the function via code then the find section works but when I call it from a cell =part(c16) then the find section doesn't find anything, even though what is being passed to the function is exactly the same.

 

[SkipVought]

How does the Command Button process get the part number?

Need to see the code where that happens.

I reiterate, your function needs a statement where part is assigned a value.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ensorg]

Sorry maybe I mis-understood.

The command button method gets the partnumber in a very simple way

 partnumber = activecell.value

As I said, this works fine, but this is not the way I want to do it.

When I call the function as such...

 =part(c16)

Then it does not work. I cannot figure out why, as the cell C16 is the same cell that I am using for the "activecell" so it is passing exactly the same value.

 

[SkipVought]

You cannot have a spreadsheet function where the activecell is the argument, because the function MUST return a value to the activecell.

PLEASE POST ALL YOUR CODE! I do not have a crystal ball. I have stated several times that SOMEWHERE in your code you must have...

part = ThePartNumberThatHasBeenSelected

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ensorg]

sorry, but I am confused. Part is not a variable, but it is the name of the function, why would I assign the name of the function a value?

 

[SkipVought]

Because that's the way it gets the vaue returned!!!

A FUNCTION returns a SINGLE VALUE like this trivial example...

Sub MySampleCode()
   x = SayHello
   MsgBox x
End Sub
Function SayHello() As String
  SayHello = "Hello!"
End Function

OR on a sheet...
[tt]
=SayHello()
[/tt]
returns "Hello!" to the cell in which the function resides.

TRIVIAL, but it illustrates just WHAT a function is & HOW a function is used.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ensorg]

Here is all my code. Like I said maybe I am confused and not using a function correctly.

Public Function part(partnumber As String)
'Function is called when partnumber field changes.
    
    If partnumber = "" Then
        MsgBox "No part entered"
        Exit Function
    End If



With Worksheets("rawpartdata").Range("A1:A1000")
        Set partfind = .Find(partnumber, LookIn:=xlValues)
            If partfind Is Nothing Then
                MsgBox "Invalid Part Number"
                Exit Function
            Else
               stractiverow = partfind.Row
            End If
        
End With

With Worksheets("partandstock")
    'description
    .Cells(13, 4).Value = Worksheets("rawpartdata").Cells(stractiverow, 2).Value
    'Prod group
    .Cells(13, 9).Value = Worksheets("rawpartdata").Cells(stractiverow, 3).Value
    'UOM
    .Cells(15, 4).Value = Worksheets("rawpartdata").Cells(stractiverow, 4).Value
    'reorder
    .Cells(15, 9).Value = Worksheets("rawpartdata").Cells(stractiverow, 5).Value
    'min
    .Cells(17, 4).Value = Worksheets("rawpartdata").Cells(stractiverow, 10).Value
    'max
    .Cells(17, 9).Value = Worksheets("rawpartdata").Cells(stractiverow, 9).Value
    'batch
    .Cells(19, 4).Value = Worksheets("rawpartdata").Cells(stractiverow, 11).Value
    'safety
    .Cells(19, 9).Value = Worksheets("rawpartdata").Cells(stractiverow, 8).Value
    'pref
    .Cells(21, 4).Value = Worksheets("rawpartdata").Cells(stractiverow, 6).Value
    'alt
    .Cells(21, 9).Value = Worksheets("rawpartdata").Cells(stractiverow, 7).Value

    'stock
    .Cells(26, 3).Value = Worksheets("rawpartdata").Cells(stractiverow, 12).Value
    'allocated stock
    .Cells(26, 6).Value = Worksheets("rawpartdata").Cells(stractiverow, 13).Value
    'wip stock
    .Cells(26, 9).Value = Worksheets("rawpartdata").Cells(stractiverow, 14).Value
        
End With

End Function

Like I said, it works prefectly if I call it like this.

Private Sub CommandButton1_Click()
Dim mypart As String
mypart = ActiveCell.Value
Call part(mypart)
End Sub

But when I try to call it using the following formula

=part(C16)

The function does not work, the find method does not find anything.

 

[SkipVought]

Again,

a FUNCTION returns a SINGLE VALUE.

Your part routine is NOT a function. It is modifying MULTIPLE VALUES in MULTIPLE PLACES. You are using a function like a SUBROUTINE, which is permissable EXCEPT when you attempt to use it like a FUNCTION -- because

a FUNCTION returns a SINGLE VALUE.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ensorg]

Fine, I apoligise for not knowing that.

Thanks for your help.

 

[SkipVought]

No apology needed.

Just realize that every time you enter a formula in a cell, a single value is returned. That's ALL a function does on a spreadsheet. Nothing can happen anywhere else in any other cell in any sheet in any workbook.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ensorg]

It all becomes clear now! Thanks again for your help.

I tried to do it that way because I didn't want a button. I just wanted them to enter the part number and hit enter for the code to execute.

Is there anyway to do this? Or am I barking up the wrong tree and should just go back to using a button.

 

[SkipVought]

So do you want to execute the code based on selecting a cell with a value? Excel has events that can be trapped, like the Worksheet_SelectionChange event. So you could determine if a cell in a particluar range was selected, then run the code using the activecell as the input value.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ensorg]

Ah OK, thanks, I'll give that a go.