Finding text in perticular column.

I am trying to make a code to find text "S1" in excel sheet column in cell number range L14:L65525 and if it is present then I want to assign it's cell address to an array location say A(1). I tried to find in VB help, I think "FIND" method is useful for this but I don't know how to use this can anyone give me the code?
skpd1978Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
R_RajeshConnect With a Mentor Commented:
if you are coding in vb, try this (was written in vba. may need some changes)

Private Sub Form_Load()
Dim xlApp As Object, xlWkb As Object
Dim rng1 As Object, rng2 As Object
ReDim mAddress(0) As String
Set xlApp = CreateObject("Excel.Application")
Set xlWkb = xlApp.workbooks.open("C:\myxl.xls")
Set rng1 = xlWkb.Worksheets(1).Range("L14:L65525")
With rng1
Set rng2 = .Find("S1", LookIn:=-4163)
If Not rng2 Is Nothing Then
Do
mAddress(UBound(mAddress)) = rng2.Address
ReDim Preserve mAddress(UBound(mAddress) + 1) As String
Set rng2 = .FindNext(rng2)
Loop While Not rng2 Is Nothing And rng2.Address <> mAddress(0)
End If
End With
Set rng1 = Nothing: Set rng2 = Nothing
xlWkb.Close 0: Set xlWkb = Nothing
xlApp.quit: Set xlApp = Nothing

For i = 0 To UBound(mAddress) - 1
MsgBox mAddress(i)
Next i
End Sub

Rajesh
0
 
R_RajeshCommented:
Hi skpd1978,

try this, its written in VBA

Sub mfind()
Dim rng1 As Range, rng2 As Range
ReDim mAddress(0) As String
Set rng1 = Worksheets(1).Range("L14:L65525")
With rng1
Set rng2 = .Find("S1", LookIn:=xlValues)
If Not rng2 Is Nothing Then
Do
mAddress(UBound(mAddress)) = rng2.Address
ReDim Preserve mAddress(UBound(mAddress) + 1) As String
Set rng2 = .FindNext(rng2)
Loop While Not rng2 Is Nothing And rng2.Address <> mAddress(0)
End If
End With
For i = 0 To UBound(mAddress) - 1
MsgBox mAddress(i)
Next i
End Sub


the array mAddress() will have all the cell address that contains the string "S1"

Rajesh
0
 
DaveCommented:
Rajesh,

You make it pretty hard for the rest of us to earn any points here :)
skpd1978, if this does help then please don't award me any points, it is a minor tweak to RR's code

The Lookat property can be set to either xlWhole (entire string) or xlPart (partial match) and this property is saved as its last used value

so you may want to use either
set rng2 =.Find("S1", LookIn:=xlValues, LookAt:=xlPart)
or
set rng2 .Find("S1", LookIn:=xlValues, LookAt:=xlWhole)

Cheers

Dave



0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
R_RajeshCommented:
look who is talking about making it hard to earn any points :)

Thanks, Dave. Iam pretty certain skpd1978's next question would have been about making partial match.

Rajesh
0
 
DaveCommented:
LOL - but they all come in the same TA

I can't diversify with you in here :)
0
 
DaveCommented:
skpd1978,

I think that this answer should have been awarded to Rajesh, I have posted a request at CS to do so in http://oldlook.experts-exchange.com:8080/Community_Support/Q_21148893.html

Cheers

Dave
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.