On Jan 17, 10:49*am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> 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 wrote:
>
> > 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
>
> > --------
>
> > Thanks in advance for any help!
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -
Seems to work good. Thanks!!
|
Similar Threads
