Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: anyone like to try and condense this code.....

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here is the first part of this macro I wrote at work, take a look, if you can help me condense the code it would cool, thanks. All it is doing is extracting isolated values from columns B,C,D of a monthly spreadsheet, then E,F,G, etc. in groups of three. But, I need to do this for every month and get all the data together in a nice little table. Anyway, you will see what I mean from the code - it works fine but just needs to be more efficient and flexible if possible. Thanks...


    Public Sub UKGrowth()

    'opens files for jan, feb, mar in turn, extracts the require data, then closes file.

    B = Array("jan", "feb", "mar")

    For Month = 0 To 2 Step 1
    SourceBook = "dataForMonth" & B(Month) & ".xls"

    Workbooks.Open Filename:="C:dataForMonth" &
    B(Month) & ".xls"

    Set Source = Application.Workbooks(SourceBook).Worksheets("UK Growth")
    For i = 0 To 32 Step 1

    Result.Cells(2, 1).Offset(Month * 45 + i, 0).Value = Source.[B3].Offset(-1,
    3 * i).Value

    Result.Cells(2, 2).Offset(Month * 45 + i, 0).Value = Source.Cells(4,
    4).Offset(0, 3 * i).Value
    Result.Cells(2, 3).Offset(Month * 45 + i, 0).Value = Source.Cells(24,
    2).Offset(0, 3 * i).Value
    Result.Cells(2, 4).Offset(Month * 45 + i, 0).Value = Source.Cells(24,
    3).Offset(0, 3 * i).Value
    Result.Cells(2, 5).Offset(Month * 45 + i, 0).Value = Source.Cells(72,
    2).Offset(0, 3 * i).Value
    Result.Cells(2, 6).Offset(Month * 45 + i, 0).Value = Source.Cells(89,
    2).Offset(0, 3 * i).Value
    Result.Cells(2, 7).Offset(Month * 45 + i, 0).Value = Source.Cells(103,
    2).Offset(0, 3 * i).Value
    Result.Cells(2, .Offset(Month * 45 + i, 0).Value = Source.Cells(114,
    2).Offset(0, 3 * i).Value
    Result.Cells(2, 9).Offset(Month * 45 + i, 0).Value = Source.Cells(196,
    4).Offset(0, 3 * i).Value
    Result.Cells(2, 10).Offset(Month * 45 + i, 0).Value = Source.Cells(220,
    4).Offset(0, 3 * i).Value
    Result.Cells(2, 11).Offset(Month * 45 + i, 0).Value = Source.Cells(44,
    4).Offset(0, 3 * i).Value
    Next i

    Workbooks(SourceBook).Close False

    Next Month

    End Sub.

    Any comments appreciated. Thanks.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The big chunk in the code above looks frightening, but all I am doing is this:

    Taking the values of D4, B24, C24 etc. and putting them in Cells(2,1) to Cells(2,10) of another workbook, which I refer to as results.

    Then put G4, E24, F24 etc. and put them into Cells(3,1) to Cells(3,10)

    So what I need is a slick way, ideally a one liner to put isolated cells into a range. something like this, but I am not sure of how to code it exactly

    Range("A2:K2").value = Range("D4", "B24", "C24", ......).value

    and then put in an offsets for the rest.

    Hope that makes some sense.
    Any help appreciated!

  3. #3
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I want to know what function the smiley face calls.


    ~Anne Troy

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    So, has someone any ideas about this question??

    Thanks

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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