Search Excel column from within Access question

[Euler271]

I'm writing some VBA code that should search for certain words in the Address1 field (e.g., "Suite", "Ste", "Apt") then copies the word and what follows it to the Address2 column. I also need it to truncate the Address1 value so that the word and what follows it no longer appears there.

For example: Address1 = "1234 Park Ave Ste 234"
The code should find "Ste" in the cell's value then copy "Ste 234" into the Address2 column. Address1 should then be changed to "1234 Park Ave".

The code should then recycle and search Address1 again for the word "Suite" and then do the same thing.

Here's the code I have for "Ste":
With xlWs.Range("L2:L" & xlWs.Cells.SpecialCells(xlLastCell).row)
Set FindRow = .Find(What:="Ste", LookAt:=xlPart, SearchOrder:=xlByRows, LookIn:=xlValues)
If Not FindRow Is Nothing Then
Set FirstRow = FindRow
xlWs.Range("M" & FindRow.row).Formula = "=Right(L" & FindRow.row & ", LEN(L" & FindRow.row & ")-Search(""Ste"", L" & FindRow.row & ")+1)"
Do
xlWs.Range("M" & FindRow.row).Formula = "=Right(L" & FindRow.row & ", LEN(L" & FindRow.row & ")-Search(""Ste"", L" & FindRow.row & ")+1)"
Set FindRow = .FindNext(after:=FindRow)
Loop While Not FindRow Is Nothing And FindRow <> FirstRow
End If
End With
It seems to work for my first search for "Suite" but then doesn't work for the other words. Maybe I have to get it to the first cell in the column to start the search again.

Does anyone have any ideas about this? Does the code look correct?

 

[TDCox]

Hi @Euler271. You appear to have worked out how to search the Excel Column from within Access based on your post and code, but are having difficulties with locating the words you're after. Does the code you're using for Suite also look the same (just with "Suite" instead of "Ste")? There doesn't appear to be any allowance for case differences in your code at present and it would be better to encapsulate within a macro that you passed a phrase to and received the portion back, using a single variable for the code you're after. Can you provide an example of the data you're using and the rest of the code please? Additionally, it appears from the code you have provided that you're doing an on the fly formula replacement rather than grabbing the value, and spitting out the fixed values you need; assuming the other two macros are the same, each row would have the formula written into multiple times, and possibly replacing previous values (i.e. if a line had "1234 Park Ave Suite 3 Apt 4" your column M would potentially end up with a formula to extract "Apt 4", but nothing about "Suite 3"); moreover at present there doesn't appear to be a point where you get the first portion - the whole address is left in column "l" with a formula to split off a portion in column "m".