Finding cells matching criteria in column range with multiple occurrences

Jeddo · Jan 26, 2019

I’m new to VBA and I’ve run across a problem and I haven’t been able to solve searching the internet.
I have a range of data (“B5:B120”) that contains numeric value and has filled and blank cells. Some of the cells have the same value. I am trying to find all the cells in that range that match the value of Me.txtBuyer and then copy data from other textboxes to cells offset from the found cells. The attached formula works for me as long as there is only one cell in the range that has the value I seek, but if there are multiple cells with that value, it matches to the number of the row instead. Any help would be appreciated and please keep in mind I’m a newbie and don’t understand all the lingo quite yet. I am using Excel 2010. Thanks for the help

 Set findvalue = Sheet4.Range("B5:B120").Find(what:=Me.txtBuyer)

findvalue.Offset(0, 11).Value = Me.cboPayment.Value
findvalue.Offset(0, 12).Value = Me.txtCheck.Value
findvalue.Offset(0, 13).Value = Me.txtAmountPaid.Value

Akuini · Jan 26, 2019

This is untested:

Code:

[FONT=lucida console][COLOR=Royalblue]Set[/COLOR] findvalue = Sheet4.Range([COLOR=brown]"B5:B120"[/COLOR]).Find(what:=Me.txtBuyer)
[COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] findvalue [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
    sAddress = findvalue.Address
    [COLOR=Royalblue]Do[/COLOR]
        findvalue.Offset([COLOR=crimson]0[/COLOR], [COLOR=crimson]11[/COLOR]).Value = Me.cboPayment.Value
        findvalue.Offset([COLOR=crimson]0[/COLOR], [COLOR=crimson]12[/COLOR]).Value = Me.txtCheck.Value
        findvalue.Offset([COLOR=crimson]0[/COLOR], [COLOR=crimson]13[/COLOR]).Value = Me.txtAmountPaid.Value

       [COLOR=Royalblue]Set[/COLOR] findvalue = Cells.FindNext(findvalue)

    [COLOR=Royalblue]Loop[/COLOR] [COLOR=Royalblue]While[/COLOR] findvalue.Address <> sAddress
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR][/FONT]

Jeddo · Jan 26, 2019

Akuini said:

This is untested:

Code:

[FONT=lucida console][COLOR=Royalblue]Set[/COLOR] findvalue = Sheet4.Range([COLOR=brown]"B5:B120"[/COLOR]).Find(what:=Me.txtBuyer)
[COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] findvalue [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
    sAddress = findvalue.Address
    [COLOR=Royalblue]Do[/COLOR]
        findvalue.Offset([COLOR=crimson]0[/COLOR], [COLOR=crimson]11[/COLOR]).Value = Me.cboPayment.Value
        findvalue.Offset([COLOR=crimson]0[/COLOR], [COLOR=crimson]12[/COLOR]).Value = Me.txtCheck.Value
        findvalue.Offset([COLOR=crimson]0[/COLOR], [COLOR=crimson]13[/COLOR]).Value = Me.txtAmountPaid.Value

       [COLOR=Royalblue]Set[/COLOR] findvalue = Cells.FindNext(findvalue)

    [COLOR=Royalblue]Loop[/COLOR] [COLOR=Royalblue]While[/COLOR] findvalue.Address <> sAddress
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR][/FONT]

Tried it, but problems. It found a match to txtBuyer (value was 13) in the column with row numbers (Row 13) instead of the value in column 2, which was on row 19. It copied the value of cbopayment to the offset column from the column 1, but it also copied that same value to the cells in 9 adjoining columns and then copied the next two statements in only one column each. There were three matches to txtBuyer, but it only copied to the row that matched the criteria of 13 and also to row 113, but no other rows. Hope this makes since. Thanks for your help

Akuini · Jan 26, 2019

It's hard to understand the problem without seeing the data. Can you post some data in a table? Mock the data so it easier to describe the problem.

Jeddo · Jan 27, 2019

Akuini said:
It's hard to understand the problem without seeing the data. Can you post some data in a table? Mock the data so it easier to describe the problem.

Hope I'm understanding you right. Data would look like this. I want to find all cells in column B that = 3, and copy data from text boxes to corresponding columns F, G & H. What my code has been doing is if there are multiple occurrences of the value I am looking for, it defaults to the row, ie; row 3 instead of B5 and B7


Buyer No.	Item no.	Description	Pymt. Type	Check No.	Amount Pd
Clock
Sign
Serving Dish
Floor Plant - Palm
BBQ Grill
Wheelbarrow
Quilt

<colgroup><col style="width:48pt" width="64" span="3"> <col style="mso-width-source:userset;mso-width-alt:2267;width:47pt" width="62"> <col style="mso-width-source:userset;mso-width-alt:4937;width:101pt" width="135"> <col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> <col style="mso-width-source:userset;mso-width-alt:3035;width:62pt" width="83"> <col style="mso-width-source:userset;mso-width-alt:2560;width:53pt" width="70"> </colgroup><tbody>
[TD="width: 64"][/TD]
[TD="class: xl65, width: 64"]A[/TD]
[TD="class: xl65, width: 64"]B[/TD]
[TD="class: xl65, width: 62"]C[/TD]
[TD="class: xl65, width: 135"]E[/TD]
[TD="class: xl65, width: 77"]F[/TD]
[TD="class: xl65, width: 83"]G[/TD]
[TD="class: xl65, width: 70"]H[/TD]

