Excel VBA Set Range/Cells after searching/find a Cell

Question

'I am making a graph. First I would like to search the keyword "A-test" in column "B" and then select 10 rows and 2 columns from where I find the word. I am having difficultly to select the range. Please, help how to use "Set" function in this case. Here is the Macro I am testing

'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Sub Macro3()    
Macro3 Macro    
    Dim Ws As Worksheet    
    Dim i  As Long
    Dim k As Long
    Dim M As Range
    EndRow = ActiveWorkbook.Sheets("Sheet1").Range("B1").Offset(Sheets("Sheet1").Rows.Count - 1, 0).End(xlUp).Row
    i = 1
    For k = 2 To EndRow
        If Cells(k, 2) Like "*A*" Then
            Set M = Range("C1:L1", ActiveCell(i, 2).Offset(2, 10)) 'Difficult
            'Range("B1").Select 'test
            'Range("C1:L1,B4:L5").Select 'test
        End If
        'Cells(k, 2) Like "*A*"
    Next
End Sub
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Here is the data.

Date    choice  Sample#1    Sample#2    Sample#3    Sample#4    Sample#5    Sample#6    Sample#7    Sample#8    Sample#9    Sample#10
2018/07/12 00:06:58 A-test  105259  103495  105225  103923  104536  103196  105020  104316  105317  103021
2018/07/12 00:07:03 B-test  105260  103848  105225  103923  104535  103196  105020  104316  105316  103021
2018/07/12 00:07:47 A-test  103108  101758  102702  101784  102592  101688  103971  103345  104344  101513
2018/07/12 00:07:51 B-test  103109  101410  102701  101785  102592  101688  103970  103346  104344  101513
2018/07/12 00:15:48 B-test  103108  101409  102702  101785  102593  101688  103971  103345  104345  101513
2018/07/12 00:40:16 B-test  103108  101408  102701  101785  102592  101688  103971  103345  104344  101513

Yes, but the word "A-test" changes the cell location. In a graph, I am trying to compare the data between "A-test" and "B-test" just below "A-test" but I get multiple "A-test". — MickeyMe, May 14 '19 at 18:06

SJR · Answer 1 · 2019-05-14T18:16:24.540

1

Quicker to use the Find method. This assumes the text only occurs once, but can be adapted if this is not the case.

Read this to find out why you probably don't need to select anything.

You might want to swap the 10 and 2 in Resize - not sure what you want there.

Sub y()

Dim r As Range

Set r = Sheets("Sheet1").Range("B:B").Find(What:="A-test", Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)

If Not r Is Nothing Then r.Resize(10, 2).Select '10 rows, 2 columns

End Sub

If you want to refer to the larger range, assign it to a range variable and then you can access its properties and methods directly without needing to use Select.

Sub y()

Dim r As Range, rBig As Range

Set r = Sheets("Sheet1").Range("B:B").Find(What:="A-test", Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)

If Not r Is Nothing Then
    Set rBig = r.Resize(10, 2)
End If

'then do stuff with the 10x2 range
'eg colour it red
rBig.Interior.Color = vbRed

End Sub

edited May 14 '19 at 18:16

answered May 14 '19 at 18:04

SJR

22,986
6
18
26

Thank you. Is there anyway that I can use "Set" or "Set Union" function to it? – MickeyMe May 14 '19 at 18:12
Not sure what you mean, but have added some code - is that what you mean? – SJR May 14 '19 at 18:17
Thank you very much. I will try that. I made the mistake. above. – MickeyMe May 14 '19 at 18:19
If I also want to select "Title = Range("C1:L1").Select " at the same time. How can I do that? Sub Macro3() ' ' Macro3 Macro ' ' Dim Ws As Worksheet Dim i As Long Dim k As Long Dim r As Range EndRow = ActiveWorkbook.Sheets("Sheet1").Range("B1").Offset(Sheets("Sheet1").Rows.Count - 1, 0).End(xlUp).Row i = 1 For k = 2 To EndRow Set r = Sheets("Sheet1").Range("B:B").Find(What:="A-test", Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False) If Not r Is Nothing Then r.Resize(2, 11).Select Range("C1:L1").Select Next End Sub – MickeyMe May 14 '19 at 18:46
I have a title "Sample#1 Sample#2 Sample#3 Sample#4 Sample#5 Sample#6 Sample#7 Sample#8 Sample#9 Sample#10" in the "Range("C1:L1").Select". I would like to select them at the same time when I resize the selection. Thank you. – MickeyMe May 14 '19 at 18:57
`Union(rBig, Range("C1:L1")).Select`. – SJR May 14 '19 at 19:19
Thank you very much. I have another problem. It doesn't select next "A-test". It may have a problem with "For" statement. Sub Macro3() ' ' Macro3 Macro ' Dim Ws As Worksheet Dim k As Long Dim r As Range, rBig As Range EndRow = ActiveWorkbook.Sheets("Sheet1").Range("B1").Offset(Sheets("Sheet1").Rows.Count - 1, 0).End(xlUp).Row For k = 2 To EndRow Set r = Sheets("Sheet1").Range("B:B").Find(What:="A-test", Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False) If Not r Is Nothing Then Set rBig = r.Resize(2, 11) End If Union(rBig, Range("C1:L1")).Select Next End Sub – MickeyMe May 14 '19 at 21:50

Excel VBA Set Range/Cells after searching/find a Cell

1 Answers1