Solved

Finding text in perticular column.

Posted on 2004-09-24
7
146 Views
Last Modified: 2010-05-02
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
Comment
Question by:skpd1978
  • 3
  • 3
7 Comments
 
LVL 24

Expert Comment

by:R_Rajesh
ID: 12147547
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
 
LVL 24

Accepted Solution

by:
R_Rajesh earned 500 total points
ID: 12147651
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
 
LVL 50

Expert Comment

by:Dave Brett
ID: 12157777
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 24

Expert Comment

by:R_Rajesh
ID: 12162123
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
 
LVL 50

Expert Comment

by:Dave Brett
ID: 12169755
LOL - but they all come in the same TA

I can't diversify with you in here :)
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 12176625
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

823 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question