[TD="class: xl65"]1[/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"]2[/TD]
[TD="class: xl65"][/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]1[/TD]

[TD="class: xl65"]3[/TD]
[TD="class: xl65"][/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]2[/TD]

[TD="class: xl65"]4[/TD]
[TD="class: xl65"][/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]3[/TD]

[TD="class: xl65"]5[/TD]
[TD="class: xl65"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]

[TD="class: xl65"]6[/TD]
[TD="class: xl65"][/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]5[/TD]

[TD="class: xl65"]7[/TD]
[TD="class: xl65"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]

[TD="class: xl65"]8[/TD]
[TD="class: xl65"][/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]7[/TD]

</tbody>

Akuini · Jan 27, 2019

Ah, sorry I missed something earlier.
Try this one instead:

Code:

[FONT=lucida console][COLOR=Royalblue]Dim[/COLOR] sAddress [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR], findvalue [COLOR=Royalblue]As[/COLOR] Range, r [COLOR=Royalblue]As[/COLOR] Range

[COLOR=Royalblue]Set[/COLOR] r = Sheet4.Range([COLOR=brown]"B5:B120"[/COLOR])
[COLOR=Royalblue]Set[/COLOR] findvalue = .Find(what:=Me.txtBuyer, lookAt:=xlWhole)
[COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] findvalue [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
    sAddress = r.findvalue.Address
    [COLOR=Royalblue]Do[/COLOR]
        findvalue.Offset([COLOR=crimson]0[/COLOR], [COLOR=crimson]11[/COLOR]).Value = Me.cboPayment.Value
        findvalue.Offset([COLOR=crimson]0[/COLOR], [COLOR=crimson]12[/COLOR]).Value = Me.txtCheck.Value
        findvalue.Offset([COLOR=crimson]0[/COLOR], [COLOR=crimson]13[/COLOR]).Value = Me.txtAmountPaid.Value

       [COLOR=Royalblue]Set[/COLOR] findvalue = r.FindNext(findvalue)

    [COLOR=Royalblue]Loop[/COLOR] [COLOR=Royalblue]While[/COLOR] findvalue.Address <> sAddress
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR][/FONT]

Jeddo · Jan 27, 2019

Tried it but nothing happens. Text Box values aren't copied to anywhere on the sheet. Tried making some edits, but my knowledge is so limited, I didn't get anywhere. thanks for your help.

Akuini · Jan 27, 2019

What is the sheet name in question?
In your code you use sheet4, it means sheet with index number 4, the name could be anything.
Say your sheet name is "data" then change this:

Set r = Sheet4.Range("B5:B120")

to

Set r = Sheets("data").Range("B5:B120")

Akuini · Jan 27, 2019

Using your example in post #5 , try this:
Your data must be in the active sheet.

Code:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] test111()

[COLOR=Royalblue]Dim[/COLOR] sAddress [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR], findvalue [COLOR=Royalblue]As[/COLOR] Range, r [COLOR=Royalblue]As[/COLOR] Range

[COLOR=Royalblue]Set[/COLOR] r = Range([COLOR=brown]"B3:B120"[/COLOR])
[COLOR=Royalblue]Set[/COLOR] findvalue = r.Find(what:=[COLOR=crimson]3[/COLOR], lookAt:=xlWhole)
[COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] findvalue [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
    sAddress = findvalue.Address
    [COLOR=Royalblue]Do[/COLOR]
        findvalue.Offset([COLOR=crimson]0[/COLOR], [COLOR=crimson]3[/COLOR]).Value = [COLOR=crimson]1[/COLOR]
        findvalue.Offset([COLOR=crimson]0[/COLOR], [COLOR=crimson]4[/COLOR]).Value = [COLOR=crimson]2[/COLOR]
        findvalue.Offset([COLOR=crimson]0[/COLOR], [COLOR=crimson]5[/COLOR]).Value = [COLOR=crimson]3[/COLOR]

       [COLOR=Royalblue]Set[/COLOR] findvalue = r.FindNext(findvalue)

    [COLOR=Royalblue]Loop[/COLOR] [COLOR=Royalblue]While[/COLOR] findvalue.Address <> sAddress
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

Jeddo · Jan 27, 2019

Didn't have any luck. I can get the code you sent to work exactly like I need on the small set of data I made to give you an idea of what I'm trying to do, but when I try and use it on my worksheet, I get nothing. I don't know if my previous post went through or not. I tinkered with the range name, but no luck. The sheet name is Checkout. I changed the range to r = Sheets("Checkout").Range("B5:B120"). I'm not good on syntax.

Finding cells matching criteria in column range with multiple occurrences

Jeddo

Board Regular

Excel Facts

Akuini

Well-known Member

Jeddo

Board Regular

Akuini

Well-known Member

Jeddo

Board Regular

Akuini

Well-known Member

Jeddo

Board Regular

Akuini

Well-known Member

Akuini

Well-known Member

Jeddo

Board Regular

Similar threads

Forum statistics

Share this page

Finding cells matching criteria in column range with multiple occurrences

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock