Return column numbers where word appears on row
Use a function like the following:
Function ColumnsWithWord(WhatWord As String, InRow As Long) As Long() Dim ResArr() As Long Dim ColNdx As Long Dim ArrNdx As Long Dim LastCol As Long LastCol = Cells(InRow, Columns.Count).End(xlToLeft).Column ReDim ResArr(1 To LastCol) For ColNdx = 1 To LastCol If InStr(1, Cells(InRow, ColNdx).Text, WhatWord, vbTextCompare) 0 Then ArrNdx = ArrNdx + 1 ResArr(ArrNdx) = ColNdx End If Next ColNdx If ArrNdx = 1 Then ReDim Preserve ResArr(1 To ArrNdx) Else Erase ResArr End If ColumnsWithWord = ResArr End Function It will return a 1-based array of Longs each of which is the column number in which WhatWord was found. (It test for the existence of WhatWord anywhere in the text of the tested cell.) If the word WhatWord was not found in the cells in row InRow, the return array is unallocated. You call this function with code like Sub AAA() Dim L() As Long Dim UB As Long Dim N As Long Dim WhatWord As String WhatWord = "project123" L = ColumnsWithWord(WhatWord:=WhatWord, InRow:=8) On Error Resume Next UB = UBound(L) If Err.Number < 0 Then Debug.Print "Unallocated array: Word Not Found" Else For N = LBound(L) To UBound(L) Debug.Print "Word: " & WhatWord & " found in column " & CStr(L(N)) Next N End If End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "XP" wrote in message ... Using Office 2003 and Windows XP; has anyone got a function to return the column number of each column containing the word "Project" on a specific row? For example on row 7: col "A" contains "Project"; col "E" contains "project"; col "G" contains "PROJECT"; the Count would = 3 and perhaps an array? would contain column numbers: 1, 5, 7. Please post example code if possible, thanks. |
All times are GMT +1. The time now is 08:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com