Macro - list rows number of the cells which contains value "Code: "

Question

I have a sheet in which Column "A" contains values. I want to collect the row numbers in a new sheet where string "Code: " is found. I want to write VBA code to achieve this. For example :

Sheet1 :

RowNo | Column A
------|---------
 1    | Hello World
 2    | Good morning
 3    | Code: 46904A65
 4    | Excuse
 5    | Code: 4523S45

Output Sheet :

RowNo | Column A
------|---------
 1    | 3
 2    | 5

Explanation : String "Code: " found in 3rd and 4th row of sheet 1. So output sheet contains those row numbers in it.

The following code is not working :

Set stTempData = Sheets("Output Sheet")
stTempData.Select

Set mainsheet = Sheets("Sheet1")
mainsheet.Select
k = Range("a65536").End(xlUp).Row

i = 1
Do While i < k

Set r = stTempData.Range("a65536").End(xlUp).Offset(1, 0)

If InStr(ActiveSheet.Cells(i, 0).Value, "Code:") > 0 Then
    r.Offset(i, 0).Value = ActiveCell.Row - 1
    j = i
    r.Offset(j + 1, 1).Value = ActiveCell.Row - 2
End If

i = i + 1
Loop

`The following code is not working ` does not help us much. What is it doing that is wrong? — Scott Craner, Aug 05 '16 at 20:10
mainly if condition. I am not able to select the value of the cell. `ActiveSheet.Cells(i, 0).Value` is not valid — Sandeep Kushwah, Aug 05 '16 at 20:12
Use Autofilter as shown [Here](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) — Siddharth Rout, Aug 05 '16 at 20:18

Scott Craner · Accepted Answer · 2016-08-05T20:20:35.830

2

Put this in A1 in the output sheet:

=IFERROR(AGGREGATE(15,6,ROW(Sheet1!$A$1:$A$5)/(ISNUMBER(SEARCH("Code",Sheet1!$A$1:$A$5))),ROW(1:1)),"")

And copy down till you get blanks.

As to your code:

There is no column 0 So your Cells() needs to change to 1. Also you do not want to offset r. One more thing you had activesheet and activecell which was throwing it off.:

Set stTempData = Sheets("Output Sheet")
stTempData.Select

Set mainsheet = Sheets("Sheet1")
mainsheet.Select
k = Range("a65536").End(xlUp).Row

i = 1
Do While i <= k

    Set r = stTempData.Range("a65536").End(xlUp).Offset(1, 0)

    If InStr(mainsheet.Cells(i, 1).Value, "Code:") > 0 Then
        r.Value = mainsheet.Cells(i, 1).Row
    End If

    i = i + 1
Loop

edited Aug 05 '16 at 20:20

answered Aug 05 '16 at 20:01

Scott Craner

148,073
10
49
81

+1 but my bad. I wanted VBA macro code to achieve this functionality. Could you please help me with VBA? – Sandeep Kushwah Aug 05 '16 at 20:09
Thanks Scott, I am validating it. Once done will mark it as answer :) – Sandeep Kushwah Aug 05 '16 at 20:23

Macro - list rows number of the cells which contains value "Code: "

1 Answers1