Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

entering new data code gives incorrctly result

0

hello

in first file works based on this detailes

each process start Cash deposit words in column E and finish at same Cash deposit words in column E(each group of data start Cash deposit words) should add SUBTOTAL row for each group and sum amounts in DEBIT,CREDIT columns for Cash deposit TOTAL,REMAINING Cash deposit and subtract Cash deposit TOTAL from REMAINING Cash deposit when add rows in last range. I put formulas how calculate also when there is remaining value then will transfer to next group and write REMAINING Cash deposit word ,but if the value zero then should not transfer

but when add new entering start Cash deposit words  then will not sum whole amounts for new group and show REMAINING Cash deposit  words in wrong location as show in file2

as you see higlighted cells in B15:C15 will exclude from summing in B19:C19 

also REMAINING Cash deposit  words should n't show ,show in one case if there is new data under it 

excel
MS Office Version: 2019
posted by: Abdo M (rep: 24) on Sat Mar 7, 2026 at 8:04 am
Answer
Discuss

Discussion

Hi again Abdo.

Sorry but I'm struggling to understand what you're tryng to do here. I looked at both files but it's still not clear. Also I don't recognise your code / methods here.

Not sure I'll be able to help you- perhaps contributor WillieD24 can make some sense of your approach. 
John_Ru (rep: 6792) Mar 7, '26 at 11:44 am
Hello Abdo,

I have to agree with John. I too have looked at both files and have come to the same conclusions. I don't understand what you are trying to do and I don't understand your coding (macro).

Maybe if you better explain what you are trying to do we may be able to help.

Cheers   :-)
WillieD24 (rep: 723) Mar 7, '26 at 2:06 pm
ok guys  I edited attaching by putting expected result should be and   to understand how calculations by putting formulas . code searchs word "Cash deposit"  will split data into multiple groups based on Cash deposit which start and insert SUBTOTAL row to sum whole amounts for DEBIT,CREDIT columns but for each group alone  . each group start Cash deposit  and finish at word don't contain Cash deposit  word  after that if the amount in BALANCE  column for each group doesn't contain zero  then will copy date and amount from prevouis group  to next group in column B and populate word REMAINING Cash deposit  in column E for adjacent amount in column D. so as you see from formulas each group will calculate alone.
the original code works from first running , but when add new data will make chaos in calculation !
Abdo M (rep: 24) Mar 8, '26 at 6:05 am
Appreciate you trying to better explain your aim with this file but I am still confused. Your original post now only has one file attached and that file has no highlights and the last row is 16, so there is no summing in row 19 (B19:C19). In your macro only variable "ap" is declared as an Object. Variables a, i&, t&, and n& are declared as Variants. For these to be declared as Objects the code needs to read: Dim a As Object, i& As Object, t& As Object, n& As Object, ap As Object, w(1 To 5). I don't know why you don't get an error for your variable names because the character "&" is not allowed in a variable name
VBA Variable Naming Rules   
Sorry, but I am still unable to help.

Cheers   :-)
WillieD24 (rep: 723) Mar 8, '26 at 4:32 pm
@Willie
actually I deleted second file because you & John you said file is not clear for you despite of I indicate to some cells doesn't arrange and calculation correctly and explain what code supposes to work , so I wanted to show what code does it and compare with my goal, obvouisly it's hard to achieve that and I knew that from the beginning , I posted here to I getting chance for help.
but my bad lucky day.
anyway thanks guys for trying to help me.
Abdo M (rep: 24) Mar 9, '26 at 4:44 am
@Willie

There won't be an error from the declarations here since when the code declares the variable as "i&", it's the same as "i as Long." It's using one of the Identifier Type Characters for the Data Type, as a shortcut. Omitting that (or " as ...") means the variable (e.g. his a) is a Variant. See the Microsoft guidance down the page of Data type summary

@Abdo - the original second file showed "unwanted outcomes" but I was confused by the way you laid out your data, especially the use of "REMAINING".

I'll try to find time to look at your file later today hopefully.
John_Ru (rep: 6792) Mar 9, '26 at 12:36 pm
@John,
As is typical of Microsoft's confusing explanations - one article states you can't use certain characters in variable names and another article says you can. Make up your mind MS.
I'll leave this one to you.

Cheers   :-)
WillieD24 (rep: 723) Mar 9, '26 at 2:52 pm
@Willie - I don't feel I have to defend Microsoft but the naming convention is maintained - e.g. the variable is still called i but the Identifier Type Character of & in the declaration quickly makes it a Long data type. 
@Abdo - sorry but I doubt I'll find time before Wednesday to look in detail
John_Ru (rep: 6792) Mar 9, '26 at 7:13 pm
Add to Discussion



Answer the Question

You must create an account to use the forum. Create an Account or Login