Need help with VBA formula (Run-time error '438' Object doesn't support this property or method)

Hi Experts,

I'm trying to write a formula via VBA that will examine dates in a Sheet1 Named Range (dateRange which is found at '2015'!$AR$565:$KA$565) and if the dates found there are within the two dates found by VBA VLookup formulas that look up "Trip #'s" in a Named Range (Trip_Lookup which resides at  'Trips Summary'!$A$8:$J$21) . If the dates match, I want to populate the Columns of the current ActiveCell Row with the letter "D" and the value in Column AO of the same Row.  The populateTripSchedule procedure is found at the top of Module 1.  At present, the procedure is erring on this line:

    Set hotelDates = ThisWorkbook.Sheets(1).Range("AR" & Application.WorksheetFunction.ActiveCell.Row) & ":" & ThisWorkbook.Sheets(1).Range("KA" & Application.WorksheetFunction.ActiveCell.Row)

... with a Run-time error '438' Object doesn't support this property or method.

Can someone help me get past that error?   And if that is solved, then I'm a little worried, too, about a subsequent statement (that looks like it could be problematic) :

        Set isect = Application.Intersect(ThisWorkbook.Sheets(1).Range(hotelDates), ThisWorkbook.Sheets(1).Range(dateRange) = "D" & ThisWorkbook.Sheets(1).Range("AO" & Application.WorksheetFunction.Function.Row()))

The intended result of this latter is to update the relevant matching columns with the values (for example) "D2" in the Range BV21:CD21 (So, if that isn't the result once this initial error is resolved, I'd appreciate assistance with that formula as well.

Sub populateTripSchedule()  
'Run with shortcut CTRL+T

Dim dTripStart As Date
Dim dTripEnd As Date
Dim Trip_Lookup As Range
Dim dateRange As Range
Dim hotelDates As Range
Dim c As Range
Dim isect As Range

On Error GoTo Exithere

    Set dateRange = ThisWorkbook.Names("DateRange").RefersToRange     '='2015'!$AR$565:$KA$565
    Set Trip_Lookup = ThisWorkbook.Names("Trip_Lookup").RefersToRange ' ='Trips Pricing Summary'!$A$8:$J$21
    hotelDates = ThisWorkbook.Sheets(1).Range("AR" & Application.WorksheetFunction.ActiveCell.Row) & ":" & ThisWorkbook.Sheets(1).Range("KA" & Application.WorksheetFunction.ActiveCell.Row)
   
    'Application.WorksheetFunction.ActiveCell.Row()

dTripStart = Application.WorksheetFunction.VLookup(ThisWorkbook.Sheets(1).Range("L" & Application.WorksheetFunction.Row()), ThisWorkbook.Sheets(1).Range(Trip_Lookup), 5, False)
dTripEnd = Application.WorksheetFunction.VLookup(ThisWorkbook.Sheets(1).Range("L" & Application.WorksheetFunction.Row()), ThisWorkbook.Sheets(1).Range(Trip_Lookup), 7, False) - 1

'    dTripStart = CDate()) '??
'    dTripEnd = CDate()
 
ThisWorkbook.Sheets(1).Activate

For Each c In dateRange

    If c >= dTripStart And c <= dTripEnd Then

        Set isect = Application.Intersect(ThisWorkbook.Sheets(1).Range(hotelDates), ThisWorkbook.Sheets(1).Range(dateRange) = "D" & ThisWorkbook.Sheets(1).Range("AO" & Application.WorksheetFunction.Function.Row()))

    End If
   
    DoEvents
   
Next c

Exithere:

    Set dateRange = Nothing
    Set Trip_Lookup = Nothing
    Set isect = Nothing
   
End Sub

I appreciate any insights!

Jeff

EE.xlsm
LVL 2
Jeffrey SmithOwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Jeff,

I'm lost here..Reading your macro looks too confusing to me to understand what you are trying to do... Can you walk me through which cells you want to apply formula..? What formula?? and the end what you want to do??

I can help you writing the code for the same..

Also this line gives you an error because you are declaring that as range however you are not using set statement in their what i mean is this line...

    hotelDates = ThisWorkbook.Sheets(1).Range("AR" & Application.WorksheetFunction.ActiveCell.Row) & ":" & ThisWorkbook.Sheets(1).Range("KA" & Application.WorksheetFunction.ActiveCell.Row)

Open in new window


This should be...

    Set hotelDates = ThisWorkbook.Sheets(1).Range("AR" & ActiveCell.Row) & ":" & ThisWorkbook.Sheets(1).Range("KA" & ActiveCell.Row)

Open in new window


And you can straight away use activecell.row which will refer to the row of the cell which you have selected...

Saurabh...
Jeffrey SmithOwnerAuthor Commented:
Thanks for posting Saurabh.

The code in the original (posted) workbook did have the "Set" on the hotelDates Range, but I made that change without circling back to correct same in my in-process EE question.

Nonetheless, I am still getting errors using the Set statement without the "Application.WorksheetFunction." qualifier which I thought was required when using Excel functions from VBA:  This statement:

    Set hotelDates = ThisWorkbook.Sheets(1).Range("AR" & ActiveCell.Row & ":" & ThisWorkbook.Sheets(1).Range("KA" & ActiveCell.Row))

... fails with Run-time error '1004' Application-defined or object-defined error

And this one:

    Set hotelDates = ThisWorkbook.Sheets(1).Range("AR" & ActiveCell.Row) & ":" & ThisWorkbook.Sheets(1).Range("KA" & ActiveCell.Row)

... fails with Run-time error '424' Object required.

So, I still need some help getting this syntax straight ...

Respecting:

"Can you walk me through which cells you want to apply formula..? What formula?? and the end what you want to do??"

I tried to indicate this in my original post:

"I'm trying to write a formula via VBA that will examine dates in a Sheet1 Named Range (dateRange which is found at '2015'!$AR$565:$KA$565) and if the dates found there are within the two dates found by VBA VLookup formulas that look up "Trip #'s" in a Named Range (Trip_Lookup which resides at  'Trips Summary'!$A$8:$J$21) . If the dates match, I want to populate the Columns of the current ActiveCell Row with the letter "D" and the value in Column AO of the same Row."

... and this:

"The intended result of this latter is to update the relevant matching columns with the values (for example) "D2" in the Range BV21:CD21"

To possibly better clarify this, when I said "If the dates match, I want to populate the Columns of the current ActiveCell Row with the letter "D" and the value in Column AO of the same Row", what I should have said instead of "If the dates match ..." was If the dates in the Named Range dateRange (Range: AR$565:$KA$565 on Sheet1 ('2015) are within the Looked up range ("Trip_Lookup" which is found at: 'Trips Summary'!$A$8:$J$21 [ NOTE: I actually changed the name of that Trips Pricing Summary' sheet to 'Trips Summary' while in the process of constructing my EE question and didn't circle back to update the change in the posted code (although the code in the attached WB had been corrected - sorry for the confusion]).

Via earlier code, the visible (unhidden) Columns in the AR:KA range that meet this criteria are the only ones showing in the attached file (BV:CD).  So, I would expect all of those cells on the ActiveCell.Row (21) to have the value "D2" in them.

Hope this is clear.

Jeff
Saurabh Singh TeotiaCommented:
Jeff,

My bad for not reading the line fully..The correct line will be...

 Set hotelDates = ThisWorkbook.Sheets(1).Range("AR" & ActiveCell.Row & ":KA" & ActiveCell.Row)

Open in new window


Also i'm still not clear on what you are trying to do since in trips sheets the only places where i can find dates is in the column-->E and G so not sure then why do you want to check from A Column to J Column...

It will be helpful for me to understand when you say active cell what cell you are talking here about to get answer..Also if you can upload your sample file with clearly highlighting the results you are expecting by highlighting those cells..I can write the code for you which does what you are looking for..

Saurabh...
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jeffrey SmithOwnerAuthor Commented:
So, I tried your new Set hotelDates statetment and that worked without error, but now I am getting a subsequent error on:

dTripStart = Application.WorksheetFunction.VLookup(ThisWorkbook.Sheets(1).Range("L" & ActiveCell.Row), ThisWorkbook.Sheets(1).Range(Trip_Lookup), 5, False)

Open in new window


... with the error: "Run-time error '1004' Application-defined or object-defined error" ... so, I need assistance fixing this, too ...

RE: "Also i'm still not clear on what you are trying to do since in trips sheets the only places where i can find dates is in the column-->E and G so not sure then why do you want to check from A Column to J Column..."

I use the Named Range Trip_Lookup array for other uses, too, but only the Columns E & G (Columns 5 & 7 with the dates in the Named Range) are needed for this purpose (plus the Lookup value in Column A).

RE: " It will be helpful for me to understand when you say active cell what cell you are talking here about to get answer..Also if you can upload your sample file with clearly highlighting the results you are expecting by highlighting those cells"

In the sample file previously submitted, the current Row is 21 and the Columns that should be updated are BV:CD, so the Range BV21:CD21 is where I expect the values "D2" to appear.

Hope that's clearer, Saurabh.

Thanks,

Jeff
Saurabh Singh TeotiaCommented:
Jeff this line will be...

dTripStart = Application.WorksheetFunction.VLookup(Range("L" & ActiveCell.Row), Trip_Lookup, 5, False)

Open in new window


Saurabh...
Jeffrey SmithOwnerAuthor Commented:
Hi Saurabh and thanks for sticking with me on this, especially on a weekend.  Your last suggestion worked and I applied that suggestion to the following statement for dTripEnd, too, and that worked as well (Thanks !).  Which brings me to the Intersect code which is now failing on the following several attempts to correct same (along with associated error messages):

1.

Set isect = Application.Intersect(Range("hotelDates"), Range("dateRange") = "D" & ThisWorkbook.Sheets(1).Range("AO" & ActiveCell.Row()))

VBE Error

2.

Set isect = Application.Intersect(hotelDates, dateRange) = "D" & ThisWorkbook.Sheets(1).Range("AO" & ActiveCell.Row())

VBE Error 2
Set isect = Application.Intersect(hotelDates, dateRange).Value = "D" & ThisWorkbook.Sheets(1).Range("AO" & ActiveCell.Row())

3.

Also, this snippet of the above code run in the Immediate Window produces the same error:  

?Application.Intersect(hotelDates, dateRange).Address

... so that seems to suggest that is the problematic part of the statement, but I can't see why.

4.

Set isect = Application.Intersect(hotelDates, dateRange).Value = "D" & ThisWorkbook.Sheets(1).Range("AO" & ActiveCell.Row()).Value

I started using an error handler at this point so future error messages will look a little different here on out:

VBE Error 3
I've attached my updated file as well.  I think this will be the last step in need of fixing, Saurabh, if that's any encouragement. ;-)

Thanks, again.

Jeff
Jeffrey SmithOwnerAuthor Commented:
Sorry, I messed up the last post:  The "orphaned" string of code above Step 3:

Set isect = Application.Intersect(hotelDates, dateRange).Value = "D" & ThisWorkbook.Sheets(1).Range("AO" & ActiveCell.Row())

... also produced the same error: "Error 91 (Object variable or With block variable not set) in procedure populateTripSchedule of Module Module1" (as did the following snippet in Step 3), but I neglected to include that code and embed the screenshot of the error within the Step 3.

Jeff
Jeffrey SmithOwnerAuthor Commented:
And I forgot to attach my revised WB file (attached now).

And to be clear about the errors, these were the errors (in order) produced by the 4 "Steps" in my attempts to fix the Intersect statement:

Error 1004 (Method 'Range' of object '_Global' failed) in procedure populateTripSchedule of Module Module1
Error 91 (Object variable or With block variable not set) in procedure populateTripSchedule of Module Module1
Error 91 (Object variable or With block variable not set) in procedure populateTripSchedule of Module Module1
Error 91 (Object variable or With block variable not set) in procedure populateTripSchedule of Module Module1

Jeff

EE-2.xlsm
Jeffrey SmithOwnerAuthor Commented:
I seem to be leading you astray here, Saurabh.


Jeff
EE-3.xlsm
Jeffrey SmithOwnerAuthor Commented:
Sorry, I hit enter before finishing my comment.  I had previously advised:

"In the sample file previously submitted, the current Row is 21 and the Columns that should be updated are BV:CD, so the Range BV21:CD21 is where I expect the values "D2" to appear."

... but that is wrong (I had gotten myself confused looking at the example file I submitted to EE and my actual file.  Anyway, the dates that are in the dateRange for the "15 WW2" Trip in Column L of Row 21 should have (and do, when I run the [separate] code for the 15 WW2 Trip (but I had sent the file with a different Trip [15 STP] already (pre)selected (by earlier code).  A long way to say is that the Columns that should be selected and visible (and now are in the Example 3 file in my last post) are DL21:DT21 (whose corresponding dates in dateRange are correctly defined by dTripStart and dTripEnd are 6/12/2015 - 6/20/2015.  So that part is all working now (and it was just me pointing you to the wrong columns.  The only thing left to finish is getting the selected columns to update with the value of "D" and the number in Column AO of the ActiveCell's Row.

Jeff
Jeffrey SmithOwnerAuthor Commented:
Update:

By running this in the Immediate Window:

?hotelDates.Address
$AR$21:$KA$21
?dateRange.Address
$AR$565:$KA$565

... I can see that these Ranges are not going to Intersect. Rather, it is the unhidden Columns in the AR:KA Range that need to be updated with the "D2" value for the ActiveCell's Row (in this case, Row 21).  Just need to be able to figure out how to do this ...

Jeff
Jeffrey SmithOwnerAuthor Commented:
Ok, Saurabh, with your help, I've gotten it worked out:

Sub populateTripSchedule()

Dim dTripStart As Date
Dim dTripEnd As Date
Dim Trip_Lookup As Range
Dim dateRange As Range
Dim hotelDates As Range
Dim c As Range
Dim isect As Range
Dim tripDates As Range
Dim tripDay As Range

OptimizeCode_Begin

On Error GoTo populateTripSchedule_Error

    Set dateRange = ThisWorkbook.Names("DateRange").RefersToRange     '='2015'!$AR$565:$KA$565
    Set Trip_Lookup = ThisWorkbook.Names("Trip_Lookup").RefersToRange ' ='Trips Summary'!$A$8:$J$21
    Set hotelDates = ThisWorkbook.Sheets(1).Range("AR" & ActiveCell.Row & ":KA" & ActiveCell.Row)
    
dTripStart = Application.WorksheetFunction.VLookup(Range("L" & ActiveCell.Row), Trip_Lookup, 5, False)
dTripEnd = Application.WorksheetFunction.VLookup(Range("L" & ActiveCell.Row()), Trip_Lookup, 7, False) - 1

ThisWorkbook.Sheets(1).Activate

For Each c In dateRange

    If c >= dTripStart And c <= dTripEnd Then
    
        Set tripDay = Range(Mid(c.Address, 2, 2) & ActiveCell.Row)
    
        tripDay.Value = "D" & ThisWorkbook.Sheets(1).Range("AO" & ActiveCell.Row)

    End If

    DoEvents
    
Next c

exitHere:

    Set dateRange = Nothing
    Set Trip_Lookup = Nothing
    Set isect = Nothing

    OptimizeCode_End
    
    On Error GoTo 0
    Exit Sub

populateTripSchedule_Error:

    Set dateRange = Nothing
    Set Trip_Lookup = Nothing
    Set isect = Nothing

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure populateTripSchedule of Module Module1"
    
GoTo exitHere

End Sub

Open in new window


Thanks for all your help !

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey SmithOwnerAuthor Commented:
My last post represents the final solution (the last part of which I developed myself) but would not have been arrived out without Saurabh's help (so I am giving him all the points).  I only picked my own comment as that is where the entire solution was posted.  If I'm doing this wrong, please let me know as I wouldn't want Saurabh to lose any credit for his help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.