+ Reply to Thread
Results 1 to 2 of 2

Macro for the adding new row with the data

Hybrid View

ashwin_bist Macro for the adding new row... 10-30-2008, 04:30 AM
Shijesh Kumar Here is the macros to achieve... 10-30-2008, 05:57 AM
  1. #1
    Registered User
    Join Date
    10-21-2008
    Location
    Bangalore, India
    Posts
    35

    Macro for the adding new row with the data

    Hi All,

    Every weekend i need to send forcasting reporting to the higher management.
    Exported data will have 2 or 3 order number in 1 opportunity, but for the report i need all the order number in different row.
    I need a macro or something which can help me doing this faster.

    I am attaching the example sheet, raw data containes the export that i will get from CRm and "Required Formate" contains the formate that i need after i run the marco.

    Regards
    Ashwin
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Shijesh Kumar's Avatar
    Join Date
    05-26-2008
    Location
    Bangalore / India
    MS-Off Ver
    2000
    Posts
    717
    Here is the macros to achieve the same.

    Note...
    1. Find and replace ", " ( comma and space in Order# with "/").
    2. if sheet3 is not there then insert sheet3.

    let me know if the code is working or not

    Sub frmt()
        Dim start_at As Integer
        Dim stop_at As Integer
        Dim no_of_occ As Integer
        Dim row_counter As Integer
        
        Cells(2, 4).Activate
        
        row_counter = 2
        
        Do While ActiveCell.Value <> ""
         no_of_occurance = Len(WorksheetFunction.Substitute(ActiveCell.Value, "/", "  ")) - Len(ActiveCell.Value)
         start_at = 1
         
         For i = 0 To no_of_occurance
            
            If i = no_of_occurance Then
            stop_at = Len(ActiveCell.Value)
         Else
            stop_at = Application.WorksheetFunction.Search("/", ActiveCell.Value, start_at)
         End If
            
            Sheet3.Cells(row_counter, 1) = ActiveCell.Offset(0, -3)
            Sheet3.Cells(row_counter, 2) = ActiveCell.Offset(0, -2)
            Sheet3.Cells(row_counter, 3) = ActiveCell.Offset(0, -1)
            Sheet3.Cells(row_counter, 4) = Mid(ActiveCell.Value, start_at, stop_at - 1)
            Sheet3.Cells(row_counter, 5) = ActiveCell.Offset(0, 1)
            row_counter = row_counter + 1
            start_at = stop_at + 1
            
            
                    
            Next i
         
         
         
         ActiveCell.Offset(1).Activate
         
       Loop
        
    End Sub
    
    
    Shijesh Kumar
    Bangalore

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1