Consulting

Results 1 to 11 of 11

Thread: VBA code for selecting data from statement to output sheet

  1. #1
    VBAX Regular
    Joined
    Dec 2008
    Posts
    7
    Location

    VBA code for selecting data from statement to output sheet

    Hi,

    i am trying to copy data from Input sheet and paste it in Output sheet in the format given. Please help with the relevant VBA codes to get the data displayed in Output Sheet. (File attached)

    Regards.
    Attached Files Attached Files

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,111
    Location
    Use Power Query

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Interest of EMI", type number}, {"Interest & charges", type number}, {"Repayment", Int64.Type}}),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"),
        #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Int64.Type}}),
        #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Value] <> 0))
    in
        #"Filtered Rows"
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Regular
    Joined
    Dec 2008
    Posts
    7
    Location
    Thanks for such a prompt reply and taking effort to write down the query. I am not so conversant with Power Query. Is it possible for code (module) which can be used in Excel in VBA?
    Thanks

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,111
    Location
    I can do that for you, but Power Query is worth learning, MS are investing far more in it than they ever will again in VBA.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Expert
    Joined
    Aug 2004
    Posts
    744
    Location
    Option Explicit
    Sub Blah()
        Dim LastRow As Long
        Dim lRow As Long
        Dim lCol As Long
        Dim lRowCount As Long
        Dim WS As Worksheet
        Dim WSOut As Worksheet
        
        Set WS = ThisWorkbook.Worksheets("Input")
        Set WSOut = ThisWorkbook.Worksheets("Output")
        LastRow = FindLastRow(WS, "A")
        lRowCount = 2
        With WSOut
            For lRow = 2 To LastRow
                For lCol = 2 To 4
                    Select Case WS.Cells(lRow, lCol)
                        Case Is > 0
                            .Cells(lRowCount, "A") = WS.Cells(lRow, "A")
                            .Cells(lRowCount, "B") = WS.Cells(1, lCol)
                            .Cells(lRowCount, "C") = WS.Cells(lRow, lCol)
                            lRowCount = lRowCount + 1
                    End Select
                Next lCol
            Next lRow
        End With
        
        Set WS = Nothing
        Set WSOut = Nothing
    End Sub
    Function FindLastRow(ByVal WS As Worksheet, ColumnLetter As String) As Long
        FindLastRow = WS.Range(ColumnLetter & Rows.Count).End(xlUp).Row
    End Function

  6. #6
    VBAX Regular
    Joined
    Dec 2008
    Posts
    7
    Location
    Thanks a lot. It worked like a charm.

  7. #7
    VBAX Regular
    Joined
    Dec 2008
    Posts
    7
    Location
    Thanks. I will make it a point to learn Power Query, seems to be the way of future.

  8. #8
    VBAX Regular
    Joined
    Dec 2008
    Posts
    7
    Location
    Just a query. Is it possible to paste the values of "Repayment" in Column D, instead of Column C (currently)?

  9. #9
    VBAX Regular
    Joined
    Dec 2008
    Posts
    7
    Location
    Hi, is it possible to paste the "values" of "Repayment" in Column D, instead of Column C (currently)?

  10. #10
    VBAX Expert
    Joined
    Aug 2004
    Posts
    744
    Location
    Option Explicit
    Sub Blah()
        Dim LastRow As Long
        Dim lRow As Long
        Dim lCol As Long
        Dim lRowCount As Long
        Dim WS As Worksheet
        Dim WSOut As Worksheet
        
        Set WS = ThisWorkbook.Worksheets("Input")
        Set WSOut = ThisWorkbook.Worksheets("Output")
        LastRow = FindLastRow(WS, "A")
        lRowCount = 2
        With WSOut
            For lRow = 2 To LastRow
                For lCol = 2 To 4
                    Select Case WS.Cells(lRow, lCol)
                        Case Is > 0
                            .Cells(lRowCount, "A") = WS.Cells(lRow, "A")
                            .Cells(lRowCount, "B") = WS.Cells(1, lCol)
                            If InStr(WS.Cells(1, lCol), "Repayment") Then
                                .Cells(lRowCount, "D") = WS.Cells(lRow, lCol)
                            Else
                                .Cells(lRowCount, "C") = WS.Cells(lRow, lCol)
                            End If
                            lRowCount = lRowCount + 1
                    End Select
                Next lCol
            Next lRow
        End With
        
        Set WS = Nothing
        Set WSOut = Nothing
    End Sub
    Function FindLastRow(ByVal WS As Worksheet, ColumnLetter As String) As Long
        FindLastRow = WS.Range(ColumnLetter & Rows.Count).End(xlUp).Row
    End Function

  11. #11
    VBAX Regular
    Joined
    Dec 2008
    Posts
    7
    Location
    Thank you very much.

Posting Permissions

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