PDA

View Full Version : [SOLVED] Require total in column B of positive & negative values



motilulla
06-11-2016, 07:53 AM
Hello,

In column A in cell A3 down entry cash In-Out I want to get total of positive & negative values in column B in cells B3 down as shown in example below



Cash

Sum



IN OUT

Pos & Neg



-2.117,00 €

-10659,44



-1.485,78 €




-2.984,51 €




-4.072,15 €




2.751,45 €

2751,45



-1.110,29 €

-1977,81



-867,52 €




216,88 €

2735,27



2.518,39 €




-1.045,55 €

-2683,47



-1.637,92 €




307,52 €

7432,15



4.978,51 €




1.090,87 €




1.055,26 €




-2.307,98 €

-3981,51



-110,06 €




-1.563,47 €




1.327,17 €

4366,71



3.039,54 €




-359,31 €

-1951,91



-1.592,60 €




2.172,03 €

5891,34



997,00 €




2.722,32 €




-1.573,18 €

-7215,27



-5.642,09 €




404,63 €

6425,45



1.113,53 €




640,93 €




3.573,65 €




692,72 €




-841,62 €

-841,62 €





Thanks In Advance

Using Excel 2000

Regards,
Moti

snb
06-11-2016, 08:07 AM
Why don't you post a sample file ?

motilulla
06-11-2016, 08:12 AM
snb, I do not know how to post let me try

motilulla
06-11-2016, 09:37 AM
Also I find useful If is data post under option “wrap tags around selected text” that can be copied and paste direct in to sheet as original data if any one do not want to download file may help here are the sample data




Cash

Sum



IN OUT

Pos & Neg



-2.117,00 €

-10659,44



-1.485,78 €




-2.984,51 €




-4.072,15 €




2.751,45 €

2751,45



-1.110,29 €

-1977,81



-867,52 €




216,88 €

2735,27



2.518,39 €




-1.045,55 €

-2683,47



-1.637,92 €




307,52 €

7432,15



4.978,51 €




1.090,87 €




1.055,26 €




-2.307,98 €

-3981,51



-110,06 €




-1.563,47 €




1.327,17 €

4366,71



3.039,54 €




-359,31 €

-1951,91



-1.592,60 €




2.172,03 €

5891,34



997,00 €




2.722,32 €




-1.573,18 €

-7215,27



-5.642,09 €




404,63 €

6425,45



1.113,53 €




640,93 €




3.573,65 €




692,72 €




-841,62 €

-841,62 €





Regards,
Moti

mdmackillop
06-11-2016, 10:11 AM
Option Explicit
Function InOut(data As Range)
Dim x As Boolean, i As Long
Dim Tot As Single
Application.Volatile
i = 1
x = data > 0
If IsNumeric(data(0)) Then
If data(0) > 0 = x Then
InOut = ""
Exit Function
End If
End If
If data(0) > 0 = x And data(2) > 0 = x Or data(2) = 0 Then
InOut = data
Exit Function
End If
Do
Tot = Tot + data(i)
i = i + 1
Loop Until (data(i) > 0) <> x Or data(i) = ""
InOut = Tot
End Function

motilulla
06-11-2016, 03:06 PM
[CODE]Option Explicit
Function InOut(data As Range)
Excellent!! mdmackillop (http://www.vbaexpress.com/forum/member.php?87-mdmackillop), working great

I appreciate your kind help

Regards
Moti :)

snb
06-12-2016, 12:14 AM
clear the contents of B1:B2; then use


Sub M_snb()
[B3:B100] = [if(A3:A100="","",if(A3:A100>0," ",0))]

For Each ar In Columns(2).SpecialCells(2, 1).Areas
ar.Cells(1) = Application.Sum(ar.Offset(, -1))
Next

For Each ar In Columns(2).SpecialCells(2, 2).Areas
ar.Cells(1) = Application.Sum(ar.Offset(, -1))
Next

Columns(2).Replace 0, "", 1
End Sub

Tom Jones
06-12-2016, 01:49 AM
Hi,

Please, how could alter codes, so the total number negative or positive, to be in the same row with the last number, positive or negative.
Ex. total of first negative numbers to be B6 not in B3, and so on.
Like below


Cash



IN OUT
Pos & Neg


-2.117,00 €



-1.485,78 €



-2.984,51 €



-4.072,15 €
-10659,441


2.751,45 €
2751,45


