PDA

View Full Version : Solved: What data type is this?



david000
10-04-2007, 08:05 PM
Private Sub CommandButton1_Click()

Dim r As Integer, c As Integer

r = Application.WorksheetFunction.Match(ComboBox1.Value, Range("Employees"), 0)

c = Application.WorksheetFunction.Match(MonthView1.Value, Range("Dates"), 0)

'How do you pass the Monthview controls value to the Match function?

Range("b2").Offset(r, c).Select


End Sub



For some reason I can't think of what the right answer is at the moment...

If I retrive the date off the worksheet this works fine but when I replace the range address with the ActiveX control it bugs out...

I'm just doing a super basic double lookup on a form.

geekgirlau
10-04-2007, 08:49 PM
I think you need to use MyActiveX.value

Bob Phillips
10-05-2007, 01:55 AM
What are your controls called, and what type of control?

The Combobox1 is a combobox I guess, and using Combobox1.Value should work as long as it has been selected.

You should add error handling ... in case.

david000
10-05-2007, 06:16 AM
I'm just not getting the right type: ie Format(Calendar1,value,m/d/yyyy)

to pass a value to the match function?

I would like a (any one is fine!) calendar control to do the work of selecting the date but - I can't find the trick to pass the value: Cstr(myDate) etc...

This is the Error

Error 1004
Unable to get Match Property of the worksheet function class.

rory
10-05-2007, 06:21 AM
Try converting the date to a Double.

Bob Phillips
10-05-2007, 06:41 AM
You should also add error handling, in case they select a date that you don't have, OR DON'T SELECT A NAME



Private Sub CommandButton1_Click()
Dim r As Integer, c As Integer

On Error Resume Next
r = Application.Match(ComboBox1.Value, Range("Employees"), 0)
c = Application.Match(CLng(Calendar1.Value), Range("Dates"), 0)
On Error GoTo 0

If r <> 0 And c <> 0 Then
Range("b2").Offset(r, c).Select
Else
Range("b2").Select
End If

End Sub

david000
10-05-2007, 08:29 AM
Thank you!

CLng???

I would have NEVER thought of that on my own!


So in other words

Dim d as Long

d = Calendar1.Value

Works too?

Bob Phillips
10-05-2007, 10:36 AM
Yes it does, but it doesn't reduce the need for proper error handling.