Consulting

Results 1 to 2 of 2

Thread: Index Match to Determine Paste Location

  1. #1

    Index Match to Determine Paste Location

    Hi all,

    I am doing a quote register for work and I'm a little stuck on some VBA coding.

    I have in this example 2 sheets (actual quote database has many more but for simplicity I've omitted most), an edit quote sheet (Sheet1) which calls up the quote details to allow for editing and then saving back to the database and a quote status sheet (Sheet2), which has a list of all the quotes and their revisions.

    What I'm wanting to do is look up the quote and revision # on sheet1 cells L1 & H1, find the match in the list on sheet2 and change the status to "Rev'd"

    I've had a looked around for similar code to play around with but all I can find is code that uses Index Match for the copy criteria not the paste criteria.

    I have attached a sample of the data base for hopefully more clarification of what I'm trying to achieve.

    Thanks for an help that can be provided.
    Attached Files Attached Files

  2. #2
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    The procedure must be placed in the "Quote" sheet module
    Sub AAA()
        Dim wksQs       As Worksheet
        Dim rngData     As Range
        Dim vData       As Variant
        Dim i           As Long
        Dim Qte         As String
        Dim Rev         As Long
        Dim blnFound    As Boolean
    
    
        Qte = Me.Range("L1").Value
        Rev = Me.Range("H1").Value
    
    
        Set wksQs = Sheet9
    
        Set rngData = wksQs.Range("C5").CurrentRegion.Columns("B:C")
    
        With rngData
            Set rngData = .Offset(1).Resize(.Rows.Count - 1)
        End With
    
        vData = rngData.Value
    
        For i = 1 To UBound(vData)
            If vData(i, 1) = Qte Then
                If vData(i, 2) = Rev Then
                    blnFound = True
                    rngData.Cells(i, 1).Offset(, 2).Value = "Rev'd"
                    Exit For
                End If
            End If
        Next i
    
        If Not blnFound Then
            MsgBox "Not found Quote #"
        End If
    End Sub
    This is one of the simpler solutions to the problem.

    Artik

Tags for this Thread

Posting Permissions

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