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,

After removing .value I get a run time error 1004 application defined or object-defined error (I have option explicit ON). This program is suppose to automate the invoicing process at my job. I have the user input a DJ name and date associated with that DJ. The vba script does some vlookups and a index matches based on the values that the user inputs. It pulls information from MASTER worksheet and populates the invoicing template which is then used to invoice/pay the DJ. It might sound confusing so I do apologize ahead of time. I would post my file but I don't think I can upload files to the message board.

Thanks,

-Chris

 

[Norie]

Chris

That's not confusing at all, if anything is confusing it's the approach you've taken.

How have you got things set up on the MASTER worksheet?

If you are able to use INDEX/MATCH/VLOOKUP worksheet functions to get the information you want it migh be possible to use VBA's Find method.

I've just had another look at the code and I think you've got the names down column A, specifically A3:A80.

The dates are in row 1 - C1 to AF1 and the actual data in C3:AF80.

Is that right?

 

[cthorn112]

Norie,
Yes that is correct. I did not look into the find method. I spent a couple hours researching index match and found it confusing yet effective. I will apply the find method and come back with questions.

Thanks,

-Chris

 

[cthorn112]

Norie,

From what I found on the web, I applied the find function to my scenario. This is a crude version. I know I am missing some values. Can you please take a look and see what can be done to make this work ?

        If TypeName(Range(comp)) = "Error" Then Exit Sub
            On Error Resume Next
            With Sheets("MASTER").Range("a1:af80")
                Set rFound = .find(what:=name, _
                after:=.Cells(1, 1), _
                LookIn:=xlValues, _
                lookat:=xlPart, _
                searchorder:=xlByRows, _
                searchdirection:=xlNext, _
                MatchCase:=False, _
                searchformat:=False)
        On Error GoTo 0
        If Not rFound Is Nothing Then Application.Goto rFound, True
        End With

Thanks,

-Chris

 

[Norie]

Chris

The first thing I think you need to get rid of is the If TypeName..., I'm not even sure what that's meant to do or why you are using it.

Then get rid of the On Error stuff.

That should leave you with the basics for using Find.

The only other thing I'd change would be the range you are looking in.

Just use the range that the names are in.

I can't remember that off-hand, I think it was someting like A1:A81?

Whatever it is just use that when looking for the name, and the same for the dates, was that C1:AF1 or something?

That should give you a start, and what you've actually got there looks perfectly fine apart from those minor points.

You've even got the If after the Find to check if it was succesful, a lot of people miss that or try and rely on error trapping, which isn't a good idea.

Give it another try and post back if you have any problems.

One thing you might encounter is difficulty with using Find with dates, but that can usualy be sorted.

 

[cthorn112]

Norie,

I had the If TypeName there because it was throwing an error and when I looked it up someone had said that is a workaround, same with the On Error.
As far as the range goes I'm confused what goes where. Do I put all three ranges in one value or does each range belong in a different part of the formula. This is what I have after taking out the things you pointed out.

    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")
        With Sheets("MASTER").Range("a3:a80")
            Set rFound = .Find(What:=name, _
            after:=.Cells(1, 1), _
            LookIn:=comp_mrange, _
            LookAt:=comp_mrange2, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False, _
            SearchFormat:=False)
        If Not rFound Is Nothing Then Application.Goto rFound, True
        End With
    End If

Thanks in advance,

-Chris

 

[Norie]

You've added If IsError? You shouldn't need that either.

Find won't return an error if nothing is found, what will happen is it'll return Nothing.

That's what you are checking for at the end of the code with the If Not ... Is Nothng.

Well you're actually checking for the opposite but it's the same thing really - that doesn't make any sense even to me.

I'll try again with an example.

If rFound Is Nothing Then ' check if anything has been found
    ' nothing found
     MsgBox "Not Found"
Else 
    ' something found
    Application.Goto rFound
End If

Does that make sense?

Anyway, back to what you should do.

1 Search for the name.

2 If name found continue to 3, if not inform user and stop execution.

3 Name found, so search for date.

4 If date found continue to 5, if not inform user and stop execution.

5 Return the price.

For 5 you can get the price using the intersect of the row the name is found on and the column the date is found on.

