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

NRGZ

New Member
Joined
Jul 23, 2021
Messages
47
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
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

New Member
Joined
Jul 23, 2021
Messages
47
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
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:

Code:
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.
VBA Code:
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
 
Last edited by a moderator:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,334
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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:

Code:
    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:

Code:
    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

New Member
Joined
Jul 23, 2021
Messages
47
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
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:

Code:
    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:

Code:
    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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,334
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Yes - I'd change the .Cells(z, 1) to .Cells (2, 1) so that you always look at the full column.
 

NRGZ

New Member
Joined
Jul 23, 2021
Messages
47
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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...... :unsure:
 

NRGZ

New Member
Joined
Jul 23, 2021
Messages
47
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
VBA Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,334
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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?
 
Solution

NRGZ

New Member
Joined
Jul 23, 2021
Messages
47
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
YES!!! it was indeed how we 'stored' the values. Final working code....for now!
VBA Code:
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/
 

Forum statistics

Threads
1,141,122
Messages
5,704,431
Members
421,349
Latest member
Santhosh3188

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top