CODEWITHSUNDEEP
excelmatchrowvba
A.Doe
A.Doe

Reputation: 25

find row number for matching value and store in variable

Based on an input value I want to find the cell in a table, and store the row number in a variable. This variable will be used to multiply the corresponding values in the next columns with the input number. My code so far:

Option Explicit

Sub Calculate()

Dim var As Variant
Dim rownumber As Integer
Dim Mx As Variant
Dim Nx As Variant
Dim Ox As Variant
Dim Px As Variant
Dim Qx As Variant
Dim Rx As Variant
Dim low As Variant
Dim cat As Variant

rownumber = WorksheetFunction.Match(Sheets("User Interface").Range("K27").Value, Sheets("C-type").Range("L5:L345"), 0)


Mx = Sheets("User Interface").Range("K27").Value * Sheets("C-type").Range("M" & rownumber).Value
Nx = Sheets("User Interface").Range("K27").Value * Sheets("C-type").Range("N" & rownumber).Value
Ox = Sheets("User Interface").Range("K27").Value * Sheets("C-type").Range("O" & rownumber).Value
Px = Sheets("User Interface").Range("K27").Value * Sheets("C-type").Range("P" & rownumber).Value
Qx = Sheets("User Interface").Range("K27").Value * Sheets("C-type").Range("Q" & rownumber).Value
Rx = Sheets("User Interface").Range("K27").Value * Sheets("C-type").Range("R" & rownumber).Value

cat = Array(Mx, Nx, Ox, Px, Qx, Rx)

low = Application.WorksheetFunction.Min(cat)

Sheets("User Interface").Range("C45").Value = low

Sheets("User Interface").Range("c45").Activate
Application.Goto ActiveCell.EntireRow, True

End Sub

The problem is that I get subscript out of range-error on my match function, thus I'm starting to doubt that this is the correct way to go about it. I need to somehow be able to reference the row number of the matching number in the table, any suggestions would be highly appreciated.

Upvotes: 0

Views: 4093

Answers (1)

Maddy
Maddy

Reputation: 781

Try This

    strToFind = ThisWorkbook.Sheets("User Interface").Range("K27").Value
    Set Rng = ThisWorkbook.Sheets("C-type").Range("L5:L345")
    If Application.WorksheetFunction.CountIf(Rng, strToFind) > 0 Then
        Set Rng1 = Rng.Find(strToFind, LookIn:=xlValues, LookAt:=xlWhole)
                rownumber = Rng1.Row
        Else
            MsgBox strToFind & " does not exist in range " & Rng.Address
    End If

Upvotes: 2

Related Questions