-1.110,29 €



-867,52 €
-1977,807


216,88 €



2.518,39 €
2735,265


-1.045,55 €



-1.637,92 €
-2683,473


307,52 €



4.978,51 €



1.090,87 €



1.055,26 €
7432,152


-2.307,98 €



-110,06 €



-1.563,47 €
-3981,51


1.327,17 €



3.039,54 €
4366,713



Thank you.

mdmackillop
06-12-2016, 03:59 AM
Hi Tom
Can you post your attempted code changes?

Tom Jones
06-12-2016, 04:23 AM
Hi mdmackillop,

In some codes, lighter, I can make some corrections but I have to understand the code. In your code did not understand how to do the math and do not know to correct it.

Same to snb code.

Can you help me - and explain - with a corrected code?

motilulla
06-12-2016, 04:54 AM
clear the contents of B1:B2; then use
Thank you snb (http://www.vbaexpress.com/forum/member.php?44644-snb), for giving a sub option I find useful because it leaves only value, which I did not new that could be too.
1st I find that if I rum code 1st time it delete header B1 and 2nd time it delete header B2
2nd code limit 100 row

I want if sub can be improved to work with my real data I will be grateful.

My real “Cash IN OUT” data find in column A, C, E, and G staring in row3 but data length in each column is different I meant row vary in each columns at present max row in column E is used 24780

I want to get “Sum Pos & Neg” results in Columns B, D, F, and H staring in row 3 and fill each column according to there data length

Example data and sample file is attached.




Cash
Sum
Cash
Sum
Cash
Sum
Cash
Sum


IN OUT
Pos & Neg
IN OUT
Pos & Neg
IN OUT
Pos & Neg
IN OUT
Pos & Neg


-2.117,00 €
-10659,44
216,88 €
216,88
-2.117,00 €
-10659,44
-2.117,00 €
-10659,44


-1.485,78 €

2.518,39 €

-1.485,78 €

-1.485,78 €



-2.984,51 €

-1.045,55 €
-2683,47
-2.984,51 €

-2.984,51 €



-4.072,15 €

-1.637,92 €

-4.072,15 €

-4.072,15 €



2.751,45 €
2751,45
307,52 €
7432,15
2.751,45 €
2751,45
2.751,45 €
2751,45


-1.110,29 €
-1977,81
4.978,51 €

-1.110,29 €
-1977,81
-1.110,29 €
-1977,81


-867,52 €

1.090,87 €

-867,52 €

-867,52 €



216,88 €
2735,26
1.055,26 €

216,88 €
2735,26
216,88 €
2939,20


2.518,39 €

-2.307,98 €
-3981,51
2.518,39 €

2.722,32 €



-1.045,55 €
-2683,47
-110,06 €

-1.045,55 €
-2683,47
-1.573,18 €
-7215,27


-1.637,92 €

-1.563,47 €

-1.637,92 €

-5.642,09 €



307,52 €
307,52
1.327,17 €
4366,71
307,52 €
7432,15
404,63 €
6425,45



0,00
3.039,54 €

4.978,51 €

1.113,53 €





-359,31 €
-1951,91
1.090,87 €

640,93 €





-1.592,60 €

1.055,26 €

3.573,65 €





2.172,03 €
5891,34
-2.307,98 €
-3981,51
692,72 €





997,00 €

-110,06 €

-841,62 €
-841,62




2.722,32 €

-1.563,47 €

1,00 €
1,00




-1.573,18 €
-7215,27
1.327,17 €
4366,71

0,00




-5.642,09 €

3.039,54 €







404,63 €
6425,45
-359,31 €
-1951,91






1.113,53 €

-1.592,60 €







640,93 €

2.172,03 €
5891,34






3.573,65 €

997,00 €







692,72 €

2.722,32 €







-841,62 €
-841,62
-1.573,18 €
-7215,27






1,00 €
1,00
-5.642,09 €








0,00
404,63 €
6425,45








1.113,53 €









640,93 €









3.573,65 €









692,72 €









-841,62 €
-841,62








1,00 €
1,00






Regards,
Moti

motilulla
06-12-2016, 04:57 AM
Hi,

Please, how could alter codes, so the total number negative or positive, to be in the same row with the last number, positive or negative.
Ex. total of first negative numbers to be B6 not in B3, and so on.
Like below


Cash




IN OUT

Pos & Neg



-2.117,00 €




-1.485,78 €




-2.984,51 €




-4.072,15 €

-10659,441



2.751,45 €

2751,45



-1.110,29 €




-867,52 €

-1977,807



216,88 €




2.518,39 €

2735,265



-1.045,55 €




-1.637,92 €

-2683,473



307,52 €




4.978,51 €




1.090,87 €




1.055,26 €

7432,152



-2.307,98 €




-110,06 €




-1.563,47 €

-3981,51



1.327,17 €




3.039,54 €

4366,713




Thank you.
Tom Jones, please can you explain how you have paste data to look like in your post

Thank you

Regards,
Moti

mdmackillop
06-12-2016, 05:17 AM
Commented code as above. Work your way through the Checks; step through the code using the Watch window

Option Explicit


Function InOut(data As Range)
Dim x As Boolean, i As Long
Dim Tot As Single
Application.Volatile

'data(0) refers to cell above
'data or data(1) refers to formula cell
'Data(2) etc. refers to following cells

i = 1
x = data > 0 'set x to True or false ==> Is data +
'Check 1
If IsNumeric(data(0)) Then 'Is cell above numeric? If not, skip next bit
'Check 2
If (data(0) > 0) = x Then 'Cell is numeric and same */- as previous
InOut = "" 'so return empty value
Exit Function 'exit fumction
End If
End If
'Check 3
If (data(0) > 0 = x) And (data(2) > 0 = x) Or (data(2) = 0) Then 'Check if data is different from above and below e.g A7
InOut = data 'If so, total = 1 cell only; Enter total vale
Exit Function 'End routine
End If
'Remainder
Do
Tot = Tot + data(i) 'Start to total cells as +/- swequence is identified
i = i + 1 'increment row
Loop Until (data(i) > 0) <> x Or data(i) = "" 'continue until +/- value changes or empty cell is encountered
InOut = Tot 'Enter total value
End Function

Tom Jones
06-12-2016, 07:07 AM
@motilulla,

Just copy from file and paste here.

@mdmackillop

Thanks for the explanation but I still do not realize how I can modify the code, so that the outcome of sum to be in the same row with the last number, positive or negative, in column A.

Thanks.

mdmackillop
06-12-2016, 07:27 AM
Hi Tom
We are not here simply to provide solutions. After a while, we expect posters to attempt a solution.

Tom Jones
06-12-2016, 08:01 AM
Thank you mdmackillop,

Your code does not use it than to learn. I'm retired and I discovered newest Excel and VBA. Excel is a hobby for me. I wanted to see and variant exposed me because I tried and I could not change it as we wanted.
I did not want in any way to abuse your kindness. Thank you .

mdmackillop
06-12-2016, 08:05 AM
I'm happy to assist. Please post what you have attempted.

mdmackillop
06-12-2016, 08:49 AM
Option Explicit
Sub Tots()
Dim r As Range, cel As Range
Dim Arr()
Dim x As Boolean, y As Boolean
Dim i As Long, j As Long


Set r = Range(Cells(3, 1), Cells(Rows.Count, 1).End(xlUp))
ReDim Arr(r.Cells.Count)
ReDim Bt(r.Cells.Count)


Arr(i) = r(0).Address


For Each cel In r
x = (cel > 0)
y = (cel(2) > 0)


If x <> y Or cel(2) = "" Then
i = i + 1
Arr(i) = cel.Address
End If
Next
ReDim Preserve Arr(i)


For j = 1 To i
Range(Arr(j)).Offset(, 3).Formula = "=SUM(" & Range(Arr(j - 1))(2).Address & ":" & Arr(j) & ")"
Next
End Sub

SamT
06-12-2016, 08:52 AM
My real “Cash IN OUT” data find in column A, C, E, and G staring in row3 but data length in each column is different
That is why it is best, easiest to use modified standard bookkeeping columns. Modified by placing totals at top of sheet




Account Name

Balance:
=C3-D3

Account Name

Blah




Date

Cash in
Cash Out

Blah
Blah
Blah


Sub Totals->

=SUM(C4:C65000)
=SUM(D4:D65000)







1/17/50
19.21







2/26/73
24.98








7/4/1776

17.76

snb
06-12-2016, 09:35 AM
@TJ

Sub M_snb()
[B1:B2]=""
[B3:B100] = [if(A3:A100="","",if(A3:A100>0," ",0))]

For Each ar In Columns(2).SpecialCells(2, 1).Areas
ar.Cells(ar.cells.count)) = Application.Sum(ar.Offset(, -1))
Next