So you need to do separate finds for the name and date, if both are succesful you can return the price.

Something like this, untested, code.

Dim comp_mrange As Range    ' names
Dim comp_mrange2 As Range    ' dates
Dim rngNameFnd As Range
Dim rngDateFnd As Range
Dim rngPrice As Range
 
    Set comp_mrange = Sheets("MASTER").Range("A3:A80")
 
    Set comp_mrange2 = Sheets("MASTER").Range("C1:AF1")
 
    ' search for name
    Set rngNameFnd = comp_mrange.Find(What:=strName, After:=comp_mrange.Cells(1, 3))
    If rngNameFnd Is Nothing Then
        MsgBox "Name not found"
        Exit Sub
    End If
 
    ' search for date
    Set rngDateFnd = comp_mrange.Find(What:=strDate, After:=comp_mrange2.Cells(1, 1))
 
    If rngDateFnd Is Nothing Then
        MsgBox "Date not found"
        Exit Sub
    End If
 
    ' if we've reached here the name and date have been found
    
    ' so we can get the price
    Set rngPrice = Intersect(rngNameFnd.EntireRow, rngDateFnd.EntireColumn)

I've removed the LookIn, LookAt etc arguments because, to be honest, I can never remember what they should be myself.

They definitely aren't ranges.

Anyway the code should work without them, if it doesn'tthey can be added back withe the correct values.

 

[cthorn112]

Norie,

I came across a run time error 13 type mismatch. This is when I put the error statements in and got another error and put another error statement so the error statements would go away (lots of errors in that sentence).
Anyway I have pasted my crude/lengthy code in its entirety so you can see all of it. Sorry if its messy but again this is my first stab at VBA. Hopefully my approach isnt that off. Thanks in advance !

Option Explicit

Sub EnterName()

    Dim name As String
    Dim todays_date As Date
    Dim payment_due As Date
    Dim job_completed As Date
    Dim input_date As Date
    Dim code As Variant
    Dim desc As Variant
    Dim more_dates As String
    Dim btns As String
    Dim choice As Integer
    Dim comp_irange As Range
    Dim comp_nrange As Range ' names
    Dim comp_drange As Range ' dates
    Dim rngNameFnd As Range
    Dim rngDateFnd As Range
    Dim rngComp As Range
    Dim comp As Variant
    Dim red As String
    Dim rFound As Range
        
    name = InputBox("Please enter name", "Name entered")
    Sheets("PROMOTER_TEMPLATE2").Range("A6,B3").Value = name
    
    code = Application.WorksheetFunction.VLookup(name, Worksheets("promoters").Range("A:C"), 3, False)
    
    desc = Application.WorksheetFunction.VLookup(name, Worksheets("promoters").Range("A:B"), 2, False)
    
    If IsError(code) Then ' returns code for name
    MsgBox "match not found"
    Else
    Sheets("PROMOTER_TEMPLATE2").Range("A16").Value = code
    End If
    
    If IsError(desc) Then ' returns desc for name
    MsgBox "match not found"
    Else
    Sheets("PROMOTER_TEMPLATE2").Range("C16").Value = desc ' MsgBox "match found" or "match found at pos: " & res
    End If
    
    todays_date = DateValue(Now) ' todays date
    Sheets("PROMOTER_TEMPLATE2").Range("D6").Value = todays_date
    
    payment_due = DateValue(Now + 7) ' payment due is today's date + 7
    Sheets("PROMOTER_TEMPLATE2").Range("A12").Value = payment_due
    
    input_date = InputBox("Please enter date", "Date entered") ' input_date is used for looking up the dates in master worksheet.
    Sheets("PROMOTER_TEMPLATE2").Range("B16").Value = input_date
    
    job_completed = (input_date - 3) ' Application.Max("B16", "B17", "B18", "B19", "B20", "B21") this should return the last input date that the user types in, right now its just taking the first input_date - 3
    Sheets("PROMOTER_TEMPLATE2").Range("B12").Value = job_completed
    
    Set comp_nrange = Sheets("MASTER").Range("A3:A80")
    Set comp_drange = Sheets("MASTER").Range("C1:AF1")
        
    ' search for name
    Set rngNameFnd = comp_nrange.Find(what:=name, after:=comp_nrange.Cells(1, 3))
    If rngNameFnd Is Nothing Then
        MsgBox "name not found"
        Exit Sub
    End If
        
    ' search for date
    Set rngDateFnd = comp_drange.Find(what:=input_date, after:=comp_drange.Cells(1, 1))
    If rngDateFnd Is Nothing Then
        MsgBox "Date not found"
        Exit Sub
    End If
    
    ' if we've reached here the name and date have been found
    ' so we can get the comp value
    Set rngComp = Intersect(rngNameFnd.EntireRow, rngDateFnd.EntireColumn)
    
    If IsError(red) Then ' not in use yet need the name and input_date to be used to return the correct red value from MASTER worksheet
    MsgBox "match not found"
    Else
    red = Application.WorksheetFunction.VLookup(name, Worksheets("MASTER").Range("A:C"), 3, False)
    Sheets("PROMOTER_TEMPLATE2").Range("E16").Value = red
    End If
    
    more_dates = "Do you have more dates to enter ?" ' if the user has more dates to enter then enter the dates.
    btns = vbYesNo + more_dates + vbDefaultButton2
    choice = MsgBox(more_dates, btns) ' MsgBox choice 7 = no 6 = yes
    
    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
            desc = Application.WorksheetFunction.VLookup(name, Worksheets("promoters").Range("A:B"), 2, False)
            Sheets("PROMOTER_TEMPLATE2").Range("C17").Value = desc
            comp = Application.WorksheetFunction.Index(comp_irange, Application.WorksheetFunction.Match(name, comp_nrange, 0), Application.WorksheetFunction.Match(input_date, comp_drange, 0))
            Sheets("PROMOTER_TEMPLATE2").Range("D17").Value = comp
        Case 7
            MsgBox "You are done"
    End Select
