Results 1 to 3 of 3
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    intersection of cells... (2000 sr-1)

    I have arranged a function but naturally not work....
    In CELL_FOUND i want to have the name of cell to insert the value of other Var...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: intersection of cells... (2000 sr-1)

    1) Column A contains text values except for the one value you're looking for - A42 is a number instead of text. Insert an apostrophe in front of the value.

    2) If you want CELL_FOUND to be a Range object, you must use

    Set CELL_FOUND = ...

    3) You have switched rows and columns. Use

    Set IntersectStat = .Cells(Application.Match(SOSPESO, .[A:A], 0), Application.Match(CLng(SPORTELLO), .[2:2], 0))

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: intersection of cells... (2000 sr-1)

    Sub prova()
    Dim SPORTELLO As String
    Dim SOSPESO As String
    SPORTELLO = "4502"
    SOSPESO = "150008160001"
    IntersectStat(SPORTELLO, SOSPESO).Value = "TEST_FOUND"
    End Sub
    Public Function IntersectStat(ByVal SPORTELLO As String, ByVal SOSPESO As String) As Range

    Dim Row As Variant
    Dim Column As Variant

    With Sheets("STAT_NEW")
    Row = Application.Match(SOSPESO, .[A:A], 0)
    If IsError(Row) Then Row = Application.Match(CDbl(SOSPESO), .[A:A], 0)
    Column = Application.Match(SPORTELLO, .[2:2], 0)
    If IsError(Column) Then Column = Application.Match(CDbl(SPORTELLO), .[2:2], 0)
    If Not IsError(Row) And Not IsError(Column) Then
    Set IntersectStat = .Cells(Row, Column)
    End If
    End With

    End Function

    cut and past from my actully project, wath you think?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •