Using Index Match in VBA Project

[PipeHack]

Hello,

I'm not a newbie when it comes to VBA but I am running into and issue that needs solving. I am using index match worksheet formulas in a macro to automate the completion of inventory sold my wife's business will have a list of cost of good sold. The problem I am running into is there is a slight variance in the Style names that come out of the point of sale systems she uses and I got a type mismatch error because the new Style name doesn't match the Style from my pricing table.

For example I have a pricing table like the one below that I am using as the reference point to the data I am pulling down from the eCommerce site:



Style
Wholesale Price
Retail Price


Irma
15.10
35


Randy
17.75
40








The Styles name from eCommerce site:



Order #
Style Name
Total


123456
Irma - OTH
35


234567
Randy - OTH
40


345678
Randy
40


456789
Randy Shirt
40


567891
Irma
35


678912
Irma Shirt
35



Here is my code that works today as long as I update my pricing table with the new Style name.


Sub OrderForumlas(control As IRibbonControl)

Dim rOut As Range
Dim rValue As Range
Dim rInput As Range


Dim Pricing As Range
Dim Style As Range
Dim rEvents As Range
Dim rDate As Range
Dim Var1 As Variant
Dim Var2 As Variant
Dim Order As Worksheet
Dim sDate As String
Dim i As Long


Set Order = Sheets("OrderID")


Set Pricing = Range("p")
Set Style = Range("m")
Set rEvents = Range("Events")
Set rDate = Range("Event_Date")
i = 2


Do
Set rOut = Order.Cells(i, 19)
Set rValue = Order.Cells(i, 16)
Set rInput = Order.Cells(i, 2)

If rInput.Offset(0, 2).Value <> "Error: Receipt was not found" Then
If rValue.Value <> "First Class Shipping - OTH" Then
If rValue.Value <> "" Then
With Application
' WholeSales Price
Var1 = .Match(rValue.Value, Style, False)
Var2 = .Index(Pricing, Var1, 2)
rOut.Value = Var2
' Total WholeSale Cost
rOut.Offset(0, 1).Value = Var2 * rValue.Offset(0, -1).Value
' Discount
Var1 = .Match(rValue.Value, Style, False)
Var2 = .Index(Pricing, Var1, 3)
rOut.Offset(0, 2).Value = rValue.Offset(0, 2).Value - Var2 * rValue.Offset(0, -1).Value
' Profit Margin
rOut.Offset(0, 3).Value = rValue.Offset(0, 2).Value - rOut.Offset(0, 2).Value - rOut.Offset(0, 1).Value
' Hostess Name
If Not IsError(Evaluate("Index(Events, Match(" & rInput.Address & ", Event_Date, 0), 3)")) Then
rOut.Offset(0, 4).Value = Evaluate("Index(Events, Match(" & rInput.Address & ", Event_Date, 0), 3)")
'Pop-up
rOut.Offset(0, 5).Value = Evaluate("Index(Events, Match(" & rInput.Address & ", Event_Date, 0), 2)")
End If
End With
End If
Else
rOut.Value = 0
rOut.Offset(0, 1).Value = 0
rOut.Offset(0, 2).Value = 0
rOut.Offset(0, 3).Value = rValue.Offset(0, 2).Value
If Not IsError(Evaluate("Index(Events, Match(" & rInput.Address & ", Event_Date, 0), 3)")) Then
rOut.Offset(0, 4).Value = Evaluate("Index(Events, Match(" & rInput.Address & ", Event_Date, 0), 3)")
rOut.Offset(0, 5).Value = Evaluate("Index(Events, Match(" & rInput.Address & ", Event_Date, 0), 2)")
End If
End If
End If
i = i + 1
Loop While i < Order.UsedRange.Rows.Count + 1


End Sub




Because the Word Irma or Randy is always in the Style Name text I was thinking I could add some programming above the index match functions to identify what the one word style is. For example if the Style name "Randy - OTH" was going to be the lookup_value of the match formula we would have the program search that text "Randy - OTH" for the word "Irma" and then "Randy" and so on through the list until it found the correct style. This way I woudn't have to keep added all of the different way the eCommerce sight seems to be wanting to change the naming conventions.

Any help you can provide would be greatly appreciated!

Thank You,

Paul

[Leith Ross]

Hello PipeHack,

It would be a great help if you could post a copy of the workbook. Be sure to change any sensitive information like names, addresses, phone numbers, etc. before you post.

The workbook will ensure a you receive correct working solution to your problem.