How to use a 'variable/string' within a MATCH Statement?

[NRGZ]

I.... Think i'm understanding the MATCH statement correctly but how do i use a variable within a MATCH statement or am i trying to get the MATCH statement to do something it cant?
Initial Code:

Private Sub cntrct1()

Sheets("totals").Activate

Dim y As Integer
Dim z As Integer
Dim prod As String
Dim prod0 As String

y = WorksheetFunction.CountA(Worksheets("contracts").Range(Worksheets("contracts").Cells(2, 1), Worksheets("contracts").Cells(999, 1)))

prod = Sheets("contracts").Cells(2, 1)

Cells(2, 6) = WorksheetFunction.Match(prod.Value, Range(Worksheets("contracts").Cells(2, 1), Worksheets("contracts").Cells(y, 1), 0))



' check status only for testing
Cells(4, 10) = prod
Cells(4, 11) = y


End Sub

What i'm trying to do is retrieve the value of a cell where the the variable from 'totals' matches the variable in 'contracts', this would loop so until all variables have been matched from the 'totals' worksheet. (at the moment i'm trying to get it to match one and then i'll get to the rest)

Thanks In Advance,
o/

 

[NRGZ]

What you are saying doesn't appear to correlate to what you actually want. You said you wanted the value in C2. Clearly that is not correct. The value you want is dependent on the row where the product is found, which is what I asked originally. Assuming that is the case, you do want Match since that will find the row position. My best guess currently would be something like:

Dim matchPos
with Worksheets("contracts")
 matchpos = Application.Match(prod, .Range(.Cells(2, 1), .Cells(y, 1)), 0) + 1
if not iserror(matchpos) then
Sheets("Totals").Cells(2, 6).value = .cells(matchpos, "C").value
end if
end with

Sorry for the confusion.
I'm getting a 'Run-time error 13 : type mismatch' error, I am trying to debug it myself by running it 'step through' but i'm not sure what the issue is.

Private Sub cntrct1()

Sheets("totals").Activate

Dim y As Integer
Dim z As Integer
Dim prod As String
Dim prod0 As String
Dim matchPos

y = WorksheetFunction.CountA(Worksheets("contracts").Range(Worksheets("contracts").Cells(2, 1), Worksheets("contracts").Cells(999, 1)))
   
    With Worksheets("contracts")
    
    For z = 2 To y + 1

    prod = Sheets("contracts").Cells(z, 1)

    matchPos = Application.Match(prod, .Range(.Cells(z, 1), .Cells(y, 1)), 0) + 1
    If Not IsError(matchPos) Then
    Sheets("Totals").Cells(z, 6).Value = .Cells(matchPos, "C").Value
    End If
   
    Next z
   
End With

' check status only for testing
Cells(4, 10) = prod
Cells(4, 11) = y



End Sub

 

[RoryA]

My fault - I added in the +1 to get the actual row number without thinking that the match might have failed. Change the block from this:

    matchPos = Application.Match(prod, .Range(.Cells(z, 1), .Cells(y, 1)), 0) + 1
    If Not IsError(matchPos) Then
    Sheets("Totals").Cells(z, 6).Value = .Cells(matchPos, "C").Value
    End If

to this:

    matchPos = Application.Match(prod, .Range(.Cells(z, 1), .Cells(y, 1)), 0)
    If Not IsError(matchPos) Then
    Sheets("Totals").Cells(z, 6).Value = .Cells(matchPos  + 1, "C").Value
    End If

 

[NRGZ]

My fault - I added in the +1 to get the actual row number without thinking that the match might have failed. Change the block from this:

    matchPos = Application.Match(prod, .Range(.Cells(z, 1), .Cells(y, 1)), 0) + 1
    If Not IsError(matchPos) Then
    Sheets("Totals").Cells(z, 6).Value = .Cells(matchPos, "C").Value
    End If

to this:

    matchPos = Application.Match(prod, .Range(.Cells(z, 1), .Cells(y, 1)), 0)
    If Not IsError(matchPos) Then
    Sheets("Totals").Cells(z, 6).Value = .Cells(matchPos  + 1, "C").Value
    End If

(runnig it via 'step through' so that i can understand its workings better)
I'm getting a Error 2042 from the matchpos variable, i understand now that this is because it hasn't found a match. When watching the the variable values change i think its because either its not looking at the correct 'sheet' and/or isnt checking the entire range in the contacts sheet, am i looking in the right direction?

 

[RoryA]

Yes - I'd change the .Cells(z, 1) to .Cells (2, 1) so that you always look at the full column.

 

[NRGZ]

Yes i thought that could have been an issue as well but i think that perhaps its the prod variable that i'm picking up. If I pick up the variable from Totals, match it with the equivalent variable in Contracts, it returns the value of the ROW in contracts and that grabs the value from the row(matchpos),column that i want to 'copy' over to the Totals sheet.
Is that right......

 

[NRGZ]

Private Sub cntrct1()

Sheets("totals").Activate

Dim y As Integer
Dim z As Integer
Dim prod As String
Dim prod0 As String
Dim matchPos


y = WorksheetFunction.CountA(Worksheets("contracts").Range(Worksheets("contracts").Cells(2, 1), Worksheets("contracts").Cells(999, 1)))
    
    With Worksheets("contracts")
     
    For z = 2 To y + 1

    prod = Sheets("Totals").Cells(z, 1)

    matchPos = Application.Match(prod, .Range(Worksheets("contracts").Cells(2, 1), Worksheets("contracts").Cells(999, 1)), 0)
    If Not IsError(matchPos) Then
    Sheets("Totals").Cells(z, 6).Value = .Cells(matchPos + 1, "C").Value
    End If
    
    Next z
    
End With

' check status only for testing
Cells(4, 10) = prod
Cells(4, 11) = y



End Sub

I'm stumped for as to why it isnt picking up a value from CONTRACTS as I think its looking in the right places...

 

[RoryA]

If you are not getting a value, then the Match must be failing. Perhaps you have numbers stored as text in one location and stored as numbers in the other, in which case they won't match. Is the prod data numeric?

 

[NRGZ]

YES!!! it was indeed how we 'stored' the values. Final working code....for now!

Option Explicit
Private Sub cntrct1()

Sheets("totals").Activate

Dim y As Integer
Dim z As Integer
Dim prod As Integer
Dim prod0 As String
Dim matchPos As Integer



y = WorksheetFunction.CountA(Worksheets("contracts").Range(Worksheets("contracts").Cells(2, 1), Worksheets("contracts").Cells(999, 1)))
  
    With Worksheets("contracts")
   
    For z = 2 To y + 1

    prod = Sheets("Totals").Cells(z, 1)

    matchPos = Application.Match(prod, .Range(Worksheets("contracts").Cells(2, 1), Worksheets("contracts").Cells(999, 1)), 0)
    If Not IsError(matchPos) Then
    Sheets("Totals").Cells(z, 6).Value = .Cells(matchPos + 1, "C").Value
    End If
  
    Next z
  
End With

' check status only for testing
Cells(4, 10) = prod
Cells(4, 11) = y



End Sub

Now to recalculate a value taking into consideration duplicates...

I want to say thanks to RoryA for all his help in this.

Stay Tuned for more cries for HELP!!!

o/