CODEWITHSUNDEEP
vbaexcel
William Cheung
William Cheung

Reputation: 11

VBA Row Location

I am currently working with an Excel sheet that was compiled by someone else (So I can’t delete or add columns). I am creating an interface with VBA for users of the worksheet. In one of the tabs there are three columns I wish to be able to find the row given two parameters which will be found in the two previous columns so I can enter a number. For reference I am using dropdown options for the user to select the first two parameters and enter in an input box for the third. Need to find the location where the two inputs match and be able to assign something in a different column but same row.

For example (find row of pear green) [answer is row 9, and with this enter the input in row 9]

Fruit     Color    Amount
Apple     Red
Apple     Green
Apple     Blue
Orange    Red
Orange    Green
Orange    Blue
Pear      Red
Pear      Green
Pear      Blue

Upvotes: 1

Views: 258

Answers (2)

Tom
Tom

Reputation: 9898

Here's another way to do it as well. This is a starting point and could be adapted to find all the occurrences in your sheet. This uses the .findnext property

Sub FindValue()
    Dim ws As Worksheet
    Dim NoRow As Integer
    Dim rng As Range
    Dim firstaddress As String
    Dim c

    Set ws = ThisWorkbook.Sheets("Fruit") ' Change to your sheet name

    With ws
        NoRow = .Range("A" & .Rows.Count).End(xlUp).Row
        Set rng = .Range("A2:A" & NoRow)
        With rng
            Set c = .Find("Pear", LookIn:=xlValues) ' Change Pear to your test value
            If Not c Is Nothing Then
                firstAddress = c.Address
                Do
                    If c.Offset(0, 1) = "Green" Then ' Change this to the second cell value
                        MsgBox c.Address
                    End If                        
                    Set c = .FindNext(c)
                Loop While Not c Is Nothing And c.Address <> firstAddress
            End If
        End With
    End With
End Sub

This should be faster then using a for loop as well

Upvotes: 0

Amen Jlili
Amen Jlili

Reputation: 1944

Here's a starting point:

Function FindOccurence(Fruit As String, Color As String) As Long
Dim WS As Worksheet
Set WS = ActiveSheet
Dim L As Long
L = WS.Cells(WS.Rows.Count, 1).End(xlUp).Row
For Each Cell In WS.Range("A2:A" & L)
If Cell.Value = Fruit And Cell.Offset(0, 1).Value = Color Then
FindOccurence = Replace(Cell.Address(0, 0), "A", "")
Exit Function
End If
FindOccurence = 0
Next Cell
End Function

Sub Main()
Debug.Print (FindOccurence("Apple", "Green"))
End Sub

enter image description here

Beware that this finds you the first occurrence.

Upvotes: 1

Related Questions