Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Need help in Excel Macro [Solved/Closed]

Report
Posts
10
Registration date
Monday July 28, 2008
Status
Member
Last seen
August 8, 2008
 -
 Jnani -
Hello Ivan,

Ive' been reading a lot of your answers and i'm quite facinated of your skill.
I need to create a macro for one of my project and it involves tranferring data (row) to specific range in another work sheet. I tried to produce a diagram but I can't seem to use it, it should help explaining my query. Anyways, i'll try it in words :)

In Sheet 1, I have a table (4 columns by 13 rows), say F11:I23. G11:G23 (2nd column) contains ID (A,B,C; randomized). Data in F11, H11, and I11 are all associated with G11.

In Sheet 2, I have another table (3 columns by 6 rows), say K11:M16. K11:M11 and K12:M12 will act as a boundary for ID "A". K13:M13 and K14:M14 will act as a boundary for ID "B". K15:M15 and K16:M16 will act as a boundary for ID "C".

Kindly help me create a macro that will identify row with ID "A" from sheet 1, copy it and insert it in sheet 2 between K11:M11 and K12:M12, pulling K12:M16 down? Same for ID "B", it will be inserted inbetween K13:M13 and K14:M14. It will therefore expand the original Sheet 2 K11:M16 to K11:M29 assuming all rows in Sheet 1 F11:I23 is not empty and has one of the IDs.

Like I said, it would've been easier if I use figures in explaining, please bear with me.

Thanks for any help you can extend.

Zero

5 replies

Answer 1 / 5
Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
 109
Hello Zero,
in the macro below, the loop For Each Mycell ... Next Mycell "reads" the contents of each cell in the given range (G11:G23) of the first table.
Whatever the ID is, the same operations are completed : insert a row and copy out data from the first table into the second one ; this is done in the bundle With Marker .... End With .
Only the initial cell changes. Thus the idea is to start the copy from a cell (named Marker) that depends on the ID ; this is done with Select Case ... End Select
Ivan 
Sub Zeromaim()
    
Dim MyCell As Range, Marker As Range
Dim LowerBoundaryForA As Range, LowerBoundaryForB As Range, LowerBoundaryForC As Range
    With Sheets("Sheet2")
        Set LowerBoundaryForA = .Range("K12")
        Set LowerBoundaryForB = .Range("K14")
        Set LowerBoundaryForC = .Range("K16")
    End With
       
    Sheets("Sheet1").Activate
    For Each MyCell In Range("G11:G23")
        
        Select Case UCase(MyCell.Value)
            Case Is = "A"
                Set Marker = LowerBoundaryForA
            Case Is = "B"
                Set Marker = LowerBoundaryForB
            Case Is = "C"
                Set Marker = LowerBoundaryForC
        End Select
        
        With Marker
            .EntireRow.Insert
            .Offset(-1, 0).Value = MyCell.Offset(0, -1).Value
            .Offset(-1, 1).Value = MyCell.Offset(0, 1).Value
            .Offset(-1, 2).Value = MyCell.Offset(0, 2).Value
        End With
        
    Next MyCell
End Sub
4
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Report
Posts
10
Registration date
Monday July 28, 2008
Status
Member
Last seen
August 8, 2008
Hi Ivan,

Thanks again for the reply, the code was very helpful. I have some follow up question, kindly refer to the post "Need help in Excel Macro" posted by zeromaim.

Kindly insert a code that if the ID is anything other than A, B, and C, the program will just skip to evaluate the next row (no inserting). If column G11:G23 reads as A, B, C, D, E, A, B, C, etc..., the program should not copy-insert the row associated to ID D, E, and other IDs.

Thanks again and hope to hear from you soon.

Anton
Answer 2 / 5
Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
 109 
Sub Zeromaim()
    
Dim MyCell As Range, Marker As Range
Dim LowerBoundaryForA As Range, LowerBoundaryForB As Range, LowerBoundaryForC As Range
    With Sheets("Sheet2")
        Set LowerBoundaryForA = .Range("K12")
        Set LowerBoundaryForB = .Range("K14")
        Set LowerBoundaryForC = .Range("K16")
    End With
       
    Sheets("Sheet1").Activate
    For Each MyCell In Range("G11:G23")
        Stop
        Select Case UCase(MyCell.Value)
            Case Is = "A"
                Set Marker = LowerBoundaryForA
            Case Is = "B"
                Set Marker = LowerBoundaryForB
            Case Is = "C"
                Set Marker = LowerBoundaryForC
            Case Else
                Set Marker = Nothing
        End Select
        
        If Not Marker Is Nothing Then
            With Marker
                .EntireRow.Insert
                .Offset(-1, 0).Value = MyCell.Offset(0, -1).Value
                .Offset(-1, 1).Value = MyCell.Offset(0, 1).Value
                .Offset(-1, 2).Value = MyCell.Offset(0, 2).Value
            End With
        End If
        
    Next MyCell
End Sub
Answer 3 / 5

Hi All,

I need to extract mysql database to my spreadsheet.The requirement goes like this ..I have a database table in mysql which I need to extract to my spreadsheet.It should happen while I trigger the Button in the spreadsheet.And It should be compatible to all the cell operations. Thanks in advance for the valuable suggestions which I may get from you all guys.

Thanks and regards
Jnani
Answer 4 / 5
Posts
10
Registration date
Monday July 28, 2008
Status
Member
Last seen
August 8, 2008
Thanks Ivan,

Follow up question :)

Kindly insert a code that if the ID is anything other than A, B, and C, the program will just skip to evaluate the next row (no inserting). If column G11:G23 reads as A, B, C, D, E, A, B, C, etc..., the program should not copy-insert the row associated to ID D, E, and other IDs.

Thanks again and hope to hear from you soon.

Anton
Answer 5 / 5
Posts
10
Registration date
Monday July 28, 2008
Status
Member
Last seen
August 8, 2008
Hi Ivan,

Thanks again, it's perfect.

How do I change the status of this topic to resolve? Anyone?

Anton
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!