End Sub

-Chris

 

[Norie]

Chris

Did you try the code I posted as is, on it's own?

Where are you getting the errors now?

Have you considered using a userform for this?<SUP><SUP></SUP></SUP>

 

[cthorn112]

Norie,

I tried running the code you posted "as is" but it returned some errors. I believe because I did not declare strName and strDate (which I declared as name and input_date), I declared those and even added the message boxes. Now I'm getting a new run time error 91 object variable block variable not set (this is when I have Dim input_date As Range, if I declare Dim input_date As Date I get run time error 13 type mismatch). As you can see I renamed some values (just to stay consistent with its purpose in the code). I have not considered using a userform. I'm assuming that this is the approach I should be taking . I will look into this now. Please take a look and let me know what you think.

Option Explicit

Sub Name2()

    Dim name As String
    Dim input_date As Range
    Dim comp_nrange As Range    ' names
    Dim comp_drange As Range    ' dates
    Dim rngNameFnd As Range
    Dim rngDateFnd As Range
    Dim rngComp As Range
    
    name = InputBox("Please enter name", "Name entered")
    Sheets("PROMOTER_TEMPLATE2").Range("A6,B3").Value = name
    
    input_date = InputBox("Please enter date", "Date entered") ' input_date is used for looking up the dates in master worksheet.
    Sheets("PROMOTER_TEMPLATE2").Range("B16").Value = input_date
    
    Set comp_nrange = Sheets("MASTER").Range("A3:A80")
    Set comp_drange = Sheets("MASTER").Range("C1:AF1")
        
    ' search for name
    Set rngNameFnd = comp_nrange.Find(What:=name, After:=comp_nrange.Cells(1, 3))
    If rngNameFnd Is Nothing Then
        MsgBox "name not found"
        Exit Sub
    End If
        
    ' search for date
    Set rngDateFnd = comp_drange.Find(What:=input_date, After:=comp_drange.Cells(1, 1))
    If rngDateFnd Is Nothing Then
        MsgBox "Date not found"
        Exit Sub
    End If
    
    ' if we've reached here the name and date have been found
    ' so we can get the comp value
    Set rngComp = Intersect(rngNameFnd.EntireRow, rngDateFnd.EntireColumn)

End Sub

Thanks,

-Chris