Solved

Finding text in perticular column.

Posted on 2004-09-24
7
140 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

747 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now