Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 21st, 2011, 01:21 PM
Registered User
 
Join Date: Jul 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default VB.NET & Excel finding a specific string in column B

Does anyone know the code or can send me in the direction to it... to solve this problem...

Using VB.NET i want find a specific string that occurs in column B of an Excelsheet and copy that entire row and paste it in a new worksheet. This string occurs a couple of times in this B column.

I know how to open up an excel sheet in VB but I'm having difficulties searching for the string and then copying that entire row and do it multiple times (loop until the end of the column)

thanks
 
Old October 23rd, 2011, 01:41 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

Here is a small hint how this can be done using VBA

Code:
Sub Drive_The_FindAll_Function()
' Sample Sub to Drive the Function
Dim arTemp() As String            'Temp Array
Dim bFound As Boolean           'Flag
Dim i1 As Integer               'Array Counter
Dim oWS As Worksheet
Dim iCnt As Integer             ' Destination
iCnt = 10
Set oWS = ActiveWorkbook.Sheets(3)
 bFound = FindAll("Ram", ActiveSheet, "B:B", arTemp())
 
 If bFound = True Then
     For i1 = 1 To UBound(arTemp)
       ' The Address Can be used for extracting data
       ActiveSheet.Range(arTemp(i1)).EntireRow.Copy Destination:=oWS.Range("A" & iCnt)
       iCnt = iCnt + 1
    Next i1
 Else
    MsgBox "Search Text Not Found"
 End If
 

End Sub
Function FindAll(ByVal sText As String, ByRef oSht As Worksheet, ByRef sRange As String, ByRef arMatches() As String) As Boolean
' --------------------------------------------------------------------------------------------------------------
' FindAll - To find all instances of the given string and return the row numbers.
'           If there are not any matches the function will return false
' --------------------------------------------------------------------------------------------------------------
 
On Error GoTo Err_Trap
Dim rFnd As Range                       ' Range Object
Dim iArr As Integer                     ' Counter for Array
Dim rFirstAddress                       ' Address of the First Find
' -----------------
' Clear the Array
' -----------------
Erase arMatches
Set rFnd = oSht.Range(sRange).Find(what:=sText, LookIn:=xlValues, lookat:=xlPart)
If Not rFnd Is Nothing Then
    rFirstAddress = rFnd.Address
    Do Until rFnd Is Nothing
       iArr = iArr + 1
       ReDim Preserve arMatches(iArr)
       arMatches(iArr) = rFnd.Address  ' rFnd.Row           '  Store the Row where the text is found
       Set rFnd = oSht.Range(sRange).FindNext(rFnd)
       If rFnd.Address = rFirstAddress Then Exit Do  ' Do not allow wrapped search
    Loop
    FindAll = True
Else
    ' ----------------------
    ' No Value is Found
    ' ----------------------
     FindAll = False
End If

' -----------------------
' Error Handling
' -----------------------
Err_Trap:
If Err <> 0 Then
    MsgBox Err.Number & " " & Err.Description, vbInformation, "Find All"
    Err.Clear
    FindAll = False
    Exit Function
End If
End Function
Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips &amp; Tricks (http://www.vbadud.blogspot.com)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Finding if an attribute has a specific value mphare XSLT 1 February 2nd, 2009 06:57 PM
[VB2005]Finding last cell of the column in excel bellaelysium Visual Basic 2005 Basics 2 June 22nd, 2008 11:33 PM
Finding specific string martinrhague Access 7 November 2nd, 2006 12:54 PM
Finding a specific character within a string tp194 Classic ASP Databases 2 October 12th, 2003 10:41 PM
Finding Field Type & string length From dataset? Jackie VB.NET 2002/2003 Basics 4 July 9th, 2003 04:53 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.