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/

 

[RoryA]

Match returns a position, not a value. What cell do you want the value from?

Also, strings don't have any properties, so it would just be prod not prod.value, and you need to qualify the Range property in your Match line with the worksheet too.

 

[NRGZ]

Match returns a position, not a value. What cell do you want the value from?

Also, strings don't have any properties, so it would just be prod not prod.value, and you need to qualify the Range property in your Match line with the worksheet too.

As i say i'm feeling my way with VB so MATCH is not what i'm wanting to use.

What i'm trying to do is retrieve the value of a cell where the the variable from 'totals' matches the variable in 'contracts'.
Source Value being searched for resides in 'contracts' cells(2, 3) and i want the value to 'show' in Cells(2, 6) in 'Totals'.

Thanks,

 

[RoryA]

That doesn't answer my question I'm afraid. You are searching for the value in column A, but which column do you want the result from?

 

[NRGZ]

That doesn't answer my question I'm afraid. You are searching for the value in column A, but which column do you want the result from?

Sheet 'contracts'
Row 2
Column 3

 

[RoryA]

That really doesn't make sense. If you just want the value of a particular cell (C2), you can refer to it directly without using Match or any other lookup function.

 

[NRGZ]

That really doesn't make sense. If you just want the value of a particular cell (C2), you can refer to it directly without using Match or any other lookup function.

Agreed but I want to retrieve the value of a cell depending upon the matching of 2 values.
Let me explain what i'm trying to do,
I am importing a number of CSVs, to separate sheets, that contain production information with the amount that is being ordered. Each sheet holds order information for a specific individual product.
A procedure grabs the product code, description and totals the quantity being ordered of that product and populates the TOTALS sheet with that information.
The CONTRACTS sheet holds information on individual products that we have 'contractual' quantity commitments for.
What i'm trying to do is take the 'value', product code, of a cell in the TOTALS sheet and IF we have a 'match' in the CONTRACTS sheet return the value of a cell containing what our contractual commitment for that product is. What the 'excel' side of things will do is then calculate what our remaining stock is.

I hope this paints a better picture.
Thanks,
(the attached image shows the layout of the TOTALS sheet)

 

[RoryA]

It sounds like you want something like this then:

With Worksheets("contracts")
   If WorksheetFunction.Countif(.Range(.Cells(2, 1), .Cells(y, 1)), prod) > 0 then Sheets("Totals").Cells(2, 6).Value = .Cells(2, 3).Value
End With

which basically says that if the value in prod appears in column A on Contracts, put the value of C2 on Contracts into F2 on the Totals sheet. Note that if you properly qualify your ranges with the relevant worksheet, you don't have to activate a specific sheet.

 

[NRGZ]

It sounds like you want something like this then:

With Worksheets("contracts")
   If WorksheetFunction.Countif(.Range(.Cells(2, 1), .Cells(y, 1)), prod) > 0 then Sheets("Totals").Cells(2, 6).Value = .Cells(2, 3).Value
End With

which basically says that if the value in prod appears in column A on Contracts, put the value of C2 on Contracts into F2 on the Totals sheet. Note that if you properly qualify your ranges with the relevant worksheet, you don't have to activate a specific sheet.

That does work, sort of.
My code so far,
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)))

With Worksheets("contracts")

For z = 2 To y + 1


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

If WorksheetFunction.CountIf(.Range(.Cells(z, 1), .Cells(y, 1)), prod) > 0 Then Sheets("Totals").Cells(z, 6).Value = .Cells(z, 3).Value

Next z

End With

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



End Sub

It works in a sequential manner but as you can see from the attached images its not actually pulling the correct value to TOTALS because its not identifying were it actually is.
example: Sheets 'TOTALS' Prod - 1491 = 90 were as 'CONTRACTS' prod - 1491 = 217
I feel like i'm getting out of my depth with knowing how to do this but i'm understanding how the coding your writing is working.
Thanks.

 

[RoryA]

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