For Each ar In Columns(2).SpecialCells(2, 2).Areas
ar.Cells(ar.cells.count) = Application.Sum(ar.Offset(, -1))
Next

Columns(2).Replace 0, "", 1
End Sub

motilulla
06-12-2016, 10:25 AM
@motilulla,
Just copy from file and paste here.
Tom Jones, I did it in opening post but I think my old version 2000 does not copy format and when I paste here in post1-copied only data not format

Thank you

Regards,
Moti

motilulla
06-12-2016, 10:46 AM
That is why it is best, easiest to use modified standard bookkeeping columns. Modified by placing totals at top of sheet
Thank you SamT, I like your worksheet example it is doing the same job what it require and reporting summary very fine. I do appreciate it.

Please do you think is it possible to get VBA that do the job with #post11 attached file In-Out v1.xls

Regards,
Moti

motilulla
06-12-2016, 11:44 AM
[CODE]Option Explicit
Sub Tots()
Hello Mdmackillop, Sorry to trouble you again

I see you have posted sub already in the post above, which can solve me, problem please can you change code to result sum-descending order as shown below.




Cash


Sum


IN OUT


Pos & Neg


-2.117,00 €


-10659,44


-1.485,78 €





-2.984,51 €





-4.072,15 €





2.751,45 €


