VBA required to delimit cells with Rules applied over it.

[amb2301]

Hi Friends,

Firstly Thanks to DocAElstein for reffering me to this Forum.


i need a help on fixing an existing VBA script,

Function of Existing Script:
i have hundreds of addresses to Delimit into seperate cells as shown in the attached sample file(Address sheet.xlsm)
i have a script to delimit those addresses Available in A column to the B,C,D & E column.
COlumn A: contains full address
COlumn B: Door number
COlumn C: Direction (N,E,S,W)
COlumn D: Street Name
COlumn E: Street Type

Twist is at Directions(N,E,S,W), sometimes it comes next to Door# (or) at the end of an address.
existing script, even do that work perfectly.

Current Requirement:
Now some addresses comes with different scenarios,
i have highlighted in yellow colour in the attached excel(green higlighted cells are working fine with existing script).
if single Numberical value (1,2,3.4,5,6,7,8,9) comes in C column,it should be moved to the D column
by adding text like(1st,2nd,3rd,4th,5th,6th,7th,8th,9th).

Could anyone please help me to resolve this issue.


Sub Demo1()
Dim V(), W(), R&, S, C%
V = Application.Trim(Range("A2", [A1].End(xlDown)))
ReDim W(1 To UBound(V), 3)
For R = 1 To UBound(V)
S = Split(V(R, 1))
If IsNumeric(S(0)) Then
W(R, 0) = S(0)
If Len(S(1)) = 1 Then
W(R, 1) = S(1): W(R, 2) = S(2): W(R, 3) = S(3)
ElseIf Len(S(UBound(S))) = 1 Then
W(R, 1) = S(UBound(S)): W(R, 2) = S(1): W(R, 3) = S(2)
Else
If UBound(S) = 3 Then W(R, 2) = S(1) & " " & S(2) Else W(R, 2) = S(1)
W(R, 3) = S(UBound(S))
End If
Else
W(R, 0) = Left(S(0), Len(S(0)) - 1): W(R, 1) = Right(S(0), 1): W(R, 2) = S(1): W(R, 3) = S(2)
End If
Next
[B2:E2].Resize(R - 1) = W
End Sub


Thanks in Advance.







Cross Posts:
https://www.excelforum.com/excel-programming-vba-macros/1316009-vba-required-to-delimit-cells-with-some-rules-applied-on-it.html
https://www.excelguru.ca/forums/showthread.php?10645-VBA-required-to-delimit-cells-with-Rules-applied-over-it&

[DocAElstein]

Hi amb2301
I think you are making this a lot more difficult then it needs to be and will be possibly wasting a lot of time unnecessarily.

I am a bit confused at what you are asking for, because you are mixing up data and requirement descriptions. You may send yourself and others trying to help you going around and around in circles.

I think you need to make a small set of test data, but think very carefully and give data which shows all possible scenarios.
You need to give very clearly in words what you want.
You need to show clearly what you want

You will possibly know what you want. But I am confused. Possibly you yourself do not yet understand what is required. For example****

You say that the green highlighted cells are working fine with existing script ??? – Is that correct ?? – take a look: Here (https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13380&viewfull=1#post13380) is a sample of your supplied data, which is produced from your existing script:
https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13380&viewfull=1#post13380
Those results are all mixed up!!



This could mean anything ..” if single Numberical value (1,2,3.4,5,6,7,8,9) comes in C column,it should be moved to the D column
by adding text like(1st,2nd,3rd,4th,5th,6th,7th,8th,9th).”….
Of course, you will understand that. You will understand that because you know what you want.
For anyone else , it can mean lots of things.

If you still want help with this, I can take another look again here tomorrow.
If you want my help you must explain and show clearly what you want.
You must give a small amount of rows that show every possible scenario. You must explain clearly what you want. And you must manually write in the results as you want them correctly to be done by the macro.

It is impossible to know what you want, since the results of the script, which you say works for the green highlighted cells, is producing rubbish.
I don’t think this is a difficult problem to solve. But you have not yet explained clearly what you want.
Nobody, except you, can know what you want. ( Or worse... you may also not yet know what you want)


Alan

P.S****Do you know that in English language, Direction is not just
N,E,S,W
It is
N,NE, E,SE, S,SW,W,NW
This is one reason why your results highlighted in green are rubbish

_____ Workbook: address sheet.xlsm ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G

1AddressDoor#Directionstreet nameroadtypestreet name + roadtypeCity Name


11
102 11 AVE S
102S
11AVE


12
10205 134 AVE NW
10205134 AVENW
Worksheet: Sheet1

[amb2301]

Hi Sir,
Thanks for looking on my post & Sorry for making you confused because of my poor explaination,

Actually i am using a Application, where i need to trigger these splitted addresses from excel to that application screen,
Here as you said for Address (10205 134 AVE NW) , i know that NW(Northwest) is direction, but as per our rule it should not be in C column,
C column should contain only (N,E,S,W).

NoW Existing script considering like...if second word or last word of addresses contains any single character its putting it in C column,
but now i need like...it should consider only the letters (N,E,S,W).....Rest all should be not be considered in C column.

if i am not clear please let me know, i will send screenshots of that Application, which i am using.

Thanks in Advance.

[DocAElstein]

I don’t have any more time today, I can look again tomorrow, if you want me to.
But you must try to understand some English. Your reply to me suggest that you have not read, or not understood all of what I have written.

You have given no where near enough detail. It is a very , very long way from clear what you actually want.


You need to give about 20 rows of data which should show exactly what you want. Type the results in manually. Don’t use that script to do it.
I told you already, your existing script is rubbish. It has never given you correct results. Those results highlighted in green are mostly incorrect rubbish.

Why do you say the existing code works or worked perfectly? It produces a lot of false results. It has never met your requirements. A lot of the results highlighted in green are false .._
_.. For example…
for Address (10205 134 AVE NW)
what is 10205
what is 134
?? How can a street name be 134 AV
Is 10205 a door??
Etc.. etc..

It is very simple what I need : Give me a file with about 20 rows. Show me exactly what you want.
Pick very carefully the sample address that you use , so that it demonstrates all scenarios.


I fear you may have a very simple requirement, needing a very simple solution, but unfortunately you are not able to understand what I am saying to you.



I wont look here again today. So if you want more help , I suggest you try again to read what I have said, and then supply me what I have asked for.