Search for exact match of string in excel row using VBA Macro

Question

How do I search for a string in one particular row in excel  the I have the row index in a long type variable   Dim rowIndex As Long rowIndex      some value being set here using some code    Now I need to check if a particular value exists in the row  whoose index is rowIndex   If there is match  I need to get the column Index of the first matching cell   I have tried using Match function  but I dont know how to pass the rowIndex variable in place of the cell range   Dim colIndex As Long     colIndex   Application Match colName  Range  B  lt my rowIndex here gt   Z  lt my rowIndex here gt     0

User · Answer

Never mind  I found the answer   This will do the trick   Dim colIndex As Long     colIndex   Application Match colName  Range Cells rowIndex  1   Cells rowIndex  100    0

User · Answer

Use worksheet find  worksheet is your worksheet  and use the row-range for its range-object  You can get the rangeobject like  worksheet rows rowIndex  as example  Then give find the required parameters it should find it for you fine  If I recall correctly  find returns the first match per default  I have no Excel at hand  so you have to look up find for yourself  sorry  I would advise against using a for-loop it is more fragile and ages slower than find

User · Answer

Try this   Sub GetColumns    Dim lnRow As Long  lnCol As Long  lnRow   3  For testing  lnCol   Sheet1 Cells lnRow  1  EntireRow Find What   sds   LookIn  xlValues  LookAt  xlPart  SearchOrder  xlByColumns  SearchDirection  xlNext  MatchCase  False  Column  End Sub   Probably best not to use colIndex and rowIndex as variable names as they are already mentioned in the Excel Object Library

User · Answer

This is not another code as you have already helped yourself  but for you to take a look at the performance when using Excel functions in VBA    Match   is faster for smaller number of search  but it isn t when it compared to a large number of data  And in that instance you may consider a variant array dictionary arrayList approach depending on if you want to sort  you want to have duplicates or not   Error handling on Match   PS     On a latter note  if you wish to do pattern matching then you may consider ScriptingObject   Regex

[vba] Search for exact match of string in excel row using VBA Macro

Examples related to vba

Examples related to excel