Solved

Finding text in perticular column.

Posted on 2004-09-24
7
143 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

910 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

20 Experts available now in Live!

Get 1:1 Help Now