Search : in
By :

Need help in Excel Macro

Last answer on Mar 21, 2009 3:38:48 am GMT zeromaim, on Jul 29, 2008 3:46:06 pm BST 
 Report this message to moderators

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

Configuration: Windows XP
Internet Explorer 7.0

Best answers for « Need help in Excel Macro » in :
Excel – Macro to detect and hide blank rows Show Excel – Macro to detect and hide blank rows Issue Solution Note Issue I want a macro to unhide about 20 blank rows copy values into the top row then hide the remaining rows (some cells have fill though) then the next time it will...
Excel Macro Help Please (Solved) Show
Excel Macro for deleting rows IF cells not... Show
Excel – Macro for copy & paste selected range ShowExcel – Macro for copy & paste selected range Issue Solution Note Issue I have a summary of data by month in one sheet and the raw data in another sheet. Instead of creating multiple worksheets for my raw data for each month, I want...
Avoid duplicates in Excel ShowAvoid duplicates in Excel In order to avoid duplication when encoding in a column from an excel sheet: take the conditional format on the first cell under the headings (eg A2) choose the following formula:...
Ask your question Reply

1

Ivan-hoe, on Aug 5, 2008 11:32:52 pm BST
  • +3

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

   
Reply to Ivan-hoe

2

zeromaim, on Aug 6, 2008 1:42:53 pm BST

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

   
Reply to zeromaim

3

zeromaim, on Aug 6, 2008 1:46:51 pm BST

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

   
Reply to zeromaim

4

Ivan-hoe, on Aug 7, 2008 9:23:21 am BST
  • +1

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

   
Reply to Ivan-hoe

5

zeromaim, on Aug 8, 2008 12:29:16 pm BST

Hi Ivan,

Thanks again, it's perfect.

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

Anton

   
Reply to zeromaim

7

 Jnani, on Mar 21, 2009 3:38:48 am GMT

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

   
Reply to Jnani
Ask your question Reply
They need your help