Is there a better way? (1 Viewer)

[Valentine]

Your code didn't quite work but I removed all the excess and it is working kinda again. I get an error "Object Variable or with block variable not set" and that highlights the rownum = rRange.row. I think it has something to do with how my range for each month is set up Mrng variable.

Dim rRange as Object
Dim rownum as Long
Dim Mrng as Object
Dim i as Integer
Dim initDate as Date

    Do While rsLeave.EOF = False
        initDate = rsLeave![Start Date]
'        rownum = rownum + 1
'        rownum = xlApp.xlSh.WorksheetFunction.Row(rsLeave![DoD ID])
'        Set rRange = xlSh.Cells.Find(What:=rsLeave![DoD ID], After:=ActiveCell, LookIn:= _
                     xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
                     xlNext, MatchCase:=False, SearchFormat:=False)
        Set rRange = xlSh.cells.Find(What:=rsLeave![DoD ID])
        rownum = rRange.Row
        i = DateDiff("d", rsLeave![Start Date], rsLeave![End Date])
        If Month(initDate) = 1 Then
            Set Mrng = xlSh.Range("CR4", "DV210")
        ElseIf Month(initDate) = 2 Then
            Set Mrng = xlSh.Range("DW4", "EX210")
        ElseIf Month(initDate) = 3 Then
            Set Mrng = xlSh.Range("EY4", "GC210")
        ElseIf Month(initDate) = 4 Then
            Set Mrng = xlSh.Range("GD4", "HG210")
        ElseIf Month(initDate) = 5 Then
            Set Mrng = xlSh.Range("HH4", "IL210")
        ElseIf Month(initDate) = 6 Then
            Set Mrng = xlSh.Range("IM4", "JP210")
        ElseIf Month(initDate) = 7 Then
            Set Mrng = xlSh.Range("JQ4", "KU210")
        ElseIf Month(initDate) = 8 Then
            Set Mrng = xlSh.Range("KV4", "LZ210")
        ElseIf Month(initDate) = 9 Then
            Set Mrng = xlSh.Range("MA4", "ND210")
        ElseIf Month(initDate) = 10 Then
            Set Mrng = xlSh.Range("D4", "AH210")
        ElseIf Month(initDate) = 11 Then
            Set Mrng = xlSh.Range("AI4", "BL210")
        ElseIf Month(initDate) = 12 Then
            Set Mrng = xlSh.Range("BM4", "CQ210")
        End If
        With Mrng
            xlSh.cells(rownum, Day(initDate + i)).Interior.ColorIndex = 4
        End With
        rsLeave.MoveNext
    Loop
    rsLeave.Close

The code doesn't error out until after about 6 cells have been shaded.

 

[Isaac]

LookAt:=xlPart

Sure you want that?

 

[Valentine]

Yeah I know I took that part out in the actual working line.

 

[Isaac]

I would replace it with xlWhole

 

[Valentine]

yeah i did do that before i commented out that line and just made it simple with what its looking for.

 

[Isaac]

Cool. glad you got it figured

i've gotten caught before with not specifying xlPart vs. xlWhole, or worse yet, letting the macro recorder default to xlPart, and it took me some time to find out that it's not a good idea to look for ID# 14 in a list that contains 14 and 140

 

[Valentine]

yeah i read through the whole thing and reduced all the optional perameters to nothing. I only have what:=rsLeave![DoD ID]. but like i stated i think there is a problem with how I am ranging the month breakout using the if statement for Mrng.

 

[Isaac]

reduced all the optional perameters to nothing

sorry if I'm beating a dead horse I don't mean to, but this may not be the best way.

Range.Find method (Excel)

Office VBA reference topic

docs.microsoft.com

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you don't specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

If you just fail to specify parameters such as LookAt, they Default. Most native vba functions have a static default, (which could also be bad and you'd want to verify it's OK for you), but in this case it's even stranger - it defaults to what was last used, similar to the Find dialogue Ctrl+F does (which you have no control over and would never want to depend on assuming).

Just a thought. I ALWAYS specify LookIn and LookAt, and would recommend.

 

[Valentine]

adding any of the optional values I get an error "Subscript out of range"

 

[Darrell]

sorry if I'm beating a dead horse

Well I was only trying to show a method of finding the row that he was after, not trying to write his code for him..

But thanks for the feedback, I'll be more careful in future never to suggest anything.

 

[Isaac]

Well I was only trying to show a method of finding the row that he was after, not trying to write his code for him..

But thanks for the feedback, I'll be more careful in future never to suggest anything.

I judge from your timezone you are not in the US.

"Beating a dead horse" just means continuing to debate something that's dead and over and done being debated.

Thus, my comment meant that I was apologizing in advance if it seemed I was carrying on too long about something the OP probably considered closed (for better or worse). Beating a dead horse had nothing to do with you or your comment.

Aside, that is an awfully extreme reaction to me simply pointing out xlPart vs. xlWhole. It wasn't personal, just a suggestion. I realize you were just trying to help out - hope that clears things up.

 

[Valentine]

So, am I doing something wrong with trying to set the Mrng variable? Doing this will make the columns find the appropriate value.

 

[Isaac]

