Most Urgent----CONVERT FORMULA INTO CODE

[fari1]

Hi, i want to convert my below formula to code, which loop through entire column A and bring me those rows which contain 10-(along with there can be anything).

=INDEX($A$2:$A$401,MATCH("10-*",$A$2:$A$401,0))

urgent help is required

 

[taurean]

i guess it is a function, i dont know how to run a function, will u plz guide

That is not a function. Looking at the other thread where you had posted your requirement, following code should do fine.

Step 1) Right click on Sheet1 tab, choose "View Code". VBA module will be opened.
Step 2) Paste the following code in it:

Public Sub FindData()
Dim lLastRow As Long
With Sheet1
lLastRow = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To lLastRow
        If InStr(1, .Range("A" & i).Value, "10-") > 0 Then
        .Rows(i).Copy Destination:=Sheet2.Range("A" & Rows.Count).End(xlUp)(2)
        End If
    Next i
    Application.CutCopyMode = False
End With
End Sub

Step 3) Stay on Sheet1 and run the code and see if this is what you were after.

 

[fari1]

Its not showing any results, why is that so

 

[fari1]

I've shown the link for my data in my earlier post and want u to check it plz, to get idea why its not working

 

[taurean]

I had seen your other post and have tested that data. It works fine here!

1. Does the code give any error? Or it just runs and doesn't do a thing.

2. Have you renamed any of these sheets?

 

[fari1]

Its just runs and does nothing, and the sheet names i've not changed, they'r same, i'm unable to find

 

[taurean]

That's intriguing, does the column A contain the specified data or is it in some other column?

 

[fari1]

its just on column A and no where else, can u give me email id, i'll email u, or u email me, and i'll provide u my email id

 

[taurean]

ss_redij at yahoo dot com

 

[kpark91]

You have to spell everything out for her. Including the worksheet names.

Sub FindData()
Dim lLastRow As Long
With Worksheets("Filings")
lLastRow = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To lLastRow
        If InStr(1, .Range("A" & i).Value, "10-") > 0 Then
        .Rows(i).Copy Destination:=Sheet2.Range("B" & Rows.Count).End(xlUp)(2)
        End If
    Next i
    Application.CutCopyMode = False
End With
End Sub

 

[fari1]

thats great kevin,u just came in and telling him about my qualities, gud,

anyways taurean, your code is working in a separate worksheet, but not running in my workbook,i've to check it, may be some setting has to be changed, thanks alot for your help