Urgent help from all excel expert !

**brandonb** · 03-17-2006, 12:11 AM

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 !

John · 03-17-2006, 04:50 AM

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
>
>

**brandonb** · 03-17-2006, 05:14 AM

Originally Posted by John

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

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

**gti_jobert** · 03-17-2006, 06:15 AM

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

**gti_jobert** · 03-17-2006, 06:24 AM

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

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 myArray(1 To maxArray)
        myArray(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 = myArray(i)
i = i + 1
Loop Until i = maxArray

Urgent help from all excel expert !

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Urgent help from all excel expert !

RE: Urgent help from all excel expert !

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions