Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

EXCEL Macro

Posted on 2014-04-01
7
328 Views
Last Modified: 2014-04-02
Hello,
Can you please help.
I have a macro that connects to my SQL database and have a select statement  to import some data into the excel sheet (Columns "M" to "BF").
Number of rows are different.

Is there any way (macro code) that can  sort the data as in my sample sheet.
Example
A25 = M2,  B25 = N2, C25 =O2, C26 = P2, C27 = Q2.....
A33 = M3, B33 = N3, C33 =O3, C34 = P3, C35 = Q3.....
A41 = M4, B41 = N4, C41 =O4,C42 = P4, C43 = Q4.....

Your help is appreciated.
thanks
sample.xls
0
Comment
Question by:W.E.B
  • 3
  • 3
7 Comments
 
LVL 81

Assisted Solution

by:byundt
byundt earned 200 total points
ID: 39971344
Here is a macro that will rewrite your data. Install it in a regular module sheet. The macro is specific to your data layout.
Sub DataRewriter()
Dim rg As Range
Dim i As Long, j As Long, nRows As Long
Dim v As Variant, vData As Variant
Application.ScreenUpdating = False
Set rg = Range("M2").CurrentRegion
If rg.Cells(1, 1).Address <> "$M$2" Then Set rg = Range(Range("M2"), rg.Cells(rg.Rows.Count, rg.Columns.Count))
vData = rg.Value
nRows = rg.Rows.Count
'M,N,O:T,U:Z,(AA:AB,AE,AG,AI,AK,AM),(AC:AD,AF,AH,AJ,AL,AN),AO:AR,AS:AV
For i = 1 To nRows
    ReDim v(1 To 7, 1 To 8)
    v(1, 1) = vData(i, 1)
    v(1, 2) = vData(i, 2)
    For j = 1 To 6
        v(j, 3) = vData(i, 2 + j)
        v(j, 4) = vData(i, 8 + j)
    Next
    v(1, 5) = vData(i, 15)
    v(2, 5) = vData(i, 16)
    v(1, 6) = vData(i, 17)
    v(2, 6) = vData(i, 18)
    For j = 1 To 5
        v(2 + j, 5) = vData(i, 17 + j * 2)
        v(2 + j, 6) = vData(i, 18 + j * 2)
    Next
    For j = 1 To 4
        v(j, 7) = vData(i, 28 + j)
        v(j, 8) = vData(i, 32 + j)
    Next
    Range("A25").Offset((i - 1) * 8, 0).Resize(7, 8).Value = v
Next
End Sub

Open in new window

sampleQ28402837.xls
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39971454
Hi,

pls try this

