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

Subtract amount in textbox from column for each name

0

Hello,

I try to subtract amount in textbox2 from column C  for each name is matched with combopbox1 . when select name from combobox1 and populate amount in textbox1 and I will write amount with formatting "#,##0.00"  then should search for  name in column B  and subtract amount from column C .

this is what I have so far

Private Sub CommandButton1_Click()
Dim TTot As Double
Dim M As Range
  With Worksheets("CS")
    Set M = .Columns(2).Find(ComboBox1.Value, , xlValues, xlWhole)
    If Not M Is Nothing Then
    TTot = TextBox2.Value
      M.Offset(, 1).Text = M.Offset(, 1).Text - TTot
    End If
  End With
End Sub

it will show error object required in this line

      M.Offset(, 1).Text = M.Offset(, 1).Text - TTot

appreciated for any help.

excel
MS Office Version: 2019
posted by: Abdo M (rep: 20) on Thu Nov 21, 2024 at 5:27 pm
Answer
Discuss

Answers

0
Selected Answer

Hi again Abdo

The error arises because you are trying to subtract from a string - since the .Text property of the offset cell (Range) includes the comma from your cell fomatting.

All you need to do is change it to .Value in the error line in your question to that below (changes in the attached file and in bold below):

Private Sub CommandButton1_Click()
    Dim TTot As Double
    Dim M As Range

    With Worksheets("CS")
      Set M = .Columns(2).Find(ComboBox1.Value, , xlValues, xlWhole)
      If Not M Is Nothing Then
      TTot = TextBox2.Value
        M.Offset(, 1).Value = M.Offset(, 1).Value - TTot
      End If
    End With

End Sub

Note that the code now changes the cells on the Worksheet (and you probably need to update your UserForm and make similar chnages elsewhere in your UserForm code).

Hope this solves the matter for you. If so, please remember to mark this Answer as Selected.

posted by: John_Ru (rep: 6537) on Fri Nov 22, 2024 at 2:47 am
Discuss

Discussion

OMG!
I was supid for this error .I no know how this is missed me .
yes I have  procedure to update the form.
thank you so much for you help.
Abdo M (rep: 20) Nov 22, '24 at 7:09 am
Glad that helped, Abdo and thanks for selecting my Answer. You weren't being stupid at all- it's often difficult to see your own errors in my opinion, easier for another person to see them with fresh eyes. 
John_Ru (rep: 6537) Nov 22, '24 at 9:03 am
Add to Discussion


Answer the Question

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