ComboBox search for variable...

[Señor Rubia]

I am doing VBA for a spreadsheet at work. The idea behind this
spreadsheet is to have a UserForm that asks for a certain Month
(cmbMonth) & Day (cmbDate) and Dollar Amount (txt.EndCash). Then,
using the Month, it goes to a certain sheet (named "January",
"February", etc.) and finds the cell with the specific date already in
there, then goes 3 cells to the right to input the dollar amount
specified. So far, I have succeeded in code that specifies the sheet,
but have drawn a blank on something that can assign a variable to
cmbDate and search for it on the sheet in A6:A37.

Here's my current code if that helps out any (most of it applies to
the OK, Cancel & Clear buttons, obviously:

-----------------------------------------
Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdClear_Click()
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox"
Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub

Private Sub cmdOK_Click()

ScreenUpdating = False
Val1 = txtEndCash
TheSheet = cmbMonth.Value
Worksheets(TheSheet).Activate
ActiveSheet.Range("H3") = Val1
ScreenUpdating = True

Dim RowCount As Long
RowCount =
Worksheets("January").Range("D20").CurrentRegion.Rows.Count
With Worksheets("January").Range("D20")
.Offset(RowCount, 0).Value = Me.txtEndCash.Value
End With

If Me.cmbMonth.Value = "" Then
MsgBox "Please Enter A Month!", vbExclamation, "Date Error"
Me.cmbMonth.SetFocus
Exit Sub
End If

If Me.cmbDate.Value = "" Then
MsgBox "Please Enter A Date!", vbExclamation, "Date Error"
Me.cmbDate.SetFocus
Exit Sub
End If

If Me.txtEndCash.Value = "" Then
MsgBox "Please Enter A Dollar Amount!", vbExclamation, "Money
Error"
Me.txtEndCash.SetFocus
Exit Sub
End If

If Me.txtEndCash.Value = "0.00" Then
MsgBox "Please Enter A Dollar Amount!", vbExclamation, "Money
Error"
Me.txtEndCash.SetFocus
Exit Sub
End If

If Not IsNumeric(Me.txtEndCash.Value) Then
MsgBox "Hey, dummy! The END CASH Amount must be a NUMBER!",
vbExclamation, "Money Error"
Me.txtEndCash.SetFocus
Exit Sub
End If


End Sub



Private Sub UserForm_Click()

End Sub

 

[Dave Peterson]

Maybe you can fit this into your code:

Dim res as variant
dim myRng as range

with worksheets(TheSheet)
set myrng = .range("a6:a37")
'cmbdate.value is text.
'I'm guessing that the values in A6:A37 are really numbers
res = application.match(clng(cmbdate.value),myrng,0)
if iserror(res) then
msgbox "That date isn't on " & .name & "!"
else
myrng(res).offset(0,3).value = txtEndCash 'not txt.endcash???
end if
end with

 

[Señor Rubia]

Maybe you can fit this into your code:

Dim res as variant
dim myRng as range

with worksheets(TheSheet)
   set myrng = .range("a6:a37")
   'cmbdate.value is text.  
   'I'm guessing that the values in A6:A37 are really numbers
   res = application.match(clng(cmbdate.value),myrng,0)
   if iserror(res) then
      msgbox "That date isn't on " & .name & "!"
   else
      myrng(res).offset(0,3).value = txtEndCash 'not txt.endcash???
   end if
end with




















--

Dave Peterson- Hide quoted text -

- Show quoted text -

Seems to work good. Thanks!!