You are here:

Excel/Find Cell from ComboBox Value and write to adjacent cell

Advertisement


Question
Snapshot
Snapshot  
QUESTION: I have a Userform which searches for the text selected in the combobox Date in Row A and writes the value in textbox Amount in a cell adjacent to the date and under the cell equal to the combobox Category when the command button is pressed.

Below is the code -

Private Sub cmdAdd_Click()
   Dim iRow As Long, iCol As Long
    
    
   If cmbDate = "" Then
       MsgBox "please select a date"
       cmbDate.SetFocus
       Exit Sub
   End If
   If cmbCat = "" Then
       MsgBox "please select a category"
       cmbCat.SetFocus
       Exit Sub
   End If
   If txtAmount = "" Then
       MsgBox "please enter then amount"
       txtAmount.SetFocus
       Exit Sub
   End If
    
    
   With ActiveSheet
       iRow = .Range("A:A").Find(cmbDate.Value, LookIn:=xlValues, LookAt:=xlWhole).Row
       iCol = .Range("3:3").Find(cmbCat.Value, LookIn:=xlValues, LookAt:=xlWhole).Column
       .Cells(iRow, iCol) = .Cells(iRow, iCol) + txtAmount
   End With
    
    
    'clear the data
   cmbDate.Value = ""
   cmbCat.Value = ""
   txtAmount.Value = ""
   cmbDate.SetFocus
    
    
End Sub


Private Sub cmdDone_Click()
   Unload Me
End Sub


Private Sub UserForm_Initialize()
   Dim cItem As Range
    
    
   Dim ws As Worksheet
    
    
   Set ws = ActiveSheet
   For Each cItem In ws.Range("3:3").SpecialCells(xlConstants).Offset(, 0)
       With Me.cmbCat
         If cItem <> "" And cItem <> "Total" Then .AddItem cItem.Value
       End With
   Next cItem
    
    
   For Each cItem In ws.Range("A:A").SpecialCells(xlConstants, xlNumbers)
       With Me.cmbDate
         .AddItem cItem.Value
       End With
   Next cItem
    
    
End Sub

I have made changes to the Userform and have added 2 textboxes Time and Counts.

I need a macro that can do as below

When the command button is pressed the values entered in one or both the textboxes should get updated under the cell equal to textboxes and for the correct date and category.

ANSWER: Bimmy

based on the very limited information you have proviced, I would say


With ActiveSheet
       iRow = .Range("A:A").Find(cmbDate.Value, LookIn:=xlValues, LookAt:=xlWhole).Row
       iCol = .Range("3:3").Find(cmbCat.Value, LookIn:=xlValues, LookAt:=xlWhole).Column
       .Cells(iRow, iCol) = .Cells(iRow, iCol) + txtAmount
   End With


would become something like

(assume txtTime holds the time and txtCount holds the count and the time is in column G and the count is in Column H( as you show in your image))


With ActiveSheet
       iRow = .Range("A:A").Find(cmbDate.Value, LookIn:=xlValues, LookAt:=xlWhole).Row
       iCol = .Range("3:3").Find(cmbCat.Value, LookIn:=xlValues, LookAt:=xlWhole).Column
       .Cells(iRow, iCol) = .Cells(iRow, iCol) + txtAmount
       .Cells(iRow, "G").Value = txtTime.Value
       .Cells(iRow, "H").Value = txtCount.Value
End With

Of course you can also add code at the top of  your event to make sure there are values in the Time and Count Textboxes - but that would just be duplicating the code you already have there and adjusting it for these specific controls.


--
Regards,
Tom Ogilvy



---------- FOLLOW-UP ----------

Error
Error  
QUESTION: I tried your code but its giving me an error

Have attached snapshot of the error

ANSWER: Bimmy,

Note that I said:  

(assume txtTime holds the time and txtCount holds the count and the time is in column G and the count is in Column H( as you show in your image))

You told me:
I have made changes to the Userform and have added 2 textboxes Time and Counts.
but you also said you had a textbox named Amount and in your code you use txtAmount
You said combobox Date and combobox Category and in your code you cmbDate and cmbCat

So I do not know the names of your controls for time and counts. I told you the assumption I made which was generally consistent with a "textbox Amount", "combobox Category" and your actual code.  You have to put in your actual control names in the code - sorry if that wasn't clear to you.   That is what the error is saying.  That it can't find a textbox named txtTime.  When you fix that with the actual name then it will probably tell you it can't find a textbox named txtCount so you will need to fix that as well with the actual name.

--
Regards,
Tom Ogilvy






---------- FOLLOW-UP ----------

QUESTION: Many Apologies for asking such questions, I got the above mentioned Userform and code from the net. I tried to make few changes to it, to achieve what I want. But it seems I end up with new problems.

From your suggestion I made the required changes and the issue went away. But ended up with a new error.

Getting a runtime error 91 :

Object variable or with block variable not set

on below line of the code

iRow = .Range("A:A").Find(cmbDate.Value, LookIn:=xlValues, LookAt:=xlWhole).Row

Is there an option to attach the file so that you can make the required corrections.

Note :

There are a number of Categories spread across the sheet. The snapshot just shows a part of it. To put in simple terms the Time and Counts reflects in
Columns J-K, M-N,P-Q,S-T,V-W,Y-Z and so on.

Also from what I could make out, the below code keeps adding the values entered into the textboxes. Which is something I'm not interested in.

Private Sub UserForm_Initialize()
Dim cItem As Range

Dim ws As Worksheet

Set ws = ActiveSheet
For Each cItem In ws.Range("3:3").SpecialCells(xlConstants).Offset(, 0)
   With Me.cmbCat
       If cItem <> "" And cItem <> "Total" Then .AddItem cItem.Value
   End With
Next cItem

For Each cItem In ws.Range("A:A").SpecialCells(xlConstants, xlNumbers)
   With Me.cmbDate
       .AddItem cItem.Value
   End With
Next cItem

End Sub

I need the code to insert (not add) whatever values are entered in both the textboxes to the correct cells. Also certain columns will have only the column Time. So when values are entered only in textbox Time, macro should update the relevant cells.

Answer
Bimmy,

You can send the workbook to be at twogilvy@msn.com

make sure you tell me exactly what you want to do.

Also, this is a peer to peer support network.  I provide technical advice and you implement it.

Since you are having problems, I will take a look - but don't expect me to build an application for you.  

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.