Dear excel expert, I would like to create macro which can perform belo
function hopefully someone can help me as I am not strong in exce
programming.

in column A i got all the model number (string) in each row such as :
Column A
AB-12345
AB-12345A
AB-12345AB
AB-12345ACB
BA-A23
BA-A23A
BA-A23AB
BA-A23ABC

I want a macro which can perform as below once activated.
Column A...................Column B.......or...Column B
AB-12345...................AB-12345.............BA-A23
AB-12345A.................AB-12345A...........BA-A32A
AB-12345AB...............AB-12345AB.........BA-A32AB
AB-12345ACB
BA-A23
BA-A23A
BA-A23AB
BA-A23ABC

Up to 2 alphabet charecter after the number charecter within 1 string

You help is very appreciate. Thanks

--
brandon
-----------------------------------------------------------------------
brandonb's Profile: http://www.excelforum.com/member.php...fo&userid=3251
View this thread: http://www.excelforum.com/showthread.php?threadid=52341

Are you trying to get models numbers that begin with AB in one column and
those that start with BA in another?

"brandonb" wrote:


Dear excel expert, I would like to create macro which can perform below
function hopefully someone can help me as I am not strong in excel
programming.

in column A i got all the model number (string) in each row such as :
Column A
AB-12345
AB-12345A
AB-12345AB
AB-12345ACB
BA-A23
BA-A23A
BA-A23AB
BA-A23ABC

I want a macro which can perform as below once activated.
Column A...................Column B.......or...Column B
AB-12345...................AB-12345.............BA-A23
AB-12345A.................AB-12345A...........BA-A32A
AB-12345AB...............AB-12345AB.........BA-A32AB
AB-12345ACB
BA-A23
BA-A23A
BA-A23AB
BA-A23ABC

Up to 2 alphabet charecter after the number charecter within 1 string

You help is very appreciate. Thanks !


--
brandonb
------------------------------------------------------------------------
brandonb's Profile: http://www.excelforum.com/member.php...o&userid=32511
View this thread: http://www.excelforum.com/showthread...hreadid=523413

John Wrote:
Are you trying to get models numbers that begin with AB in one colum
and
those that start with BA in another?

Ok what I want is lets say i enter BA-A23 the macro will find from al
my model list (maybe more than 1000 raw) then it will find the simila
model name such as BA-A23A, BA-A23AB, BA-A23ABC. The result will b
display unto another column

--
brandon
-----------------------------------------------------------------------
brandonb's Profile: http://www.excelforum.com/member.php...fo&userid=3251
View this thread: http://www.excelforum.com/showthread.php?threadid=52341

Where you entering your model number, from a userform or on the shee
itself

--
gti_jober
-----------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...fo&userid=3063
View this thread: http://www.excelforum.com/showthread.php?threadid=52341

ok i've just wrote this for you, not checked it....but should work


Code
-------------------

Dim myArray(), maxArray%, myModelNo$

Erase myArray

myModelNo = where ever you get your _
value (from user form? from entry on sheet??)

'find all you model numbers based on input
i = 1
Do
If InStr(1, Cells(i, 1).Value, myModelNo, vbTextCompare) < 0 Then

maxArray = maxArray + 1
ReDim Preserve Arry(1 To maxArray)
Arry(maxArray) = Cells(i, 1).Value 'Stores your model in Array

End If
i = i + 1
Loop Until Cells(i, 1).Value = ""

'output model numbers to new column
i = 1
Do
Cells(i, 2).Value = Arry(i)
i = i + 1
Loop Until i = maxArray

-------------------

--
gti_jober
-----------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...fo&userid=3063
View this thread: http://www.excelforum.com/showthread.php?threadid=52341