Posts by yudisulistiyo

Re: merging row based on unique value in colum A

Quote from jindon;663705

Then try change

Code

With Sheets("result").Cells(1).Resize(n, UBound(a, 2)) 
         '.Columns("r").NumberFormat = "@"
        .Value = a 
        .EntireColumn.AutoFit 
    End With

to

Code

With Sheets("result").Cells(1)
        For i = 1 To n
            For ii = 1 To UBound(a, 2)
                .Cells(i, ii).Value = a(i, ii)
            Next
        Next
        .CurrentRegion.EntireColumn.AutoFit
    End With

i didnt understand why in few days ago when i run this code

With Sheets("result").Cells(1).Resize(n, UBound(a, 2)) 
         '.Columns("r").NumberFormat = "@"
        .Value = a 
        .EntireColumn.AutoFit 
    End With

i always got error message.
but now both of code above its work perfectly,
thanks jindon for ur help its very nice code and very helpful for me thanks jindon youre best...

Re: merging row based on unique value in colum A

jindon when i hitted debug, line highligt this syntax code .Value = a

Re: merging row based on unique value in colum A

Quote from jindon;663691

Working.............

jindon why in my computer when i try ur file show error message runtime error :"1004" whats wrong in my excel programs?

Re: merging row based on unique value in colum A

jindon i tried to change my sheet to sheet11 it doesnt work..i got error message 400 jindon

Re: merging row based on unique value in colum A

Ok jindon i will try.. how if i have 44000 rows data jindon it will work??? here for my actually file in large size jindon http://www.mediafire.com/view/?cxpegpci6fkmj61

Re: merging row based on unique value in colum A

OK jindon i put in "Result" sheet for result. oh ya jindon i get your code from other thread but when applied ur code in my file that have more than 44k rows the code resulterror message 400.. here my file jindon. because my file have large size i cant upload file above 1MB here, i upload it in other place here for that link jindon http://www.mediafire.com/view/?cxpegpci6fkmj61 thanks jindon for ur reply

im newbie for vba programming, i have more than 44K rows of data and have 3 column A,B,C. and i want to merge that based on column A as key. i used code from this forum in this link http://www.ozgrid.com/forum/showthread.php?t=156024, my problem is code always return error 400 when i executed this

Sub test()
    Dim a, i As Long, ii As Long, n As Long, x As Long, flg As Boolean
    a = Sheets("sheet1").Range("a1").CurrentRegion.Value
    n = 1
    With CreateObject("Scripting.Dictionary")
        For i = 2 To UBound(a, 1)




            If Not .exists(a(i, 1)) Then
                n = n + 1
                .Item(a(i, 1)) = n
                flg = True
            End If
            x = .Item(a(i, 1))
            If flg Then
                For ii = 1 To UBound(a, 2)
                    a(x, ii) = a(i, ii)
                Next
            Else
                a(x, 2) = a(x, 2) & ";;" & a(i, 2)
                a(x, 3) = a(x, 3) & ";;" & a(i, 3)
            End If
            flg = False
        Next
    End With
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("result").Delete
    On Error GoTo 0
    Sheets.Add(after:=Sheets("sheet1")).Name = "Result"
    With Sheets("result").Cells(1).Resize(n, UBound(a, 2))
        '.Columns("r").NumberFormat = "@"
        .Value = a
        .EntireColumn.AutoFit
    End With
End Sub

basically my file contain data like here

A B C
12 a b
12 c d
13 a ee
.
.
.

my goal file like here
A B C
12 a,c b,d
13 a ee
.
.
.
more details for my file in attachment
can give me solution? thanks so much

Re: Transpose comma delimitted cell to multiple rows and copy adjacent cell down

Quote from Stanley D. Grom;591754

davet,

Welcome to the Ozgrid Forum.

Detach/Open workbook ReorgData Sp - davet - OZ161519 - SDG10.xlsm and run macro ReorgData.

If you want to use the macro on another workbook:

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).
1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code

Option Explicit
Sub ReorgData()
' Stanley D. Grom, 01/17/2012
' [URL]http://www.ozgrid.com/forum/showthread.php?t=161519[/URL]
Dim w1 As Worksheet, wR As Worksheet
Dim lr As Long, r As Long, Sp, n As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
w1.UsedRange.Copy wR.Range("A1")
lr = wR.Cells(Rows.Count, 1).End(xlUp).Row
For r = lr To 1 Step -1
  If InStr(wR.Cells(r, 2), ",") > 0 Then
    Sp = Split(wR.Cells(r, 2), ",")
    wR.Rows(r + 1).Resize(UBound(Sp)).Insert
    wR.Cells(r, 2).Resize(UBound(Sp) + 1) = Application.Transpose(Sp)
    wR.Cells(r, 1).Resize(UBound(Sp) + 1) = wR.Cells(r, 1)
  End If
Next r
wR.UsedRange.Columns.AutoFit
wR.Activate
Application.ScreenUpdating = True
End Sub

Display More

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.

Display More

thanks Stanley D. Grom i have try ur code its very great and helpfull for saving my time now :thumbcoo:

Re: Regular Expressions in VBA

ok sorry all i dont know for the future i ll create my thread in ozgrid thanks before

Re: Regular Expressions in VBA

Sub test2() Dim txt As String txt = "6-methyl-5-hepten-2-one" With CreateObject("VBScript.RegExp") .Pattern = ".*((\d+-\D+){2})(?!.)" If .test(txt) Then MsgBox .Replace(txt, "$1") End If End With End Sub code is awesome jindon , i want to ask to you how if txt applicated in cell how if i want to find that txt in cell and replace it with your code. can you help me to give me example with your code to find replace string in cell thanks jindon