String search with in the excel column values row wise

Question

I have an Excel sheet which has 200 columns. Now number of rows are 3500. So i have to search a string if it presents within any column for each row. Now to make the process fatser,I am looking for any alternate instead of Looping technique. Is there any such?

IntRow6 = 2
DataCount = 0

Do While objSheet6.Cells(IntRow6,1).Value <> ""
  For DataCount = 0 to UBound(VMHArray)
        IntClmn3 = 1        
        Do While 1 = 1
            If objSheet6.Cells(IntRow6,IntClmn3).Value = VMHArray(DataCount) Then
                objSheet6.Cells(IntRow6,IntClmn3+2).Value=objSheet6.Cells(IntRow6,IntClmn3+5).Value
                Exit Do
            End If
            IntClmn3 = IntClmn3 + 1
        Loop
    Next
    IntRow6 = IntRow6 + 1
Loop

The above is taking to much time, thus i am looking for an equivalent VBScript code which can run more faster search.

EDIT:

ParentColmnCount=ParentColmnCount-1
IntRow6=2
DataCount=0

Do While objSheet6.Cells(IntRow6,1).Value <> ""
    For DataCount=0 to UBound(VMHArray)
        If objSheet6.Range(objSheet6.Cells(IntRow6,1),objSheet6.Cells(IntRow6,ParentColmnCo‌unt)).Find(VMHArray(DataCount)) <> Nothing Then
            MsgBox("Hi")
        End If
    Next
    IntRow6=IntRow6+1
Loop

I'm getting any error saying that, "Object variable not set" error at the Range line of the above code.

UPDATE

I have updated my code as per your suggestion,and modified the declaration of variables as below:

Option Explicit

Dim objExcel1,objWorkbook

Dim strPathExcel1

Dim objSheet6,objSheet3

Dim IntRow6,IntRow3

Dim IntClmn3

Dim DataCount,ParentColmnCount

Dim Falg

Dim TaskCounter

Dim r As Range Dim s As Variant

But I am getting the error again: Expected end of statement" in the line "Dim r As Range"

ParentColmnCount=ParentColmnCount-1 IntRow6=2 DataCount=0 Do While objSheet6.Cells(IntRow6,1).Value <> "" For DataCount=0 to UBound(VMHArray) If objSheet6.Range(objSheet6.Cells(IntRow6,1),objSheet6.Cells(IntRow6,ParentColmnCount)).Find(VMHArray(DataCount)) <> Nothing Then MsgBox("Hi") End If Next IntRow6=IntRow6+1 Loop — Arup Rakshit
@pete But getting any error saying that, "Object variable not set" error at the Range line of the above code. — Arup Rakshit

pete pete · Accepted Answer · 2012-12-09T15:31:01

Check out Range.Find (which returns a Range object). It's faster than a loop.

Example:

Sub Test()
    Dim r As Range
    Dim matched As Range

    'Get the second row
    Set r = Sheet1.Range("2:2")
    Set matched = r.Find("myString")

    'Do stuff with matched
End Sub

UDPATE:

Assuming you are using CreateObject("Excel.Application"), then the Range object will be present (as CreateObject will return an instance of Excel which contains Range objects and functionality).

For further proof, you're already looping through the worksheet using Range objects (Cells is a Range object).

UPDATE:

Here's a more advanced example. Assume the following data in on Sheet1:

fred    ethel   ricky   bobby   1   2   3   4
lucy    myrtle  fonzy   rickie  1   2   3   4
joanie  chachie donna   patty   1   2   3   4
selma   homer   lisa    bart    1   2   3   4

You can loop through the ranges like so:

Sub test()
    Dim r As Range
    Dim names(3) As String
    Dim s As Variant

    names(0) = "ethel"
    names(1) = "fonzy"
    names(2) = "patty"
    names(3) = "selma"

    For Each s In names
        For Each r In Sheet1.Cells.CurrentRegion.Find(s)
            r.Offset(0, 4).Value = r.Offset(0, 4).Value + 1000
        Next r
    Next s
End Sub

After the routine is done, the data is now:

fred    ethel   ricky   bobby   1   1002    3   4
lucy    myrtle  fonzy   rickie  1   2   1003    4
joanie  chachie donna   patty   1   2   3   1004
selma   homer   lisa    bart    1001    2   3   4

UPDATE: I just saw your comment on your question (and edited your question to include the updated code).

You're getting "Object variable not set" on that line because you cannot use <> comparisons with objects. Change the line to read:

If Not objSheet6.Range(objSheet6.Cells(IntRow6,1),objSheet6.Cells(IntRow6,ParentColmnCo‌unt)).Find(VMHArray(DataCount)) Is Nothing Then

String search with in the excel column values row wise

1 Answers