PDA

View Full Version : Solved: Entering date into adjacent cell to selected



Johnpants
11-08-2005, 08:38 AM
Hi, I have a worksheet that has a list of divisions in column A, in column B I want to be able to enter each divisions sales target, for example:

A1 = Marketing
B1 = ?1000

I have a Userform created that has a ComboBox populated with the divisions in column A. What I am stuck on, is from the TextBox I want it to add what I enter, 1 Cell to the right depending on which division I select from the ComboBox..

Does that make sense?

If so can anyone help please.

Thank you.

If it helps, the Code I have so far for the UserForm is:


Private Sub UserForm_Activate()
Dim MyRange As Range
Application.ScreenUpdating = False
Sheets("Data").Select
Set MyRange = Range("A5", Range("A65536").End(xlUp))
With Me.ComboBox1
.RowSource = MyRange.Address
End With

End Sub

mvidas
11-08-2005, 08:44 AM
Hi Johnpants,

Welcome to VBAX!
Just to let you know, the tags we use for code here are [ vba ] (no spaces between [] and vba) to start the code, and [ /vba ] to close the code. I've fixed your original posting to show this.

To accomplish what you want (I'm guessing at a button_click), try the followingPrivate Sub CommandButton1_Click()
Dim FND As Range, MyRange As Range
Set MyRange = Sheets("Data").Range("A5", Sheets("Data").Range("A65536").End(xlUp))
Set FND = MyRange.Find(ComboBox1.Value, LookAt:=xlWhole)
If Not FND Is Nothing Then
FND.Offset(0, 1).Value = TextBox1.Text
End If
Set FND = Nothing
Set MyRange = Nothing
End SubJust change CommandButton1 / ComboBox1 / TextBox1 to their appropriate names (if different), you should be all set!

Let us know if you have any questions!
Matt

Bob Phillips
11-08-2005, 08:49 AM
Dim MyRange As Range

Private Sub CommandButton1_Click()
Dim iRow As Long
With Me
iRow = Application.Match(.ComboBox1.Value, MyRange, 0)
MyRange.Cells(iRow, 2).Value = .TextBox1.Text
End With
End Sub

Private Sub UserForm_Activate()
Application.ScreenUpdating = False
With Worksheets("Data")
Set MyRange = .Range("A5", .Range("A" & Rows.Count).End(xlUp))
End With
Me.ComboBox1.RowSource = MyRange.Address

End Sub

Johnpants
11-08-2005, 09:08 AM
Thank you very much, that works perectly and exactly how I wanted it.

Will use '[vba]' and '[\vba]' next time too, thanks for pointing that out.

Much appreciated.

mvidas
11-08-2005, 09:34 AM
Glad to help, and again, welcome to the board!

One last thing - if your issue has been solved, you can close this question by going to "Thread Tools" at the top, and then "Mark Thread Solved". Thanks!

Matt