2751,45


-1.110,29 €


-1977,81


-867,52 €





216,88 €


2735,26


2.518,39 €





-1.045,55 €


-2683,47


-1.637,92 €





307,52 €


7432,15


4.978,51 €





1.090,87 €





1.055,26 €





-2.307,98 €


-3981,51


-110,06 €





-1.563,47 €





1.327,17 €


4366,71


3.039,54 €





-359,31 €


-1951,91


-1.592,60 €





2.172,03 €


5891,34


997,00 €





2.722,32 €





-1.573,18 €


-7215,27


-5.642,09 €





404,63 €


6425,45


1.113,53 €





640,93 €





3.573,65 €





692,72 €





-841,62 €


-841,62


1,00 €


1,00




Thank you

Regards,
Moti

SamT
06-12-2016, 12:23 PM
These simple subs, run in order, will insert the date columns and the empty spacer columns. They will set the headers for Row 2 and move the negative numbers to the Cash Out Columns. There is one sub that will convert the Cash Out numbers to positive.


Sub ClearSumsColumns()
Dim c As Long
For c = Cells(1, Columns.Count).End(xlToLeft).Column To 2 Step -2
Columns(c).ClearContents
Next
End Sub

Sub SetCashoutHeaders()
Dim c As Long
For c = Cells(1, Columns.Count).End(xlToLeft).Column To 2 Step -2
Cells(2, c) = "Cash Out"
Next
End Sub

Sub SetCashInHeaders()
Dim c As Long
For c = Cells(1, Columns.Count).End(xlToLeft).Column - 1 To 1 Step -2
Cells(2, c) = "Cash In"
Next
End Sub

Sub MoveMinusNumbers()
Dim c As Long
Dim r As Long
Dim Cel As Range

For c = Cells(1, Columns.Count).End(xlToLeft).Column - 1 To 1 Step -2
For r = 3 To Cells(Rows.Count, c).End(xlUp).Row
Set Cel = Cells(r, c)
If Cel < 0 Then
Cel.Offset(, 1) = Cel
Cel = 0
End If
Next
Next
End Sub

Sub ConvertNegsToPos()
'converts all negative numbers in Cash Out to positive numbers.
Dim Cel As Range
For Each Cel In UsedRange
If Cel < 0 Then Cel = Abs(Cel)
Next
End Sub

Sub InsertDateColumns()
Dim c As Long
For c = Cells(1, Columns.Count).End(xlToLeft).Column To 1 Step -2
Columns(c).EntireColumn.Insert
Next
End Sub

Sub AddDateColumnHeader()
Dim c As Long
For c = Cells(2, Columns.Count).End(xlToLeft).Column - 2 To 1 Step -3
Cells(2, c) = "Date"
Next
End Sub

Sub InsertEmptyColumnBetween()
Dim c As Long
For c = Cells(2, Columns.Count).End(xlToLeft).Column - 2 To 4 Step -3
Columns(c).EntireColumn.Insert
Next

End Sub

