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

add condition to my code

0

hi

first  of  all  i  would  thank  Mr . John  for  provide  me  assistance  I issue  a new  post 

for  rules  this  forum  actually  I  would work on   the  same  file   i added checkbox1  so  what  I want  when  I  select  checkbox1  and  select  the  name in b1  then  ignore  any  balances in column e  before zero  value   because  it's  paid  whit  keepung  all of  condition  in  the  code  without  change  it   the  aim  of  that  sometimes  Iwould  show  just  remaining balance  without  see to the  paid  balance   with  considering  should  not  show  the  balances  in e6,c3   becaust  it's  paid 

thanks  again 

Answer
Discuss

Answers

0

Sorry to say, Khalil, that your logic can't be implemented in the real world. This is because you expect the balance to go to zero sooner or later, and that would signify a new start. Trouble is that it will seldom go to zero, especially with your best customers. Take Sameer in your sample workbook. He paid  Rs 15,000 on July 4. What if he would have bought something else on that day and then still paid Rs 11,380 on July 19. The balance wouldn't have been zero and your idea of excluding paid invoices would fail. To be clear: it would fail for your most important and most active accounts while working for less important customers.

The only way to know which invoices are paid is to assign each payment to a particular invoice and then mark invoices that are paid as "settled". Only if you have such a system can we exclude those invoices and the related payments from the display. In your worksheet you have the columns to do that but not the logic needed to fill them.

Part of that logic is your handling of initial balances. In your example, the opening balance of Rs 100 should be booked into the Credit column. This is necessary so that the total of Debit minus total of Credit = Current Balance. The Balance column's first line is calculated same as all the others: E5 + C6 - D6. However, this will fail because E5 is a word, not a number. Beat the problem by using the SUM function.

[E6] =SUM(E5,C6,-D6)

Apply this Custom cell format to E6:E11.

#,##0.00 "DR";[Red]#,##0.00 "CR";

Of course, you can set the formula by code but it doesn't make much sense to ask VBA to ask Excel to do the calculations. VBA knows how to calculate itself. So, once your b/f balance is in the correct column it can draw the correct balance on every line and fill it in. It can also set the cell format.

Your column F should be hidden and it should contain the open balance of each invoice. When a payment is made the balance is reduced, oldest one first. When an invoice is settled the balance for it is zero and it can be excluded from the display.

Your column G is superfluous. The "Get Data" button is also useless. Instead, use the event procedure you already have. Below is the corrected version of this procedure. Just expand the function to not only delete old data but also display the new whenever the user changes the customer name.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Range("B1:C3"), Target) Is Nothing Then
        ' if a search value changed, clear the data
        Sheets("account").Range("A6:H114").ClearContents
    End If
End Sub

Note that the range B1:C3 in the above procedure is wrong. Clearly, you mean B1 or C3. However, C3 is totally wrong and should be removed. This balance comes from Sheet1 and can't be modified in the display.

A6:H114 will only work until it doesn't work and then leave you baffled. Learn how to define a dynamic range. Here at Teach Excel we will teach you. Just remember to ask single questions. In this post I am giving you 3 or 4 answers to questions you didn't ask. That's not the way this forum should work. Thank you for your support.

Discuss

Discussion

Variatus 
first  of  all  my  apology  about  my  request  indeed  when  i  issue  this  post  I suspect   it's  not  possible  do  that  just  i  want  searching  for  a unique  idea  but  I failed to  issue  the  logical  idea   despite  your  comment  is  very  huge  but  it's  good   to  know  the  right way  how  the  matters  should  be   I will satisfy  your  answering   thanks  for  your  time
Kalil (rep: 36) Mar 14, '21 at 3:31 am
Add to Discussion


Answer the Question

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