locked
VBA Function to find row with specific value in another sheet's column (Primary Key) and returning row values. RRS feed

  • Question

  • Hi Guys I am stuck on one small thing plz help

    Here's the problem

    Sheet1:

    Creditors      SaleNo
    a 1
    b 2
    c 3

    Sheet2:

    SaleNo Creditors
    3             ?
    2             ?

    Function on Sheet2 finds the creditor from sheet1 by matching SaleNo.

    Here is my solution that works (Subroutine)

    Sub MatchingValues()

    Dim MatchingSaleNo As String
    Dim SaleNo As Long

    SaleNo = InputBox("Please enter your value")
     
     Worksheets("Sheet1").Activate
     Columns("L:L").Select
         Selection.Find(What:=SaleNo, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
             :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
              False, SearchFormat:=False).Activate
             
              ActiveCell.Offset(0, -1).Range("A1").Select
             
             
        MatchingSaleNo = ActiveCell.Value

    Worksheets("Sheet2").Activate
    Range("e6").Value = MatchingSaleNo

    End Sub

    ------------------------------------------------------------------------------------------------------------------------------------------------------------

    Problem: Similar Function that is showing error :( Help

    Function MatchingSaleNo(SaleNo As Long) As String

     Worksheets("Sheet1").Activate
     Columns("L:L").Select
        
         Selection.Find(What:=SaleNo, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
             :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
              False, SearchFormat:=False).Activate
             
              ActiveCell.Offset(0, -1).Range("A1").Select
                       
        MatchingSaleNo = ActiveCell.Value

    End Function

    ----------------------------------------------------------------------------------------------------------------------------------------------------

    I will really appreciate any help cuz i am stuck in the middle of something due to this.

    Thanks in advance


    • Edited by Nijatam Monday, November 5, 2012 11:12 AM Formatting
    Monday, November 5, 2012 11:10 AM

All replies

  • Function MatchingSaleNo(SaleNo As Long) As String
          
       Dim Rng As Range
            
       Set Rng = Worksheets("Sheet1").Range("L:L").Find(What:=SaleNo, LookIn:=xlFormulas, LookAt _
             :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
              False, SearchFormat:=False)
        If Not (Rng Is Nothing) Then
             MatchingSaleNo = Rng.Offset(0, -1).Value
        Else
            MatchingSaleNo = "Not found"
        End If
            
    End Function

    See if it helps.

    Actually the method of object which changes user intaerface is not permissible in Function.Look at the code.Mainly I removed the activate statements which changes the activecell,activesheet etc.

    All the best.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Monday, November 5, 2012 12:28 PM
    Answerer