Now, Delete Row 1, insert 7 new Rows at the top,Insert a new Column A.

Then Copy the top 7 rows from one sheet in my attachment and paste the into Row 1.

To convert all the Displayed formulas to real formulas, Select the top 7 rows, Press Ctrl+H and replace '= with =

As you noticed, to add a new account, just copy the 3 columns and top 8 rows of any existing account and paste it into a new location.

This style of simple bookkeeping was designed to make it easy to write code for a VBA UserForm for Data Entry.

The built-in Tricks are
nb: must clear Range("A7")

Sheet names are Group Names

Row 4 is the list of accounts on the sheet.

Account Names are actually not in Merged Cells, they are Horizontally Aligned "Center Across Selection"

ListBox Accounts.ListIndex * 4 + 2 is the column for the selected account. Example: Account1 in Cells(4,2). The Row is always 4


With AccountCell.CurrentRegion
The account balance is always .Cells(2,1)

Cash In Balance is .Cells(3, 2)

Cash Out Balance is .Cells(3, 3)

Next Date Entry cell is .Cells(.Rows.Count, 1).Offset(1). NB: this adjusts Current Region, so Set a Variable to this cell then enter Amounts according to an offset from that Set Variable


Dim NextEntry As Range
With Account Cell.CurrentRegion
Set NextEntry = .Cells(.Rows.Count, 1).Offset(1)
NextEntry =Date
NextEntry.Offset(, 2) = CashOut value

motilulla
06-12-2016, 01:16 PM
These simple subs, run in order, will insert the date columns and the empty spacer columns. They will set the headers for Row 2 and move the negative numbers to the Cash Out Columns. There is one sub that will convert the Cash Out numbers to positive.


Now, Delete Row 1, insert 7 new Rows at the top,Insert a new Column A.

Then Copy the top 7 rows from one sheet in my attachment and paste the into Row 1.

To convert all the Displayed formulas to real formulas, Select the top 7 rows, Press Ctrl+H and replace '= with =

As you noticed, to add a new account, just copy the 3 columns and top 8 rows of any existing account and paste it into a new location.

This style of simple bookkeeping was designed to make it easy to write code for a VBA UserForm for Data Entry.

The built-in Tricks are
nb: must clear Range("A7")

Sheet names are Group Names

Row 4 is the list of accounts on the sheet.

Account Names are actually not in Merged Cells, they are Horizontally Aligned "Center Across Selection"

ListBox Accounts.ListIndex * 4 + 2 is the column for the selected account. Example: Account1 in Cells(4,2). The Row is always 4


With AccountCell.CurrentRegion
The account balance is always .Cells(2,1)

Cash In Balance is .Cells(3, 2)

Cash Out Balance is .Cells(3, 3)

Next Date Entry cell is .Cells(.Rows.Count, 1).Offset(1). NB: this adjusts Current Region, so Set a Variable to this cell then enter Amounts according to an offset from that Set Variable


Dim NextEntry As Range
With Account Cell.CurrentRegion
Set NextEntry = .Cells(.Rows.Count, 1).Offset(1)
NextEntry =Date
NextEntry.Offset(, 2) = CashOut value
SamT, really it is new and too difficult for me to make it work I think I am following as per your instruction
But many things are involved need to understand bit in detail

1st do I have to use your and mine attachments and make one
2nd do I need to run your code in order with my attachment
3rd where or in which 3 of sheet I have paste 7 rows
4th nothing happened if I select 7 rows and press Ctrl+H
5th what is the bottom code Dim NextEntry As Range and where to put

I don’t know what I am doing wrong

Please help I want your idea to make it work

Thank you

Regards,
Moti

mdmackillop
06-12-2016, 01:26 PM
please can you change code to result sum-descending order as shown below

SNB's excellent sub gives the desired result. If you wish formulae in the cells then make this change in his code


ar.Cells(1).Formula = "=Sum(" & ar.Offset(, -1).Address & ")"

motilulla
06-12-2016, 02:03 PM
SNB's excellent sub gives the desired result. If you wish formulae in the cells then make this change in his code


ar.Cells(1).Formula = "=Sum(" & ar.Offset(, -1).Address & ")"
Mdmackillop, thank you I am same place May I am not explaing well I am not able to alter the code what I want VBA that put column A sum of positive & negative values in column B starting from cell B3 as long as data find in column A,

