Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching and storing Column Number
Hi,
Is there a way in VB I can search for a word in an excel file, and then once found I store the column number of the cell where the word is found? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching and storing Column Number
One way:
Sub oneWay() Dim r As Long Dim sString As String sString = "fred" On Error Resume Next r = Cells.Find(What:=sString, After:=Cells(1, 1), _ LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Column On Error GoTo 0 If r = 0 Then MsgBox "not found" Else MsgBox r End If End Sub wrote: Hi, Is there a way in VB I can search for a word in an excel file, and then once found I store the column number of the cell where the word is found? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching and storing Column Number
Thanks,
I tried that and I keep getting 1 as my answer but that's not where the word is found.. Any idea why? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching and storing Column Number
Did you look through column A to see if your string was anywhere in that column?
If you did, you may want to post your code and some details about what's in your worksheet and where. wrote: Thanks, I tried that and I keep getting 1 as my answer but that's not where the word is found.. Any idea why? -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching and storing Column Number
My Macro is exactly as JW wrote it: nothing before or after... also,
there's only one occurance of the word. Thanks On Sep 28, 2:16 pm, Dave Peterson wrote: Did you look through column A to see if your string was anywhere in that column? If you did, you may want to post your code and some details about what's in your worksheet and where. wrote: Thanks, I tried that and I keep getting 1 as my answer but that's not where the word is found.. Any idea why? -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching and storing Column Number
Sub oneWay()
Dim r As Range Dim sString As String sString = "fred" ' change this to the word you are looking for set r = Cells.Find(What:=sString, _ After:=Cells(rows.count, columns.count), _ LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) If r is nothing then MsgBox "not found" Else application.Goto r, true MsgBox r.column End If End Sub -- Regards, Tom Ogilvy " wrote: My Macro is exactly as JW wrote it: nothing before or after... also, there's only one occurance of the word. Thanks On Sep 28, 2:16 pm, Dave Peterson wrote: Did you look through column A to see if your string was anywhere in that column? If you did, you may want to post your code and some details about what's in your worksheet and where. wrote: Thanks, I tried that and I keep getting 1 as my answer but that's not where the word is found.. Any idea why? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching for low and high number | Excel Worksheet Functions | |||
Storing column() to use later | Excel Programming | |||
storing lettrs in an excel cell to later = a number for a formula | Excel Discussion (Misc queries) | |||
Storing column references in a variable | Excel Programming | |||
Storing a column of input data | Excel Programming |