run time error 1004

[cthorn112]

hello all,

I have isolated a piece of code that seems to be giving me problems. I tried debugging with no luck. I believe the error lies in when im setting comp = . Any red flags come to mind when looking at my code ?

[B][FONT=Arial][SIZE=2][COLOR=#000000]
Sub comp()      

Dim comp_irange As Range
Dim comp_mrange As Range
Dim comp_mrange2 As Range
Dim comp As Variant   
code...... 

If IsError(comp) Then
MsgBox "match not found"
Else
Set comp_irange = Sheets("MASTER").Range("C3:AF80") 
Set comp_mrange = Sheets("MASTER").Range("A3:A80")
Set comp_mrange2 = Sheets("MASTER").Range("C1:AF1")
comp = Application.WorksheetFunction.Index(comp_irange, Application.WorksheetFunction.Match(name, comp_mragne, 0), Application.WorksheetFunction.Match(input_date, comp_mragne2, 0))     Sheets("PROMOTER_TEMPLATE2").Range("D16").Value = comp
End If[/COLOR][/SIZE][/FONT][/B]

Thanks in advance,
-Chris

 

[cthorn112]

Norie,

I went back and did some re-coding and have a working program. It might not be nice to look at but it works and seems to be error free (knock on wood). My next hurdle to overcome is having the program loop back through if the user has more dates to enter. I have set up a pop up box which will ask the user if they have more dates to enter. If the user clicks no then they are done. If the user clicks yes It would prompt them to enter more dates (no need to enter the name again, right ?). I need to figure out how to have it loop back through without writing the same code 10 times (the maximum amount of dates the user will enter is 10). Can you please let me know if this is the right way to go about it and what I need to do to make this work ?

[FONT=Arial][SIZE=2][COLOR=#000000]     Select Case choice
        Case 6
            MsgBox "Please enter the next date"
            input_date = InputBox("Please Enter Date", "Date Entered")
            Sheets("PROMOTER_TEMPLATE2").Range("B17").Value = input_date
            
            If IsError(comp) Then
            MsgBox "match not found"
            Else
            Set comp_nrange = Sheets("MASTER").Range("C3:AF80")
            Set comp_drange = Sheets("MASTER").Range("A3:A80")
            Set comp_drange2 = Sheets("MASTER").Range("C1:AF1")
            If TypeName(input_date) = "Error" Then Exit Sub
            On Error Resume Next
                If Err = 0 Then
            With Application.WorksheetFunction
                comp = .Index(comp_nrange, _
                .Match(name, comp_drange, 0), _
                .Match(CLng(input_date), comp_drange2, 0))
            End With
                Sheets("PROMOTER_TEMPLATE2").Range("D17").Value = comp
                Else: MsgBox "Error !!!"
                End If
                On Error GoTo 0
            End If
            
            If IsError(red) Then
            MsgBox "match not found"
            Else
            Set comp_nrange = Sheets("MASTER").Range("C3:AF80")
            Set comp_drange = Sheets("MASTER").Range("A3:A80")
            Set comp_drange2 = Sheets("MASTER").Range("C1:AF1").Offset(0, -1)
            If TypeName(input_date) = "Error" Then Exit Sub
            On Error Resume Next
                If Err = 0 Then
            With Application.WorksheetFunction
                red = .Index(comp_nrange, _
                .Match(name, comp_drange, 0), _
                .Match(CLng(input_date), comp_drange2, 0))
            End With
                Sheets("PROMOTER_TEMPLATE2").Range("E17").Value = red
                Else: MsgBox "Error !!!"
                End If
                On Error GoTo 0
            End If
            
        Case 7
            MsgBox "You are done"
    End Select[/COLOR][/SIZE][/FONT][B][FONT=Arial][SIZE=2][COLOR=#000000]
[/COLOR][/SIZE][/FONT][/B]

Thanks,

-Chris

 

[Norie]

Chris

This is for post #20.

You shouldn't use name (or Name) for a variable - I couldn't tell you how many objects in VBA use that as a property.

Also, why would you declare the variable for the date as a range, I though it was only one value?

I intended the variables strName and strDate to be used in place of your name, and input_date - those were the variables I used in the What argument for the 2 finds.

Anyway, it doesn't matter what the variable names are - that's really a personal thing I suppose.

So you use whatever makes sense to you but make sure you don't use something like Name which could be mistaken for an in-built property/method.

Also make sure that if you change it in one place to change it in the rest of the code.

If you add Option Explicit to the top of all your modules it'll help you with that.

PS I think you've already got that, going by the error.

As for using a userform, I think it would be worth a shot - if done properly you wouldn't need to use find or worksheet functions to get the price/cost/whatever.

It might also help if multiple dates are involved - your next post.

I'll get back to you later - got to shoot off now.

 

[cthorn112]

Norie,

Yeah rookie mistake on using variables that might be reserved.

input_date should be date like you pointed out. I made it range just for the sake of pointing out the error.

I'm always going to use option explicit and error handling as needed.

I don't want to use a form for this program as it is 90% complete. Definitely for my next project I will incorporate it into my program.

Again many thanks for the help.

Be careful, you might shoot your eye out (A Christmas Story)

Thanks,

-Chris

 

[Norie]

Chris

Well if it's working then that's the important thing, though it wouldn't be hard at all to do the userform thing.

I was even thinking about mocking something up but couldn't make up or remember enough DJ names and I don't think there's a pre-built list kicking about anywhere.

There are various ways you could do the date thing I suppose.

Perhaps allow the user to enter all the dates in one go as a delimited list.

Dim varDates As Variant
Dim input_date As Date
Dim arrDates
Dim I As Long

    varDates = InputBox("Please enter date(s):" & vbCrLf & "(Separate multiple dates with a comma)" & vbCrLf, "Enter Dates")
 
    Select Case varDates
 
        Case vbCancel
            ' cancel clicked
            Exit Sub    ' or something more appropriate

        Case Else
            arrDates = Split(varDates, ",")

    End Select
 
    For I = LBound(arrDates) To UBound(arrDates)

        input_date = DateValue(arrDates(I))

        Debug.Print input_date    ' for testing
 
        ' code for getting price

    Next I

 

[cthorn112]

Norie,

I definitely like that approach !! When I asked the user what they thought, they did not share the same amount of joy as we did (So back to the original plan).
One final question I want to return the max date after the user is done entering dates. Lets say for example they enter 4 dates (3/18/2011, 3/19/2011, 3/25/2011, and 3/26/2011). I want the max date to be returned from this fixed range (In this case 3/26/2011). I have this code set up in Case 7 (which means they have no more dates to enter so look at the range of dates and return the max date.) Nothing in being returned. Any advice or suggestions ?

Dim job_completed As Range

Case 7
     Set job_completed=Sheets("PROMOTER_TEMPLATE2").Range("B16:B21")
     job_completed = Application.WorksheetFunction.Max(job_completed)
     Sheets("PROMOTER_TEMPLATE2").Range("B12").Value = job_completed
     MsgBox "You are done"

Thanks,

-Chris

 

[Norie]

Chris

I can't think of another way apart from a userform, or what you've suggested yourself.

Input boxes aren't built for multiple inputs.

Can't realy help with the rest of the code either - I'm not very good with formulas on a worksheet never mind in code.

 

[cthorn112]

Norie,

Not a problem. You have been more then vital in helping me with my program. This is literally the last thing so after this hopefully I can wash my hands clean of this and move on.
The dates are being entered in the correct cells so that part is fine. The problem resides in looking at the range of dates, picking the max value and returning that value into the job completed cell. Once I figure it out Ill post back to this thread. Again many thanks for the help and support . I'm sure I will be on again soon.

Thanks,

-Chris