For example if column A has data in 4000 row Column B fill sum to be filled 4000 rows I mean as long as data find in the column A

Regards,
Moti

mdmackillop
06-12-2016, 02:16 PM
The code given will work to row 100. If you need more adjust this line accordingly


[B3:B100] = [if(A3:A100="","",if(A3:A100>0," ",0))]

motilulla
06-12-2016, 02:41 PM
The code given will work to row 100. If you need more adjust this line accordingly


[B3:B100] = [if(A3:A100="","",if(A3:A100>0," ",0))]
Mdmackillop, I agree with you but I need to modify every time when new row added or subtracted
For all that I like your code you have posted in #post 18 which is perfect work as long as data find in column A
If it is not much trouble for you can modify and display result sum, as my opening post will be great of you.

Thank you

Regards,
Moti

mdmackillop
06-12-2016, 03:10 PM
You misunderstand. Set it for the maximum number or rows you need. There is no need to change it every time. Please test these things to satisfy yourself and reveal if there are any real issues.

motilulla
06-12-2016, 04:02 PM
You misunderstand. Set it for the maximum number or rows you need. There is no need to change it every time. Please test these things to satisfy yourself and reveal if there are any real issues.
Mdmackillop, ok for example my last used row is A3250 and in code I change 100 to say for example 45000
Before I run the code if I press Ctrl+End it goes to A3250 but after I run the code if I press Ctrl+End it goes to A45000 how can you control this? I do not want code to do this always remember my last used cell…


Thank you

Regards,
Moti

Tom Jones
06-13-2016, 02:35 AM
@TJ

Sub M_snb()
[B1:B2]=""
[B3:B100] = [if(A3:A100="","",if(A3:A100>0," ",0))]

For Each ar In Columns(2).SpecialCells(2, 1).Areas
ar.Cells(ar.cells.count)) = Application.Sum(ar.Offset(, -1))
Next

For Each ar In Columns(2).SpecialCells(2, 2).Areas
ar.Cells(ar.cells.count) = Application.Sum(ar.Offset(, -1))
Next

Columns(2).Replace 0, "", 1
End Sub



@snb,

Thank you very much. As usual your VBA codes are fantastic, extraordinary.
I do not think I'll learn some time to make such codes.


@mlmackillop,

Thanks for cod and lost time to help me.

motilulla
06-13-2016, 03:26 PM
These simple subs, run in order, will insert the date columns and the empty spacer columns. They will set the headers for Row 2 and move the negative numbers to the Cash Out Columns. There is one sub that will convert the Cash Out numbers to positive.
SamT, I have read your Post #24 more than 30 times and tried my best to get results as you explain but could not get it. Sub MoveMinusNumbers does not result any thing I thought might I post file with code please take a look what I am doing wrong

Thank you

Regards
Moti

SamT
06-13-2016, 05:50 PM
You have to put all that code in the Sheet1 code page.

There was just enough difference in v2 that I had to adjust the column numbers a bit.

The numbers to adjust are A and B

Cells(2, Columns.Count).End(xlToLeft).Column - A To 1 Step -BA adjusts the starting column, counting from the last used column in Row 2, and B adjusts how many columns to "step" or "skip" over.

The reason I wrote each step i the process as a separate sub was to make it easy to figure out and adjust.

When I started, I made a copy of sheet1, If I messed up, I copied all the cells from the Sheet1(2) to sheet1 and tried again

snb
06-14-2016, 12:53 AM
@SamT

Just a teaser :jester: ;)


Sub M_snb()
UsedRange.Name = "snb"
[snb] = [if(snb="","",if(snb<0,"="&snb,snb))]
[snb].SpecialCells(-4123).Offset(, 1) = [snb].SpecialCells(-4123).Value
[snb].SpecialCells(-4123).Clear
End Sub

motilulla
06-14-2016, 02:58 AM
16392
You have to put all that code in the Sheet1 code page.
Thank you SamT, for your patience and helping out.

I rerun codes and achieve to designed bookkeeping sheet as per your instructions it is an interesting I like the sheet now and in module1 entered the bottom code is in your post #24 but can not make it run highlights 2 lines “With Account Cell.CurrentRegion” & “NextEntry.Offset(, 2) = CashOut value” I have attached new file please take a look

Please what is VBA UserForm for Data Entry? I know it is too much asking a favour but really if you can make it when you get time I am interested to have it I am finding it useful for me.

Thank you

Regards
Moti