Need help in Excel Macro Solved/Closed

Question

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

Ivan-hoe · Accepted Answer

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

zeromaim · Answer

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

Ivan-hoe · Answer

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

zeromaim · Answer

Hi Ivan,

Thanks again, it's perfect.

How do I change the status of this topic to resolve? Anyone?

Anton

Jnani · Answer

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

Need help in Excel Macro

5 responses

Similar discussions

Subscribe To Our Newsletter!