Index Match to Determine Paste Location

[Lifespeachy]

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.

[Artik]

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 SubThis is one of the simpler solutions to the problem.

Artik