Column numbers of matched strings of an Excel sheet rows

Question

I wrote a code to search a each of the array variable if presents in each row of a Sheet.Now When the match will be found i need to know what the column number is,where the search string has been found. Any idea how to get that using vb script?

ParentColmnCount=ParentColmnCount-1

IntRow6=2

DataCount=0

Do While objSheet6.Cells(IntRow6,1).Value <> ""

For DataCount=0 to UBound(VMHArray)
    If Not objSheet6.Range(objSheet6.Cells(IntRow6,1),objSheet6.Cells(IntRow6,ParentColmnCount)).Find(VMHArray(DataCount)) Is Nothing Then
MsgBox(objSheet6.Range(objSheet6.Cells(IntRow6,1),objSheet6.Cells(IntRow6,ParentColmnCount)).Find(VMHArray(DataCount)).Columns)

    End If
      Next

IntRow6=IntRow6+1

Loop

Update:

 IntRow6=2
 DataCount=0
 Do While objSheet6.Cells(IntRow6,1).Value <> ""

 For DataCount=0 to UBound(VMHArray)


  Set rSearch = objSheet6.Cells(IntRow6,1).EntireRow
  Set rFound = rSearch.Find(VMHArray(DataCount))

        If Not rFound Is Nothing Then
                 adrFirst = rFound.Address

               Do
                   MsgBox(IntRow6)
                   MsgBox(rFound.Column + 1)
                   MsgBox(adrFirst)
                   rCol=rFound.Column
                   objSheet6.Cells(IntRow6,rCol + 2)= objSheet6.Cells(IntRow6,rCol + 5)
                   Set rFound = rSearch.FindNext(rFound)
               Loop Until rFound.Address <> adrFirst And Not rFound Is Nothing
       End If

 Next

 IntRow6=IntRow6+1
 Loop

But it seems the control falls into the infinite Loop and continuously giving 21 as its first matched column number.Producing the output as below:

    2 33 $AF$2     2 33 $AF$2         2 33 $AF$2  .......

Any idea Why So?

Thanks

Ekkehard.Horner · Accepted Answer · 2012-12-10T16:15:48.033

Demo script to show how to use .Find:

  Dim oFS    : Set oFS  = CreateObject("Scripting.FileSystemObject")
  Dim sFSpec : sFSpec   = oFS.BuildPath(oFS.GetAbsolutePathname("..\xls"), "13763603.xls")
  Dim oXls   : Set oXls = CreateObject("Excel.Application")
  Dim oWb    : Set oWb  = oXls.Workbooks.Open(sFSpec)
  Dim oWs    : Set oWs  = oWb.Worksheets(2)
  Dim rSearch, rFound, adrFirst

  Set rSearch = oWs.Cells(1, 1).EntireRow
  Set rFound = rSearch.Find("b1")
  WScript.Echo 1, "TypeName(rFound):", TypeName(rFound), rFound.Row, rFound.Column

  Set rSearch = oWs.Range(oWs.Cells(1, 1), oWs.Cells(5, 3)) 
  Set rFound = rSearch.Find("nix")
  WScript.Echo 2, "TypeName(rFound):", TypeName(rFound)

  Set rFound = rSearch.Find("alpha")
  If Not rFound Is Nothing Then
     adrFirst = rFound.Address
     Do
         WScript.Echo 3, "TypeName(rFound):", TypeName(rFound), rFound.Row, rFound.Column
         Set rFound = rSearch.FindNext(rFound)
     Loop Until rFound.Address = adrFirst
  End If

  oWb.Close  
  oXls.Quit

output for this sheet:

alpha sheet :

1 TypeName(rFound): Range 1 2
2 TypeName(rFound): Nothing
3 TypeName(rFound): Range 1 3
3 TypeName(rFound): Range 2 1
3 TypeName(rFound): Range 4 2
3 TypeName(rFound): Range 5 3

Update:

As can be seen from the output

----------------------
4 1 TypeName(rFound): Range 1 3
Wahr     rFound.Address <> adrFirst
Falsch   rFound Is Nothing
Wahr     Not rFound Is Nothing
Wahr     rFound.Address <> adrFirst And Not rFound Is Nothing

of my diagnostic code insert into the above script:

  WScript.Echo "----------------------"
  Dim nCnt : nCnt = 0
  Set rFound = rSearch.Find("alpha")
  If Not rFound Is Nothing Then
     adrFirst = rFound.Address
     Do
         nCnt = nCnt + 1
         If nCnt > 9 Then WScript.Echo "Aborting" : Exit Do : End If
         WScript.Echo 4, nCnt, "TypeName(rFound):", TypeName(rFound), rFound.Row, rFound.Column
         Set rFound = rSearch.FindNext(rFound)
         WScript.Echo CStr(rFound.Address <> adrFirst), vbTab, "rFound.Address <> adrFirst"
         WScript.Echo CStr(rFound Is Nothing), vbTab, "rFound Is Nothing"
         WScript.Echo CStr(Not rFound Is Nothing), vbTab, "Not rFound Is Nothing"
         WScript.Echo CStr(rFound.Address <> adrFirst And Not rFound Is Nothing), vbTab, "rFound.Address <> adrFirst And Not rFound Is Nothing"
         WScript.Echo
     Loop Until rFound.Address <> adrFirst And Not rFound Is Nothing
  End If

it's not a good idea to replace my carefully crafted simple condition

 Loop Until rFound.Address = adrFirst

with something complicated - not to say: silly - like

Loop Until rFound.Address <> adrFirst And Not rFound Is Nothing

What on earth made you do that?

May i have some help on my new question,any faster code? http://stackoverflow.com/questions/13798858/row-data-partition-empty-column-values-in-a-row-in-one-side-and-non-empties-ar#comment18983534_13798858 — Arup Rakshit, Dec 10 '12 at 13:51
As per my research what i feel that FindNext() method searching the same value if present in the same row or not,when it has been found once.So if the second time it presents in the same row,loop will be continued otherwise rFound.Address and adrFirst will be same and the loop condition "rFound.Address <> adrFirst" should break the Loop to iterate further more. But in my case it is not happening,it falls into the forever loop,which is proved by the Output as i mentioned in my updated code. So what the reason is? — Arup Rakshit, Dec 11 '12 at 05:06
@user1878162 - your assumptions are wrong; please study my update carefully to see why your misguided loop condition forces an immediate exit: "Not rFound Is Nothing" is always True, "rFound.Address <> adrFirst" is True (because the second match is found), now consider the difference between "Until" and "While". — Ekkehard.Horner, Dec 11 '12 at 07:54
Can you help me here in my below post please? http://stackoverflow.com/questions/13820907/to-delete-an-excel-column-which-contains-no-data-in-any-of-the-cell-of-it-using/13821320#13821320 — Arup Rakshit, Dec 11 '12 at 15:50

Column numbers of matched strings of an Excel sheet rows

1 Answers1