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

getting account each client based on combobox and textbox

0

Hi Guys,

I  want  to match the  name in combobox1  with  column E    if  it's  matched  ,then  subtract  Total summing of  column F  from Total summing of  column G  which is  relating  the  client . and  show  the  final  result  in textbox1. for  instance   client KHALID  TOTAL for  column F=120+120  ,TOTAL for  column G=110 , the  result  in textbox1= 240-110 =130.  

should  sum any  values  for  the  client  for  each column F,G  and  subtract between two  columns . last  thing  should  be  show  as  numberformat ="#,##0.00" in textbox1

Private Sub ComboBox1_Change()
Me.TextBox1.Value = WorksheetFunction.SumIf(sheet2.Range("E:E"), ComboBox1.Value, sheet2.Range("F:F") - WorksheetFunction.SumIf(sheet2.Range("E:E"), ComboBox1.Value, sheet2.Range("G:G")))
End Sub

thanks

excel
MS Office Version: 2019
posted by: Ali M (rep: 28) on Mon Aug 22, 2022 at 9:09 am
Answer
Discuss

Discussion

Ali

I'm a bit confused by the wording of your question,. A representative file would help me (and save time)- please edit your question and use the Add Files... button to upload a representative Excel file (without any personal data) to show your existing macro and data. I'll then be able to help hopefully.  
John_Ru (rep: 6142) Aug 22, '22 at 10:12 am
Hi John,
my apologies !! 
honestly  I  thought  the  file  has been uploaded .
Ali M (rep: 28) Aug 22, '22 at 10:24 am
Add to Discussion

Answers

0
Selected Answer

Ali

Thanks for the file. I suggest you first create a single string criteria (IfCrit) with double quotation marks -given by Chr(34) in VBA- like "KHALID", since it's not a number. You can then use that in VBA's Evaluate (which will work out what a formula string in a cell would return) as follows: 

Option Explicit

Private Sub ComboBox1_Change()

Dim IfCrit As String

' define string criteria in double quotes
IfCrit = Chr(34) & ComboBox1.Text & Chr(34)
' evaluate SumIf strings with that criteria
Me.TextBox1.Value = Evaluate("=SUMIF(Sheet2!E2:E10," & IfCrit & ", Sheet2!F2:F10)") _
    - Evaluate("=SUMIF(Sheet2!E2:E10," & IfCrit & ", Sheet2!G2:G10)")

End Sub

(I limited the ranges to E2:E10 for example to prevent delays from working out entire columns)

The attached file has that code. Hope this fixes things for you.

posted by: John_Ru (rep: 6142) on Mon Aug 22, 2022 at 11:20 am
Discuss

Discussion

(I limited the ranges to E3:E10 for example to prevent delays from working out entire columns)
I thought to don't affect the whole column , then I should expand the range every time when increase the range.

actually  I  felt   so  confused  about  the  calculation    for  client ALI  . the  only  this  client  gives error  value   , but  I've  found  why  , you  start  the  range  from  row3  but  should  be 2 .
thanks  for  your  solution .
Ali M (rep: 28) Aug 22, '22 at 11:43 am
Oops! Sorry about that (I was trying the formula on my own file before your file arrived) but I have now corrected both the Answer and the file.

Thanks for selecting my Answer, Ali.
John_Ru (rep: 6142) Aug 22, '22 at 11:57 am
Add to Discussion


Answer the Question

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