Hi, everyone. I need for help.
i now is facing some problem of my project.
This is the one of the data at the cells (column)
700cm 600CP D1 DLC
And i want change it to this format
D-1 700cm DLC 600CP
So, how can i pick or search the location of D1 and put it in the front of the cells. And also change the data to the sequence that i want .
thanks & Best Regards,
Andy
Hi Andy,
Regarding to your sample data, it can be changed by the following code,
But I must ask you something...
Q1:Is D1 is located in same place?
Q2:Is there any regulations? (Eg, the data had always cm, CP and CLC...something like this)
Sub Test()
MsgBox ChangeTomyFormat([A1])
End Sub
Function ChangeTomyFormat(ByVal rng As Range) As String
Dim buf, ret, tmp()
buf = Split(rng.Value, " ")
ReDim tmp(UBound(buf))
tmp(0) = Left(buf(2), 1) & "-" & Mid(buf(2), 2)
tmp(1) = buf(0)
tmp(2) = buf(3)
tmp(3) = buf(1)
ChangeTomyFormat = Join(tmp)
End Function
Hi Colo,
Actually is like that, P1, Seg, Componet, Type is a field. There have thousand of data like that, but the field for (P1, Seg, Component) will change only, and Type will not change.
Seg...........Component..........................Type
--------------------------------------------------
MB..........700cm 600CP D1 DLC............API
MB...........500cm 300CP K2 DDPA.........API
And i will need to search by the column field "Type", if have API name, i will need to change the naming of field (Seg and Component) to the format below.
Seg.........................Component............Type
------------------------------------------------------
Monitor 600CP......D-1 700cm DLC.......API
Monitor 300CP......K-2 500cm DDPA.....API
The "." is nothing, mean space only at here.
And for you question :
Q1 : D1 is located at same place, that is the 13th -14th character in the cell.
Q2 : The size of the data is fix, maybe is 200cm , 500cm, 700cm only. It is from the 1st to 5th character in the cell.
I just starting learn using VBA to do my project. Many function is didn't no very kknow how to sure yet. So, are you know how to auto change the sequence format like above for the all worksheet data when i enter a button.
Thanks & Best Regards,
Andy
Hi Andy,
Assume your worksheet is as follows, please try this code.
Option Explicit
Sub UDSort()
Dim buf, i As Long
buf = [A1].CurrentRegion.Value
For i = LBound(buf) + 1 To UBound(buf)
If Not IsEmpty(buf(i, 3)) Then
buf(i, 1) = IIf(GetMonitor(buf(i, 2)) = vbNullString, buf(i, 1), GetMonitor(buf(i, 2)))
buf(i, 2) = SortComp(buf(i, 2))
End If
Next
[A1].CurrentRegion.Value = buf
End Sub
Function GetMonitor(ByVal strSeq As String) As String
Dim a, i As Long
a = Split(strSeq, " ")
For i = LBound(a) To UBound(a)
If a(i) Like "*CP" Then GetMonitor = "Monitor " & a(i): Exit Function
Next
GetMonitor = vbNullString
End Function
Function SortComp(ByVal strSeq As String) As String
Dim a, b(0 To 2)
a = Split(strSeq, " ")
b(0) = Left(a(2), 1) & "-" & Mid(a(2), 2)
b(1) = a(0)
b(2) = a(3)
SortComp = Join(b)
End Function<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=6><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - Book2.xls</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: xl2002 XP : OS = Windows Windows 2000 </FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=6><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp <A onclick=show_popup(); href="#javascript:void(0)">(<U>A</U>)bout</A></TD><TD vAlign=center align=right><FORM name=formCb755237><INPUT onclick='window.clipboardData.setData("Text",document.formFb078704.sltNb935705.value);' type=button value="Copy Formula" name=btCb873980></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=6><TABLE border=0><TBODY><TR><FORM name=formFb078704><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value" name=sltNb935705><OPTION value="" selected>E5</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8><B>=</B></TD><TD align=left bgColor=white><INPUT size=80 name=txbFb426622></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><BR></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>A</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>D</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>E</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>1</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 9pt; VERTICAL-ALIGN: middle; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: left">Seg</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 9pt; VERTICAL-ALIGN: middle; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: left">Component</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 9pt; VERTICAL-ALIGN: middle; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: left">Type</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 9pt; VERTICAL-ALIGN: middle; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 9pt; VERTICAL-ALIGN: middle; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>2</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 9pt; VERTICAL-ALIGN: middle; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">MB</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 9pt; VERTICAL-ALIGN: middle; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">700cm 600CP D1 DLC</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 9pt; VERTICAL-ALIGN: middle; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">API</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 9pt; VERTICAL-ALIGN: middle; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 9pt; VERTICAL-ALIGN: middle; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>3</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 9pt; VERTICAL-ALIGN: middle; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">MB</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 9pt; VERTICAL-ALIGN: middle; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">500cm 300CP K2 DDPA</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 9pt; VERTICAL-ALIGN: middle; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">API</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 9pt; VERTICAL-ALIGN: middle; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 9pt; VERTICAL-ALIGN: middle; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan=6><TABLE width="100%" align=left VALIGN="TOP"><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left><U>Sheet1</U></TD><TD> </TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE><BR><FONT color=#339966 size=1>[HtmlMaker 2.41] </FONT><FONT color=#339966 size=1>To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT><BR><FONT color=red size=1>PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT></CENTER>
Hi Colo,
i still facing some problem, still can solve yet.
When i run it, there have a error. The error is
Run-time error '9':
Subscript out of range.
The error line is
b(0) = Left(a(2), 1) & "-" & Mid(a(2), 2)
i also got some question asking you,
Q1 : For the field "Type", if is not only contain API, is will contain empty or other data. It like that
PPC
P1
P2
API
A1
A2
PPC
P1
P2
API
A1
A2
So, eveytime i need to search the field "Type" contain name 'API', and go to field "Component, Seg" change the format. How to do it?
Q2: When i push a button, how do i auto replace all the original naming to the format i want at the same worksheets?
Lastly, i want thanks a lots that the effort for helping me, thanks.
Thanks & Best Regards,
Andy
Hi Andy,
I could not grasp the laypot of the worksheet, so I changed the code to the specification which can search titles in the first row. Please try this code and let me have the result.
>Q1 : For the field "Type", if is not only contain API, is will contain empty or other data.
My code judges processing by whether the cell (field Type) is blank.
>Q2 : When i push a button, how do i auto replace all the original naming to the format i want at the same worksheets?
Why don't you insert a Button from View > Toolsbars > Form, and assign the macro named UDSort2?
Option Explicit: Option Base 0
Sub UDSort2()
Dim col(0 To 2) As Long, arrTitle, ret, i As Long
Dim lngEnd As Long, buf0, buf1, buf2, tmp
'Find Title Column
arrTitle = Array("Seg", "Component", "Type")
For i = 0 To 2
ret = Application.Match(arrTitle(i), Rows(1), 0)
If IsError(ret) Then
MsgBox arrTitle(i) & " was NOT found at row1"
Exit Sub
Else
col(i) = ret
End If
Next
lngEnd = ActiveSheet.UsedRange.Rows.Count
'Get each value
buf0 = Cells(col(0)).Resize(lngEnd).Value 'Seg
buf1 = Cells(col(1)).Resize(lngEnd).Value 'Component
buf2 = Cells(col(2)).Resize(lngEnd).Value 'Type
'Change value
For i = LBound(buf0) + 1 To UBound(buf0)
If Not IsEmpty(buf2(i, 1)) Then 'If Type column is NOT empty
tmp = GetMonitor(buf1(i, 1))
If Not tmp = vbNullString Then
buf0(i, 1) = tmp
Else
If MsgBox("Seems this data has been changed already..." & vbLf & _
"Do you want to continue?", vbYesNo + vbQuestion) <> vbYes Then
Exit Sub
End If
End If
buf1(i, 1) = SortComp(buf1(i, 1))
End If
Next
'Put changed value
Cells(col(0)).Resize(lngEnd).Value = buf0 'Seg
Cells(col(1)).Resize(lngEnd).Value = buf1 'Component
Cells(col(2)).Resize(lngEnd).Value = buf2 'Type
End Sub
Function GetMonitor(ByVal strSeq As String) As String
Dim a, i As Long
a = Split(strSeq, " ")
For i = LBound(a) To UBound(a)
If a(i) Like "*CP" Then GetMonitor = "Monitor " & a(i): Exit Function
Next
GetMonitor = vbNullString
End Function
Function SortComp(ByVal strSeq As String) As String
Dim a, b(0 To 2), i As Long, tmp
a = Split(strSeq, " ")
For i = LBound(a) To UBound(a)
If a(i) Like "*cm" Then
b(1) = a(i)
tmp = tmp & i
End If
If IsNumeric(Right(a(i), 1)) And Len(a(i)) > 1 Then
b(0) = Left(a(1), 1) & "-" & Mid(a(1), 2)
tmp = tmp & i
End If
If a(i) Like "*CP" Then
tmp = tmp & i
End If
Next
For i = LBound(a) To UBound(a)
If InStr(1, i, tmp, vbTextCompare) = 0 Then b(2) = a(i)
Next
SortComp = Join(b)
End FunctionCouldn't you just use the text to columns feature in the Data menu and use a space as the delimiter.
Then move the columns around the way you want and use the & to add the columns back together into a single cell. eg A1&B1&C1
hth
Thanks for you all help, especially Colo.
Thanks & Regards,
Andy
Don’t have an account yet? Register yourself now and be a part of our community!