Urgent help from all excel expert !

  • Thread starter Thread starter brandonb
  • Start date Start date
B

brandonb

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
 
Are you trying to get models numbers that begin with AB in one column and
those that start with BA in another?
 
John said:
Are you trying to get models numbers that begin with AB in one colum
and
those that start with BA in another?
Click to expand...
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
 
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
 
Back
Top