CODEWITHSUNDEEP
listvba
NewSpeaker
NewSpeaker

Reputation: 67

VBA - Check a range in sheet1 for a variable and create a list on sheet2 of each occurence of the variable

What I want to do is check sheet1, column3 for a variable that I have defined as combobox1.value on sheet2.

I want the macro to list each time it finds the value of combobox1 in sheet1 on sheet2 along with 2 offset values.

I am completely lost and cannot find anything similar to this on Stack overflow. 

      Sub make_list()
Dim grower As String
Dim r As Range
grower = Sheets("Grower Reporting").ComboBox1.Value

Set r = Sheets("Grower Rejection Data").Range("E:E").Find(grower, Range("e1"), _
LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlNext)
i = 1
If Not r Is Nothing Then
    firstAddress = r.Address
    Do
        'Add cell references to a list on sheet1 column E.
        Sheets("Sheet1").Cells(i, 5).Value = Sheets("Grower Rejection Data").Cells(r.Row, 3).Address
        'Add any additional offset values here
        i = i + 1
        Set r = Sheets("Grower Rejection Data").Range("e:e").FindNext(r)
    Loop While Not r Is Nothing And r.Address <> firstAddress
End If
End Sub

What I am trying to do is check in column e on sheet "Grower Rejection Data" how many times "grower" variable appears and list each occurrence on sheet "Grower Reporting" starting in cell h31. I hope that helps make a bit clearer what I am attempting.

Upvotes: 0

Views: 161

Answers (1)

ARich
ARich

Reputation: 3279

It sounds like this is what you're looking for:

Set R = Sheets("Grower Rejection Data").Range("E:E").Find(grower, Range("E1"), _      LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlNext)
i = 31
If Not R Is Nothing Then
    firstAddress = R.Address
    Do
        Sheets("Grower Reporting").Cells(i, 38).Value = R.Value
        Sheets("Grower Reporting").Cells(i, 39).Value = R.Offset(0,1).Value
        Sheets("Grower Reporting").Cells(i, 40).Value = R.Offset(0,2).Value           
        i = i + 1
        Set R = Sheets("Grower Rejection Data").Range("E:E").FindNext(R)
    Loop While Not R Is Nothing And R.Address <> firstAddress
End If

Upvotes: 1

Related Questions