I've lost track of what your current code actually is.

All I'm saying is that if you have 140 in column A, and 14 in column B, and your .Find code is NOT specifying LookAt = xlWhole

Then: When you want to find ID # 14 , your FIND code will stop at column A because "14" is part of "140".

Right? Wouldn't that be bad??

 

[Valentine]

yes that part is fixed and working. Thank you, There are 2 things left that I am working on my month range in the variable Mrng and highlighting the cells for start date, start date+i, and everything in between. After doing A LOT of different things testing and trying I am still stuck, here is what I have currently:

Dim rownum as long
dim i as integer
dim Mrng as Variant
Dim rRange as object

Do While rsLeave.EOF = False
        Set rRange = xlSh.Cells.Find(What:=rsLeave![DoD ID])
        rownum = rRange.Row
        i = DateDiff("d", rsLeave![Start Date], rsLeave![End Date])
        If Month(rsLeave![Start Date]) = "1" Then
            Mrng = "CR:DV"
        ElseIf Month(rsLeave![Start Date]) = "2" Then
            Mrng = "DW:EX"
        ElseIf Month(rsLeave![Start Date]) = "3" Then
            Mrng = "EY:GC"
        ElseIf Month(rsLeave![Start Date]) = "4" Then
            Mrng = "GD:HG"
        ElseIf Month(rsLeave![Start Date]) = "5" Then
            Mrng = "HH:IL"
        ElseIf Month(rsLeave![Start Date]) = "6" Then
            Mrng = "IM:JP"
        ElseIf Month(rsLeave![Start Date]) = "7" Then
            Mrng = "JQ:KU"
        ElseIf Month(rsLeave![Start Date]) = "8" Then
            Mrng = "KV:LZ"
        ElseIf Month(rsLeave![Start Date]) = "9" Then
            Mrng = "MA:ND"
        ElseIf Month(rsLeave![Start Date]) = "10" Then
            Mrng = "D:AH"
        ElseIf Month(rsLeave![Start Date]) = "11" Then
            Mrng = "AI:BL"
        ElseIf Month(rsLeave![Start Date]) = "12" Then
            Mrng = "BM:CQ"
        End If
        With xlSh.Range(Mrng)
'            xlSh.Range("(rownum, Day(rsLeave![Start Date]))", "(rownum, Day(rsLeave![Start Date] + i))").Interior.ColorIndex = 4
            xlSh.Cells(rownum, Day(rsLeave![Start Date])).Interior.ColorIndex = 4
            xlSh.Cells(rownum, Day(rsLeave![Start Date] + i)).Interior.ColorIndex = 4
        End With
        rsLeave.MoveNext
    Loop

 

[Isaac]

This looks strange:

With xlSh.Range(Mrng)
'            xlSh.Range("(rownum, Day(rsLeave![Start Date]))", "(rownum, Day(rsLeave![Start Date] + i))").Interior.ColorIndex = 4
            xlSh.Cells(rownum, Day(rsLeave![Start Date])).Interior.ColorIndex = 4
            xlSh.Cells(rownum, Day(rsLeave![Start Date] + i)).Interior.ColorIndex = 4
        End With

If Mrng is already defined properly, and that's the range you want to color, then shouldn't your With block look more like

With xlSh.Range(Mrng)
'            .Interior.ColorIndex = 4
            .Interior.ColorIndex = 4
            .Interior.ColorIndex = 4
 End With

I can't readily understand what all the other stuff is.
But I can assert that it makes no sense to say With xlSh.Range("A properly defined range") ........ and then act upon a totally different range inside the block.
The whole purpose of a with block (which I never use, as it saves almost no typing and is harder to follow) is to not having to keep refer to the primary object inside the block.

 

[Valentine]

the first line in that with block is commented, it was just me trying to test out between function. I do not want to shade in the WHOLE Mrng just the cells that coincide with rsLeave![start Date] and i as well as in between. the Mrng variable is to get the column range of the month of rsLeave![Start Date].

So the part of the code your seeing in post #74 loops through the rsLeave recordset that has a query of every leave Start date and end date that belongs to DoD ID. the rownum variable works perfectly finding the DoD ID and pulling the row number for that ID.

i gives the difference between start and end date, this is in case leave goes past a month

the next part i think is where my issue is, I am trying to put a range to look at for each leave form. So the first leave form in the recordset is 6/5/2022 - 6/6/2022 so I want the range to be columns IM - JP because in the spreadhseet that is the section that is JUNE month.

So after the if statement i have the with range Mrng so the month is taken care of and the row is taken care of all i need now is the day. so

xlSh.Cells(rownum, Day(rsLeave![Start Date])).Interior.ColorIndex = 4

should take the Mrng range for the month and go to the row of the DoD ID and then count from the beginning of the range a number of days equal to rsLeave![Start Date] and shade that the color green.

xlSh.Cells(rownum, Day(rsLeave![Start Date] + i)).Interior.ColorIndex = 4

this takes the i variable(difference between start date and end date) adds it to start date and shades in that cell green.

I am currently working on how to shade the cells in between those 2 cells but at the present the Mrng variable is not working as I have hoped as the whole recordset starts shading in from column A to AE instead f the proper range.