PDA

View Full Version : locating cells



chungtinhlak
12-16-2008, 11:00 AM
I have a userform where the date is auto populated in a textbox. A combobox where the list is coming from Row A. and i also have a textbox where user will input a number.

On my excel file, i have the date going acrross in the colum and the name going down the rows.

A B C D E
12/16/08 12/17/08 12/18/08 12/19/08 .......
1. John
2. Smith
3. Joe
4. Mark

My question is, if on 12/16/08, i select Smith and input 2, how can I make it so that the result bill in cell("B2")?

Thanks

Bob Phillips
12-16-2008, 11:23 AM
Dim nRow As Long
Dim nColumn As Long

nRow = Application.Match(CLng(CDate(Me.ComboBox1.Value)), Rows("1:1"), 0)
nColumn = Application.Match(Me.TextBox1.Value, Columns("A"), 0)
Cells(nRow, nColumn).Value = TextBox2.Text

chungtinhlak
12-16-2008, 01:00 PM
nColumn = Application.Match(CLng(CDate(Me.txtdate.Value)), Rows("1:1"), 0)
nRow = Application.Match(Me.combobox1.Value, Columns("A"), 0)
Cells(nRow, nColumn).Value = UserForm1.textmissing.Text


Thanks xld, I follow through it and understand most of it, can I ask you what the why we need the CLng(Cdate?

also, what is the "0" toward the end do?

Thanks a lot xld

Bob Phillips
12-16-2008, 05:23 PM
The Cdate is required to cast the string date to a real date, the Clng casts that date to a number, which is how Excel stores date.

The ,0 says to math on unsorted data, an exact match.