Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count how many times the same word appears in column | Excel Discussion (Misc queries) | |||
Insert pagebreak when a specific word appears in a column. | Excel Discussion (Misc queries) | |||
how to return a set of numbers in column A | Excel Worksheet Functions | |||
count the number of times a specific word appears in a column | Excel Worksheet Functions | |||
need formula to search column for a word and return another word | Excel Discussion (Misc queries) |