Search Excel column from within Access question

Euler271

New Member
Joined
Dec 4, 2017
Messages
31
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?


 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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".
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top