1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

excel macro help

Discussion in 'Software Development' started by tpkelley, Jun 9, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. tpkelley

    tpkelley Thread Starter

    Joined:
    Oct 12, 2002
    Messages:
    66
    I am writing a macro that will clean up a lager exported from an accounting program.

    I have the following sheet:
    Now:
    A B C
    1 GL Account-8600
    2
    3 1 / 2011
    4 1121
    5 1416
    6 1416
    7 1416
    8 1121
    9 1121
    10 1121
    11
    12 2 / 2011
    13 1416
    14 1121
    15 1121
    16 1416
    I would like cell “A3” copied and pasted until they have an empty cell. In this example, it will be A3-A10 will be changed to the number “1”, and cell A13-A16 will have a number 2. (The amount of rows will vary) I will not have a number greater then 12 months. Also, the empty rows (A2, A3, A11 and A12) and the row below needs to be deleted. I will use this data to make a table in excel, ant the macro has been written for that.

    I would like the following outcome:
    A
    1 GL Account-8600
    2 1
    3 1
    4 1
    5 1
    6 1
    7 1
    8 1
    9 1
    10 1
    11 2
    12 2
    13 2
    14 2
    15 2
    16 2
     
  2. ckphilli

    ckphilli

    Joined:
    Apr 29, 2006
    Messages:
    4,384
    Could you give us what you have so far? I'm willing to help, but need to be clear on your intentions.
     
  3. tpkelley

    tpkelley Thread Starter

    Joined:
    Oct 12, 2002
    Messages:
    66
    counting the date will not work. If I have a posting month missing the table will be off by one month.

    Sub delcol()
    '
    ' delcol Macro
    ' to del col it exported excel
    '
    ' Keyboard Shortcut: Ctrl+d
    '
    Range("1:1,11:11,2:2,3:3,4:4,5:5,6:6,7:8,9:9,10:10").Select
    Range("A10").Activate
    Selection.Delete Shift:=xlUp
    Range("A1:D1").Select
    Selection.Cut Destination:=Range("B1:E1")
    Range("A:A,F:F,H:H,I:I,J:J,K:K,L:L,M:M,N:N,O:O,P:p,S:S").Select
    Range("S1").Activate
    Selection.Delete Shift:=xlToLeft
    Range("A2").Select
    Dim cl As Range
    For Each cl In Columns(2).SpecialCells(2)
    If IsDate(cl) Then
    Cells(Rows.Count, 14).End(xlUp).Offset(1).Resize(, 5) = cl.Resize(, 5).Value
    Cells(Rows.Count, 13).End(xlUp).Offset(1) = WorksheetFunction.CountIf(Range(Range("B1"), cl.Address), "/")
    End If
    Next cl
    Columns("M:R").Select
    Selection.Cut Destination:=Columns("H:M")
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Range("A1:E1").Select
    Selection.Cut Destination:=Range("I1:M1")
    Range("H3").Select
    ActiveCell.FormulaR1C1 = "Posting"
    Range("I3").Select
    ActiveCell.FormulaR1C1 = "Date"
    Range("J3").Select
    ActiveCell.FormulaR1C1 = "Amount"
    Range("K3").Select
    ActiveCell.FormulaR1C1 = "Vendor Number"
    Range("L3").Select
    ActiveCell.FormulaR1C1 = "Vendor Name"
    Range("M3").Select
    ActiveCell.FormulaR1C1 = "Invoice Number"
    Columns("A:G").Select
    Selection.ClearContents
    Range("H3").Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    "Sheet1!R3C8:R234C13").CreatePivotTable TableDestination:="", TableName:= _
    "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Posting")
    .Orientation = xlRowField
    .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
    .Orientation = xlRowField
    .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Vendor Name")
    .Orientation = xlRowField
    .Position = 3
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Invoice Number")
    .Orientation = xlRowField
    .Position = 4
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Amount")
    .Orientation = xlRowField
    .Position = 5
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
    ActiveSheet.PivotTables("PivotTable1").Format xlReport5
    Range("E3").Select
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Amount"), "Sum of Amount", xlSum
    Range("F3").Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Amount")
    .NumberFormat = "#,##0.00"
    End With
    Application.CommandBars("PivotTable").Visible = False
    ActiveWorkbook.ShowPivotTableFieldList = False
    End Sub
     
  4. ckphilli

    ckphilli

    Joined:
    Apr 29, 2006
    Messages:
    4,384
    Really don't think I can be much help after looking at it. I'll pm someone that is more advance with macros than I.
     
  5. tpkelley

    tpkelley Thread Starter

    Joined:
    Oct 12, 2002
    Messages:
    66
    If you need a copy of the file, I can send it to you.
     
  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    Can you post a copy of the file here? Just click the "Go Advanced" button at the bottom of the quick reply window and use the manage attachments button.

    Rollin
     
  7. tpkelley

    tpkelley Thread Starter

    Joined:
    Oct 12, 2002
    Messages:
    66
    i am having a problem with this part. it is counting the date. i need it to copy the posting date next to the invoice information. you can run the macro on this file.

    Dim cl As Range
    For Each cl In Columns(2).SpecialCells(2)
    If IsDate(cl) Then
    Cells(Rows.Count, 14).End(xlUp).Offset(1).Resize(, 5) = cl.Resize(, 5).Value
    Cells(Rows.Count, 13).End(xlUp).Offset(1) = WorksheetFunction.CountIf(Range(Range("B1"), cl.Address), "/")
    End If
    Next cl
     

    Attached Files:

  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1056391