Sub macro()
Dim FirstCell As Range
Columns("H:H").NumberFormat = """$""#,##0.00_);(""$""#,##0.00)"
For Each c In Range(Range("M2"), Range("M" & Cells.Rows.Count).End(xlUp))
    ActualRow = c.Row
    Set FirstCell = Range("A" & 25 + ((ActualRow - 2) * 8))
    Range(FirstCell, _
            FirstCell.Offset(6, 7)).Interior.Color = c.Interior.Color
    FirstCell = c
    FirstCell.Offset(0, 1) = c.Offset(0, 1)
    FirstCell.Offset(0, 2).Resize(6, 1) = WorksheetFunction.Transpose(c.Offset(0, 2).Resize(1, 6))
    FirstCell.Offset(0, 3).Resize(6, 1) = WorksheetFunction.Transpose(c.Offset(0, 8).Resize(1, 6))
    FirstCell.Offset(0, 4).Resize(2, 1) = WorksheetFunction.Transpose(c.Offset(0, 14).Resize(1, 2))
    FirstCell.Offset(2, 4) = c.Offset(0, 18)
    FirstCell.Offset(3, 4) = c.Offset(0, 20)
    FirstCell.Offset(4, 4) = c.Offset(0, 22)
    FirstCell.Offset(5, 4) = c.Offset(0, 24)
    FirstCell.Offset(6, 4) = c.Offset(0, 26)
    FirstCell.Offset(0, 5).Resize(2, 1) = WorksheetFunction.Transpose(c.Offset(0, 16).Resize(1, 2))
    FirstCell.Offset(2, 5) = c.Offset(0, 19)
    FirstCell.Offset(3, 5) = c.Offset(0, 21)
    FirstCell.Offset(4, 5) = c.Offset(0, 23)
    FirstCell.Offset(5, 5) = c.Offset(0, 25)
    FirstCell.Offset(5, 5).NumberFormat = "dd/mm/yyyy"
    FirstCell.Offset(6, 5) = c.Offset(0, 27)
    FirstCell.Offset(0, 6).Resize(4, 1) = WorksheetFunction.Transpose(c.Offset(0, 28).Resize(1, 4))
    FirstCell.Offset(0, 7).Resize(4, 1) = WorksheetFunction.Transpose(c.Offset(0, 32).Resize(1, 4))
    
    
Next
End Sub

Open in new window

Regards
Copy-of-sample.xlsm
0
 

Author Comment

by:W.E.B
ID: 39971989
Hello,
thank you for the help.

Rgonzo1971, I get error when trying the code.
Variable not defined. (must be variant or object)
For Each c In Range(Range("M2"), .....

byundt, I'm testing your code now.

Thank you
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39972010
Hi,

pls try

Sub macro()
Dim FirstCell As Range, c As Range
Columns("H:H").NumberFormat = """$""#,##0.00_);(""$""#,##0.00)"
For Each c In Range(Range("M2"), Range("M" & Cells.Rows.Count).End(xlUp))
    Set FirstCell = Range("A" & 25 + ((c.Row - 2) * 8))
    Range(FirstCell, _
            FirstCell.Offset(6, 7)).Interior.Color = c.Interior.Color
    FirstCell = c
    FirstCell.Offset(0, 1) = c.Offset(0, 1)
    FirstCell.Offset(0, 2).Resize(6, 1) = WorksheetFunction.Transpose(c.Offset(0, 2).Resize(1, 6))
    FirstCell.Offset(0, 3).Resize(6, 1) = WorksheetFunction.Transpose(c.Offset(0, 8).Resize(1, 6))
    FirstCell.Offset(0, 4).Resize(2, 1) = WorksheetFunction.Transpose(c.Offset(0, 14).Resize(1, 2))
    FirstCell.Offset(2, 4) = c.Offset(0, 18)
    FirstCell.Offset(3, 4) = c.Offset(0, 20)
    FirstCell.Offset(4, 4) = c.Offset(0, 22)
    FirstCell.Offset(5, 4) = c.Offset(0, 24)
    FirstCell.Offset(6, 4) = c.Offset(0, 26)
    FirstCell.Offset(0, 5).Resize(2, 1) = WorksheetFunction.Transpose(c.Offset(0, 16).Resize(1, 2))
    FirstCell.Offset(2, 5) = c.Offset(0, 19)
    FirstCell.Offset(3, 5) = c.Offset(0, 21)
    FirstCell.Offset(4, 5) = c.Offset(0, 23)
    FirstCell.Offset(5, 5) = c.Offset(0, 25)
    FirstCell.Offset(5, 5).NumberFormat = "dd/mm/yyyy"
    FirstCell.Offset(6, 5) = c.Offset(0, 27)
    FirstCell.Offset(0, 6).Resize(4, 1) = WorksheetFunction.Transpose(c.Offset(0, 28).Resize(1, 4))
    FirstCell.Offset(0, 7).Resize(4, 1) = WorksheetFunction.Transpose(c.Offset(0, 32).Resize(1, 4))
    
    
Next
End Sub

Open in new window

Regards
0
 

Author Comment

by:W.E.B
ID: 39972188
Thank you guys, both working as expected.

Rgonzo1971,
when running your code, there are no grid lines.
how can I show the grid lines ?

I tried using , with no luck
ActiveWindow.DisplayGridlines = True

thanks
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 300 total points
ID: 39972234
Hi,

If you want the colors used in the list and a grid use this

