Passing value to Public Function (Excel) to find elsewhere - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Passing value to Public Function (Excel) to find elsewhere

sucrosesucrose Posts: 2Member
I'm trying to create the following macro to automate calculations in a spreadsheet.

Enter certain value in excel cell... this value then gets passed to a Public Function (as a Variant/String, right?)

Within the public function, the looped find method (from Help files) is used to find this passed variable in another worksheet.

The problem is that if something is passed to the function, the find method always finds nothing. At first I thought, maybe, there was some discrepancy between what was being passed and the results I'd be searching through, but no... even if I search for an internally defined variable (not the one I am passing) it fails to work... Remove the passing, and bham, it works like a charm...

Why would this be the case? is the Find method only for non-public functions? If so, or whatever, is there another way that I might do this?

Thanks a lot!

Comments

  • StannyBoyStannyBoy Posts: 115Member
    You'll have to post more details. I tried this (which I think is what you might be doing) and it worked fine.

    [code]
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Address = "$A$1" Then gosearch Target
    End Sub

    Sub gosearch(searchval)
    Sheets(2).Activate
    ActiveSheet.Cells.Find(searchval).Select
    End Sub
    [/code]

    I've had problems in the past with errant spaces and so on, so this could be the problem? Anyway, post some more details of exactly what you are tring to do and I'll sort it out for you.

    SB





    : I'm trying to create the following macro to automate calculations in a spreadsheet.
    :
    : Enter certain value in excel cell... this value then gets passed to a Public Function (as a Variant/String, right?)
    :
    : Within the public function, the looped find method (from Help files) is used to find this passed variable in another worksheet.
    :
    : The problem is that if something is passed to the function, the find method always finds nothing. At first I thought, maybe, there was some discrepancy between what was being passed and the results I'd be searching through, but no... even if I search for an internally defined variable (not the one I am passing) it fails to work... Remove the passing, and bham, it works like a charm...
    :
    : Why would this be the case? is the Find method only for non-public functions? If so, or whatever, is there another way that I might do this?
    :
    : Thanks a lot!
    :


  • sucrosesucrose Posts: 2Member

    Hi

    This is the code that I have been trying to use... as you can I have the search parameter being passed and then in the loop I tell it what the parameter is... is the pass is removed, the find works perfectly, if it is there, find always returns nothing

    Thanks

    Function TA_Tally(Cable)


    Cable = "D93-DJB-003"


    FBM2013W = 0
    FBM201 = 0
    FBM203 = 0
    FBM205_In = 0
    FBM205_Out = 0
    FBM206 = 0
    FBM237 = 0
    FBM243 = 0
    FBM246 = 0
    FBM207B = 0
    FBM242_D = 0
    FBM242_W24 = 0
    FBM242_W120 = 0


    With Worksheets("Cable Table").Range("B1:B984")

    Set c = .Find(Cable, LookIn:=xlValues)
    If Not c Is Nothing Then
    firstAddress = c.Address
    Do
    Current_Cell = c.Address

    New_Current = Mid(Current_Cell, 3, 6)

    New_Greater = "$A" + New_Current

    Range(New_Greater).Select

    TA_Type = ActiveCell.Value

    If TA_Type = "FBM201-3W" Then

    FBM2013W = FBM2013W + 1

    End If

    If TA_Type = "FBM201" Then

    FBM201 = FBM201 + 1

    End If

    If TA_Type = "FBM203" Then

    FBM203 = FBM203 + 1

    End If

    If TA_Type = "FBM205-In" Then

    FBM205_In = FBM205_In + 1

    End If

    If TA_Type = "FBM205-Out" Then

    FBM205_Out = FBM205_Out + 1

    End If

    If TA_Type = "FBM206" Then

    FBM206 = FBM206 + 1

    End If

    If TA_Type = "FBM237" Then

    FBM237 = FBM237 + 1

    End If

    If TA_Type = "FBM246" Then

    FBM246 = FBM246 + 1

    End If

    If TA_Type = "FBM207B" Then

    FBM207B = FBM207B + 1

    End If

    If TA_Type = "FBM242-D" Then

    FBM242_D = FBM242_D + 1

    End If

    If TA_Type = "FBM242-W24" Then

    FBM242_W24 = FBM242_W24 + 1

    End If

    If TA_Type = "FBM242-W120" Then

    FBM242_W120 = FBM242_W120 + 1

    End If

    Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstAddress
    End If

    End With

    MsgBox "FBW201-3W Count = " & FBM2013W

    End Function




    : You'll have to post more details. I tried this (which I think is what you might be doing) and it worked fine.
    :
    : [code]
    : Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    : If Target.Address = "$A$1" Then gosearch Target
    : End Sub
    :
    : Sub gosearch(searchval)
    : Sheets(2).Activate
    : ActiveSheet.Cells.Find(searchval).Select
    : End Sub
    : [/code]
    :
    : I've had problems in the past with errant spaces and so on, so this could be the problem? Anyway, post some more details of exactly what you are tring to do and I'll sort it out for you.
    :
    : SB
    :
    :
    :
    :
    :
    : : I'm trying to create the following macro to automate calculations in a spreadsheet.
    : :
    : : Enter certain value in excel cell... this value then gets passed to a Public Function (as a Variant/String, right?)
    : :
    : : Within the public function, the looped find method (from Help files) is used to find this passed variable in another worksheet.
    : :
    : : The problem is that if something is passed to the function, the find method always finds nothing. At first I thought, maybe, there was some discrepancy between what was being passed and the results I'd be searching through, but no... even if I search for an internally defined variable (not the one I am passing) it fails to work... Remove the passing, and bham, it works like a charm...
    : :
    : : Why would this be the case? is the Find method only for non-public functions? If so, or whatever, is there another way that I might do this?
    : :
    : : Thanks a lot!
    : :
    :
    :


  • StannyBoyStannyBoy Posts: 115Member
    I think your original thoughts were right, it doesn't want to know about the FIND while it's calculating. Here's a solution, which worked fine on a small list, and should be reasonable even on your bigger list.

    [code]
    Function ta_tally2(cable)
    Dim cellLook As Range
    Dim abc As Integer, abd As Integer

    For Each cellLook In Sheets(1).Range("B1:B10")
    If cellLook = cable Then
    Select Case cellLook.Offset(, -1)
    Case "abc"
    abc = abc + 1
    Case "abd"
    abd = abd + 1
    End Select
    End If
    Next

    ta_tally2 = abc

    End Function
    [/code]

    Alternatively, if you are in control of the original list, add another column (column C in this eg) with the formula "=B1&A1" then use the [b]COUNTIF[/b] function thus

    =COUNTIF(C:C,B2&"abc")

    where B2 is the particular cable you are looking for.

    Hope this all helps.
    SB


    :
    : Hi
    :
    : This is the code that I have been trying to use... as you can I have the search parameter being passed and then in the loop I tell it what the parameter is... is the pass is removed, the find works perfectly, if it is there, find always returns nothing
    :
    : Thanks
    :
    : Function TA_Tally(Cable)
    :
    :
    : Cable = "D93-DJB-003"
    :
    :
    : FBM2013W = 0
    : FBM201 = 0
    : FBM203 = 0
    : FBM205_In = 0
    : FBM205_Out = 0
    : FBM206 = 0
    : FBM237 = 0
    : FBM243 = 0
    : FBM246 = 0
    : FBM207B = 0
    : FBM242_D = 0
    : FBM242_W24 = 0
    : FBM242_W120 = 0
    :
    :
    : With Worksheets("Cable Table").Range("B1:B984")
    :
    : Set c = .Find(Cable, LookIn:=xlValues)
    : If Not c Is Nothing Then
    : firstAddress = c.Address
    : Do
    : Current_Cell = c.Address
    :
    : New_Current = Mid(Current_Cell, 3, 6)
    :
    : New_Greater = "$A" + New_Current
    :
    : Range(New_Greater).Select
    :
    : TA_Type = ActiveCell.Value
    :
    : If TA_Type = "FBM201-3W" Then
    :
    : FBM2013W = FBM2013W + 1
    :
    : End If
    :
    : If TA_Type = "FBM201" Then
    :
    : FBM201 = FBM201 + 1
    :
    : End If
    :
    : If TA_Type = "FBM203" Then
    :
    : FBM203 = FBM203 + 1
    :
    : End If
    :
    : If TA_Type = "FBM205-In" Then
    :
    : FBM205_In = FBM205_In + 1
    :
    : End If
    :
    : If TA_Type = "FBM205-Out" Then
    :
    : FBM205_Out = FBM205_Out + 1
    :
    : End If
    :
    : If TA_Type = "FBM206" Then
    :
    : FBM206 = FBM206 + 1
    :
    : End If
    :
    : If TA_Type = "FBM237" Then
    :
    : FBM237 = FBM237 + 1
    :
    : End If
    :
    : If TA_Type = "FBM246" Then
    :
    : FBM246 = FBM246 + 1
    :
    : End If
    :
    : If TA_Type = "FBM207B" Then
    :
    : FBM207B = FBM207B + 1
    :
    : End If
    :
    : If TA_Type = "FBM242-D" Then
    :
    : FBM242_D = FBM242_D + 1
    :
    : End If
    :
    : If TA_Type = "FBM242-W24" Then
    :
    : FBM242_W24 = FBM242_W24 + 1
    :
    : End If
    :
    : If TA_Type = "FBM242-W120" Then
    :
    : FBM242_W120 = FBM242_W120 + 1
    :
    : End If
    :
    : Set c = .FindNext(c)
    : Loop While Not c Is Nothing And c.Address <> firstAddress
    : End If
    :
    : End With
    :
    : MsgBox "FBW201-3W Count = " & FBM2013W
    :
    : End Function
    :
    :
    :
    :
    : : You'll have to post more details. I tried this (which I think is what you might be doing) and it worked fine.
    : :
    : : [code]
    : : Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    : : If Target.Address = "$A$1" Then gosearch Target
    : : End Sub
    : :
    : : Sub gosearch(searchval)
    : : Sheets(2).Activate
    : : ActiveSheet.Cells.Find(searchval).Select
    : : End Sub
    : : [/code]
    : :
    : : I've had problems in the past with errant spaces and so on, so this could be the problem? Anyway, post some more details of exactly what you are tring to do and I'll sort it out for you.
    : :
    : : SB
    : :
    : :
    : :
    : :
    : :
    : : : I'm trying to create the following macro to automate calculations in a spreadsheet.
    : : :
    : : : Enter certain value in excel cell... this value then gets passed to a Public Function (as a Variant/String, right?)
    : : :
    : : : Within the public function, the looped find method (from Help files) is used to find this passed variable in another worksheet.
    : : :
    : : : The problem is that if something is passed to the function, the find method always finds nothing. At first I thought, maybe, there was some discrepancy between what was being passed and the results I'd be searching through, but no... even if I search for an internally defined variable (not the one I am passing) it fails to work... Remove the passing, and bham, it works like a charm...
    : : :
    : : : Why would this be the case? is the Find method only for non-public functions? If so, or whatever, is there another way that I might do this?
    : : :
    : : : Thanks a lot!
    : : :
    : :
    : :
    :
    :


Sign In or Register to comment.