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

 

[Norie]

Chris

Is it the Set lines where the error actually occurs?

 

[shg]

You may have other problems, but you can start by fixing the typo:

....Match(name, comp_[COLOR=red]mragne[/COLOR], 0), ...

If you put Option Explicit at the top of every module, you'll never have that particular problem again.

 

[sulakvea]

nice catch on a friday night shg ... it took me 5 times to go over his line (after your suggestion) to notice the typo

 

[cthorn112]

option explicit did the trick, now I have more errors to filter through. I shall return thanks for the help/advice. What does SHG mean ? (I know in hockey its shorthanded goals but were not talking about hockey, lol.)

-Chris

 

[cthorn112]

Found it... Self Help Group

 

[cthorn112]

I believe my program is failing in this part of the code. The program runs half way through then throws error after I enter the date (which is the same format as the dates in the worksheet) 424 object required. I don't believe this is a date format issue but I could be wrong. Thanks in advance.

[B][FONT=Arial][SIZE=2][COLOR=#000000]
Dim input_date As Date
Dim comp_irange As Range
Dim comp_mrange As Range
Dim comp_mrange2 As Range 
Dim btns As String  

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 
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")
     If TypeName(Range(comp.Value)) = "Error" Then Exit Sub
     On Error Resume Next
         If Err = 0 Then
         comp = Application.WorksheetFunction.Index(comp_irange, Application.WorksheetFunction.Match(name, comp_mrange, 0), Application.WorksheetFunction.Match(input_date, comp_mrange2, 0))         Sheets("PROMOTER_TEMPLATE2").Range("D16").Value = comp
         Else: MsgBox "not found"
         End If
         On Error GoTo 0 
End If[/COLOR][/SIZE][/FONT][/B]

-Chris

 

[Norie]

What's 'comp'?

 

[cthorn112]

Norie,

My apologies for not posting this in the code.

Dim comp As Variant

-Chris

 

[Norie]

Chris

Apologies for the short message - I was on my phone.

As far as I can see 'comp' isn't an object like a range or something, so you don't need .Value for it.

That should fix the particular error you've described.

Post back if there are any other problems, and I think it might be a good idea to explain what the code is actually meant to do.

I think I know what that is but I'm not certain.