Sub macro()
Dim FirstCell As Range
Rows("25:" & Cells.Rows.Count).EntireRow.Delete
Columns("H:H").NumberFormat = """$""#,##0.00_);(""$""#,##0.00)"
For Each c In Range(Range("M2"), Range("M" & Cells.Rows.Count).End(xlUp))
    ActualRow = c.Row
    Set FirstCell = Range("A" & 25 + ((ActualRow - 2) * 8))
    With Range(FirstCell, FirstCell.Offset(6, 7))
        .Interior.Color = c.Interior.Color
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
    End With

    FirstCell = c
    FirstCell.Offset(0, 1) = c.Offset(0, 1)
    FirstCell.Offset(0, 2).Resize(6, 1) = WorksheetFunction.Transpose(c.Offset(0, 2).Resize(1, 6))
    FirstCell.Offset(0, 3).Resize(6, 1) = WorksheetFunction.Transpose(c.Offset(0, 8).Resize(1, 6))
    FirstCell.Offset(0, 4).Resize(2, 1) = WorksheetFunction.Transpose(c.Offset(0, 14).Resize(1, 2))
    FirstCell.Offset(2, 4) = c.Offset(0, 18)
    FirstCell.Offset(3, 4) = c.Offset(0, 20)
    FirstCell.Offset(4, 4) = c.Offset(0, 22)
    FirstCell.Offset(5, 4) = c.Offset(0, 24)
    FirstCell.Offset(6, 4) = c.Offset(0, 26)
    FirstCell.Offset(0, 5).Resize(2, 1) = WorksheetFunction.Transpose(c.Offset(0, 16).Resize(1, 2))
    FirstCell.Offset(2, 5) = c.Offset(0, 19)
    FirstCell.Offset(3, 5) = c.Offset(0, 21)
    FirstCell.Offset(4, 5) = c.Offset(0, 23)
    FirstCell.Offset(5, 5) = c.Offset(0, 25)
    FirstCell.Offset(5, 5).NumberFormat = "dd/mm/yyyy"
    FirstCell.Offset(6, 5) = c.Offset(0, 27)
    FirstCell.Offset(0, 6).Resize(4, 1) = WorksheetFunction.Transpose(c.Offset(0, 28).Resize(1, 4))
    FirstCell.Offset(0, 7).Resize(4, 1) = WorksheetFunction.Transpose(c.Offset(0, 32).Resize(1, 4))
Next
End Sub

Open in new window

Or if you want No color at all use this
Sub macro()
Dim FirstCell As Range
Rows("25:" & Cells.Rows.Count).EntireRow.Delete
Columns("H:H").NumberFormat = """$""#,##0.00_);(""$""#,##0.00)"
For Each c In Range(Range("M2"), Range("M" & Cells.Rows.Count).End(xlUp))
    ActualRow = c.Row
    Set FirstCell = Range("A" & 25 + ((ActualRow - 2) * 8))
    FirstCell = c
    FirstCell.Offset(0, 1) = c.Offset(0, 1)
    FirstCell.Offset(0, 2).Resize(6, 1) = WorksheetFunction.Transpose(c.Offset(0, 2).Resize(1, 6))
    FirstCell.Offset(0, 3).Resize(6, 1) = WorksheetFunction.Transpose(c.Offset(0, 8).Resize(1, 6))
    FirstCell.Offset(0, 4).Resize(2, 1) = WorksheetFunction.Transpose(c.Offset(0, 14).Resize(1, 2))
    FirstCell.Offset(2, 4) = c.Offset(0, 18)
    FirstCell.Offset(3, 4) = c.Offset(0, 20)
    FirstCell.Offset(4, 4) = c.Offset(0, 22)
    FirstCell.Offset(5, 4) = c.Offset(0, 24)
    FirstCell.Offset(6, 4) = c.Offset(0, 26)
    FirstCell.Offset(0, 5).Resize(2, 1) = WorksheetFunction.Transpose(c.Offset(0, 16).Resize(1, 2))
    FirstCell.Offset(2, 5) = c.Offset(0, 19)
    FirstCell.Offset(3, 5) = c.Offset(0, 21)
    FirstCell.Offset(4, 5) = c.Offset(0, 23)
    FirstCell.Offset(5, 5) = c.Offset(0, 25)
    FirstCell.Offset(5, 5).NumberFormat = "dd/mm/yyyy"
    FirstCell.Offset(6, 5) = c.Offset(0, 27)
    FirstCell.Offset(0, 6).Resize(4, 1) = WorksheetFunction.Transpose(c.Offset(0, 28).Resize(1, 4))
    FirstCell.Offset(0, 7).Resize(4, 1) = WorksheetFunction.Transpose(c.Offset(0, 32).Resize(1, 4))
Next
End Sub

Open in new window

Regards
0
 

Author Closing Comment

by:W.E.B
ID: 39972254
Thank you very much guys.
Much appreciated.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question