Help with a change in Macro
Results 1 to 4 of 4

Thread: Help with a change in Macro
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help with a change in Macro

    Hi, hope you can help.

    I currently have a spreadsheet set up with a macro in; we copy data into tab A, run the macro and it pulls through the info into tab B. We use this to format the information downloaded straight from our bank, however the bank's statements are now downloading in a different format.

    Basically, where the original statement had both debits and credits in one column, they are now split into two. Is there a way of changing the formula in the macro to sum up both columns in tab A and give the result into tab B?

    Hope that makes sense!

    Thanks

  2. #2
    Board Regular
    Join Date
    Dec 2018
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with a change in Macro

    Quote Originally Posted by Batcath View Post
    Hi, hope you can help.

    I currently have a spreadsheet set up with a macro in; we copy data into tab A, run the macro and it pulls through the info into tab B. We use this to format the information downloaded straight from our bank, however the bank's statements are now downloading in a different format.

    Basically, where the original statement had both debits and credits in one column, they are now split into two. Is there a way of changing the formula in the macro to sum up both columns in tab A and give the result into tab B?

    Hope that makes sense!

    Thanks
    Hey, should be possible, but i need to see your code to tell you more

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with a change in Macro

    Quote Originally Posted by Lavina View Post
    Hey, should be possible, but i need to see your code to tell you more
    This is everything:-


    Dim wb As Workbook, wk1 As Worksheet, wk2 As Worksheet, myvalue As Variant

    Sub BankRec()

    Set wb = ThisWorkbook

    Set wk1 = wb.Sheets("Bank Report")
    Set wk2 = wb.Sheets("Cashbook")

    Application.CutCopyMode = False
    wk1.Sort.SortFields.Clear
    wk1.Sort.SortFields.Add Key:=Range( _
    "S2:S5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
    With ActiveWorkbook.Worksheets("Bank Report").Sort
    .SetRange Range("A1:W5000")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With

    i = wk1.Range("U1").Value

    If wk1.Range("N1").Value = 0 Then

    Else

    MsgBox "There appear to be some duplicated lines, please check."

    End

    End If

    num = wk2.Range("B1")

    'Date
    wk2.Range("B" & num & ":B" & num + i - 2).Value = wk1.Range("AA2:AA" & i).Value
    'Narrative
    wk2.Range("C" & num & ":C" & num + i - 2).Value = wk1.Range("S2:S" & i).Value
    'Description
    wk2.Range("D" & num & ":D" & num + i - 2).Value = wk1.Range("S2:S" & i).Value
    'Amount
    wk2.Range("E" & num & ":E" & num + i - 2).Value = wk1.Range("W2:W" & i).Value

    'Type
    wk2.Range("F" & num & ":F" & num + i - 2).Value = wk1.Range("U2:U" & i).Value
    'Narration 2
    wk2.Range("G" & num & ":G" & num + i - 2).Value = wk1.Range("T2:T" & i).Value
    'Narration 3
    wk2.Range("H" & num & ":H" & num + i - 2).Value = wk1.Range("R2:R" & i).Value
    'Funds Cleared
    wk2.Range("I" & num & ":I" & num + i - 2).Value = "Y"

    wk1.Range("A2:AA" & i).ClearContents

    myvalue = InputBox("Please enter the Bank Statement C/F Balance")

    wb.Sheets("Bank Reconciliation").Range("F13") = myvalue

    'myvalue = vbNullString
    'myvalue = InputBox("Please enter the Nominal Ledger balance")

    'wb.Sheets("Bank Reconciliation").Range("F18") = myvalue
    If wb.Sheets("Bank Reconciliation").Range("F15").Value = 0 Then
    wb.Sheets("Cashbook").Activate
    Else
    wb.Sheets("Bank Reconciliation").Activate
    End If

    End Sub



    The highlighted line is what I want to amend to include the value in "X"

    Thank you

  4. #4
    Board Regular
    Join Date
    Dec 2018
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with a change in Macro

    Quote Originally Posted by Batcath View Post
    This is everything:-


    Dim wb As Workbook, wk1 As Worksheet, wk2 As Worksheet, myvalue As Variant

    Sub BankRec()

    Set wb = ThisWorkbook

    Set wk1 = wb.Sheets("Bank Report")
    Set wk2 = wb.Sheets("Cashbook")

    Application.CutCopyMode = False
    wk1.Sort.SortFields.Clear
    wk1.Sort.SortFields.Add Key:=Range( _
    "S2:S5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
    With ActiveWorkbook.Worksheets("Bank Report").Sort
    .SetRange Range("A1:W5000")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With

    i = wk1.Range("U1").Value

    If wk1.Range("N1").Value = 0 Then

    Else

    MsgBox "There appear to be some duplicated lines, please check."

    End

    End If

    num = wk2.Range("B1")

    'Date
    wk2.Range("B" & num & ":B" & num + i - 2).Value = wk1.Range("AA2:AA" & i).Value
    'Narrative
    wk2.Range("C" & num & ":C" & num + i - 2).Value = wk1.Range("S2:S" & i).Value
    'Description
    wk2.Range("D" & num & ":D" & num + i - 2).Value = wk1.Range("S2:S" & i).Value
    'Amount
    wk2.Range("E" & num & ":E" & num + i - 2).Value = wk1.Range("W2:W" & i).Value

    'Type
    wk2.Range("F" & num & ":F" & num + i - 2).Value = wk1.Range("U2:U" & i).Value
    'Narration 2
    wk2.Range("G" & num & ":G" & num + i - 2).Value = wk1.Range("T2:T" & i).Value
    'Narration 3
    wk2.Range("H" & num & ":H" & num + i - 2).Value = wk1.Range("R2:R" & i).Value
    'Funds Cleared
    wk2.Range("I" & num & ":I" & num + i - 2).Value = "Y"

    wk1.Range("A2:AA" & i).ClearContents

    myvalue = InputBox("Please enter the Bank Statement C/F Balance")

    wb.Sheets("Bank Reconciliation").Range("F13") = myvalue

    'myvalue = vbNullString
    'myvalue = InputBox("Please enter the Nominal Ledger balance")

    'wb.Sheets("Bank Reconciliation").Range("F18") = myvalue
    If wb.Sheets("Bank Reconciliation").Range("F15").Value = 0 Then
    wb.Sheets("Cashbook").Activate
    Else
    wb.Sheets("Bank Reconciliation").Activate
    End If

    End Sub



    The highlighted line is what I want to amend to include the value in "X"

    Thank you
    Hey, can you add an additional column?

    Then set something up like:

    Code:
    lastRow = Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    With Range("Z1:Z"&lastRow)
           .Formula = "=W1+X1"
           .Value = .Value
    End With
    And then just move Column Z instead of W.
    You can of course use some far off column that is never touched. If making an additional column is a problem we look for another way
    Last edited by Lavina; Aug 19th, 2019 at 06:51 AM.

Some videos you may like

User Tag List

Tags for this Thread

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
  •