• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 161
  • Last Modified:

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?
0
skpd1978
Asked:
skpd1978
  • 3
  • 3
1 Solution
 
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
 
R_RajeshCommented:
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now