Help with a change in Macro

Batcath

New Member
Joined
Aug 19, 2019
Messages
2
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 :)
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top