How to select cells based on specific string in a range?

Question

I want to look for a string "GAS CYLINDERS" in range "B17:B29" and if a cell contains the given string, go to column "H"(from "B") of the same row of the cell which contains the string then, select it.I want it to loop till row 29 and keep on selecting the eligible cells and take the sum of the values of all selected cells and finally , paste the total in another cell ("M22")

Code I tried:

ROW_NUMBER = 16
ROW_NUMBER = ROW_NUMBER + 1
PARTICULARS = Sheets("Invoice").Range("B" & ROW_NUMBER)
XXX = InStr(PARTICULARS, "GAS CYLINDER")

For Each XXX In Range("B17: B29")
    If MYRNG = Empty And InStr(PARTICULARS, "GAS CYLINDER") > 1 Then
        MYRNG = XXX.Address
    ElseIf InStr(PARTICULARS, "GAS CYLINDER") > 1 Then
        MYRNG = MYRNG & "," & XXX.Address
    End If
Next XXX

Range(MYRNG).Select
Selection.Offset(0, 6).Select

Image for reference

PS: I'm new to VBA. The code that i tried i found on You tube and Google and I modified it a little because the code i initially found didn't get me the expected result.

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-find-method-excel 20 seconds search on google with your question title. Please put some effort into it. — Alejandro Nagy, Dec 20 '17 at 20:16
ROW_NUMBER = 16 ROW_NUMBER = ROW_NUMBER + 1 PARTICULARS = Sheets("Invoice").Range("B" & ROW_NUMBER) 'XXX = InStr(PARTICULARS, "GAS CYLINDER" For Each XXX In Range("B17: B29") If MYRNG = Empty And InStr(PARTICULARS, "GAS CYLINDER") > 1 Then MYRNG = XXX.Address ElseIf InStr(PARTICULARS, "GAS CYLINDER") > 1 Then MYRNG = MYRNG & "," & XXX.Address End If Next XXX Range(MYRNG).Select Selection.Offset(0, 6).Select — Sw1tch, Dec 20 '17 at 20:17
Can you add the code into the original post, and tag with the code tags (`{}`)? It's hard to read in the comments. — BruceWayne, Dec 20 '17 at 20:18
I hate to be pedantic (but computer programs **are**, so you need to get used to it) but you say "I want to look for a string "GAS CYLINDERS" " and then you show sample data that does not contain that string anywhere. — YowE3K, Dec 20 '17 at 20:28
@BruceWayne i hope the code i tried initially can be clearly seen now. — Sw1tch, Dec 20 '17 at 20:56
@YowE3K text in cells "b17","b19",b21"(in the image) contain "GAS CYLINDERS" in them? — Sw1tch, Dec 20 '17 at 21:00
Programs won't match "GAS CYLINDERS" with "GAS CYLINDER" - there is no "S" on the end of the string in your sample data - that's why I say you need to get used to them being pedantic. (It looks like your code is searching for the correct thing, so it is only the question that isn't pedantic enough.) — YowE3K, Dec 20 '17 at 21:03

NetMage · Answer 1 · 2017-12-20T20:54:11.163

2

Why are you using code instead of a formula?

In cell M22:

=SUMIF(B17:B21, "*Gas Cylinder*", H17:H21)

To compare and display an error message,

=IF(SUMIF(B17:B21, "*Gas Cylinder*", H17:H21)<>N22, "ERROR: ...", "")

Or test for true in M22 in your VBA save code:

=SUMIF(B17:B21, "*Gas Cylinder*", H17:H21)<>N22

edited Dec 20 '17 at 20:54

answered Dec 20 '17 at 20:28

NetMage

26,163
3
34
55

i actually want excel to pop an error message if the total of the eligible cells doesnt match the value of cell "n22" when i click "save" command button on my userform.That is why i need the vba code. – Sw1tch Dec 20 '17 at 20:50
Updated answer - just use the formula and test the value of M22. – NetMage Dec 20 '17 at 20:54
@Sw1tch Or just use an `If Range("N22").Value <> Application.SumIf(Range("B17:B21", "*Gas Cylinder*", Range("H17:H21")) Then` in VBA. – YowE3K Dec 20 '17 at 21:07
testing the value of m22 in vba code worked.Thank You so much for your help and time! I really appreciate it! Also sorry for the trouble. Cheers!(to everyone) – Sw1tch Dec 20 '17 at 21:18

score 1 · Accepted Answer · answered Dec 20 '17 at 21:13

1

Firstly, there is very rarely a need to Select things in VBA.

You want to calculate a number and place that into a cell, so simplify your code to just do that:

Dim XXX As Range
Dim myTotal As Long

For Each XXX In Range("B17: B29")
    If InStr(XXX.Value, "GAS CYLINDER") > 0 Then
        myTotal = myTotal + XXX.Offset(0, 6).Value
    End If
Next XXX

Range("M22").Value = myTotal

Or you could just use Excel's SumIf function:

Range("M22").Value = Application.SumIf(Range("B17:B29", "*GAS CYLINDER*", Range("H17:H29"))

answered Dec 20 '17 at 21:13

YowE3K

23,852
7
26
40

works like a charm. Also, i'll keep the "select" suggestion in mind. Thanks a Ton for your time! Cheers! – Sw1tch Dec 20 '17 at 21:32
1

@Sw1tch - When you have a moment, you should check out [this SO question/answer](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) on how to avoid using `.Select`/`.Activate`. – BruceWayne Dec 20 '17 at 22:59
@BruceWayne i sure will. Thank you! – Sw1tch Dec 21 '17 at 04:13

How to select cells based on specific string in a range?

2 Answers2