Consulting

Results 1 to 2 of 2

Thread: VBA

  1. #1

    VBA

    Hello

    I have quick question to ask i am getting stuck trying to do this in vba.

    I have Column A that has many cell that different amount. I am trying duplicated this in vba with loop

    Thanks

    Loan Amount Loan Amount
    1 12000 1 9999.99
    2 50000 1 2000.01
    3 35000 2 9999.99
    4 8500 2 9999.99
    2 9999.99
    2 9999.99
    2 9999.99
    2 0.05
    3 9999.99
    3 9999.99
    3 9999.99
    3 5000.03
    4 8500

  2. #2
    This problem statement is a bit vague. But, I'll take a stab anyway.
    This code will create a new table from an autofiltered data field.

    Private Sub cmdFilerCriteriaToNewXLTable_Click()
    Dim Excel As Object
    Dim excelSheet As Object
    Dim excelSheet2 As Object
    Dim Range As Object
    
    
        Set Excel = GetObject(, "Excel.Application")
        Set excelSheet = Excel.ActiveWorkbook.Sheets("YOUR_TABLE")
        
        Excel.Visible = True
        
    
    
        'from an autofiltered column on this table, can be made "Sheet1", etc.
        
        Excel.ActiveWorkbook.Sheets("YOUR_TABLE").Select
    
    
        strFILT = YOUR_TARGET_FILTER 'like 9999.99
        
        'be on sheet "YOUR_TABLE"
        'can delete this if autofilter is already on
        Excel.application.Range("A1:Z1").Select
        'Excel.application.Range("YOUR_RANGE_ONECOLUMN").Select
        '============
        'added this to set autofilter field val automaically
        strRange = "YOUR_RANGE_ONECOLUMN"
        strFC = Excel.application.Range(strRange).Column
        
        'SET FIELD TO AUTOFILTER COLUMN (EXPLICIT=10) OR NO DATA RETURNED WITH NO ERROR
        'if problem make field explicit to column loc rather than strFC var
        'Excel.application.Selection.AutoFilter Field:=strFC, Criteria1:=strFILT
        
        Excel.ActiveWorkbook.Sheets("Table_Complete").Select
        Excel.application.Selection.AutoFilter Field:=10, Criteria1:=strFILT
        
        'GETS ALL CELLS WITH DATA
        excelSheet.Range("A1", excelSheet.Range("A1").End(xlToRight).End(xlDown)).Cells(1, 1).Select
        
        With Excel.application.Selection
            .HorizontalAlignment = xlLeft
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .IndentLevel = 0
            .ShrinkToFit = False
            .MergeCells = False
            
        End With
       
        'NAME RANGE
        STRPLANTFILTER = Replace(strFILT, " ", "_") 'YOU MAY HAVE TO
        'PREFIX YOUR CRITERIA TO AN ALLOWABLE XL TABLE NAME
        
        excelSheet.Range("A1", excelSheet.Range("A1").End(xlToRight).End(xlDown)).Select
        
        'COPIES THE AUTOFILTERED SHEET DATA
        Excel.application.Selection.Copy
        
       'ADD SHEET TO MAKE NEW TABLE FROM THE ABOVE COPY
        Excel.Sheets.Add
        
        'NAME SHEET - GRAB THE FIRST "NEW"
        Excel.Sheets(i + 1).Name = STRPLANTFILTER
        
        'SET NEW SHEET
        Set excelSheet2 = Excel.ActiveWorkbook.Sheets(STRPLANTFILTER)
        
        'SELECT NEW SHEET
        Excel.application.Sheets(STRPLANTFILTER).Select
        
        'SELECT THE FIRST CELL FOR PASTE
        Excel.ActiveWorkbook.Sheets(STRPLANTFILTER).Range("A1").Select
        
        'PASTE INTO CURRENT SHEET
        Excel.ActiveSheet.Paste
        
        'THAT MAKES A NEW TABLE FROM YOUR TARGET AUTOFILTERED DATA
    End Sub

Posting Permissions

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