IF loop Set(s) value for Range: How to set it if = Nothing

T

TheRookie

New Member
Joined
Dec 22, 2018
Messages
12
I have a column of dates/times. Then, within UserForm, Range looks for a value > BeginDate1. Everything works great (right until the error). What I am trying to do is set the value if there is nothing found in the loop. rng1st & rng2nd will then set posted data. When there is .Cell > BeginDate1, all is good. If this value is not found, it needs to set to BeginDate1. The code below produces type mismatch.

For xRw = 2 To LastRow
If .Cells(xRw, "B") > BeginDate1 Then
Set rng1st = .Cells(xRw, "B")
Exit For
End If
ElseIf rng1st = "" Then
Set rng1st = BeginDate1

Next


For xRw = LastRow To 2 Step -1
If .Cells(xRw, "B") < EndDate1 Then
Set rng2nd = .Cells(xRw, "B")
Exit For
End If
Next

If I don't set this value when "Nothing" is found then the code below throws error: Object defined Error

For Each NewCll In .Range(rng1st, rng2nd)
MsgBoxString1 = MsgBoxString1 & NewCll.Offset(, -1).Value & " " & NewCll.Offset(, 1).Value & vbNewLine
ActiveCell.Offset(1, 0).Activate
ActiveCell = NewCll.Value
ActiveCell.Offset(0, 1).Value = NewCll.Offset(0, -1)
ActiveCell.Offset(0, 2).Value = NewCll.Offset(0, 1)

Next



I never ask until I've racked my brain. Any help GREATLY appreciated!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Hi,
I would have thought an Else Without If error would have shown as you ended your IF statement on the previous line.

Your variable rng1st I assume is declared as range & if the test in your loop returns false and you want to ensure your variable is Set Nothing then try writing it this way

Code: 
For xRw = 2 To LastRow
    If .Cells(xRw, "B") > BeginDate1 Then
        Set rng1st = .Cells(xRw, "B")
        Exit For
    Else
        Set rng1st = Nothing
    End If
Next

If though all you are trying to do is to find the earliest & latest dates in your range then you should be able to use worksheet functions Min & Max which would avoid the looping codes

Untested but perhaps something like following will help you

Code: 
 Dim rng As Range, Cell As Range    
  Dim BeginDate1 As Date, EndDate1 As Date
    
    Set rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
    
    BeginDate1 = WorksheetFunction.Min(rng)
    
    EndDate1 = WorksheetFunction.Max(rng)
    
    For Each Cell In rng
        With Cell
            If IsDate(.Value) Then
                If .Value > BeginDate1 And .Value < EndDate1 Then
                    msgboxString1 = msgboxString1 & .Offset(, -1).Value & " " & .Offset(, 1).Value & vbNewLine
                End If
            End If
        End With
    Next Cell

code is a suggestion only & will need to be developed to meet your specific project need.

Hope Helpful

Dave
 
Last edited:
Upvote 0
Thanks Dave. I failed to clearly explain. The column of dates/times goes back months, with several daily entries. I'm avoiding the min/max because that would select everything in the column. The BeginDate1 and EndDate1 is setup to form a 24 hour window for a report. For example BeginDate1 is set to yestarday 6am, and EndDate1 is set to today 6am. It works great as long as both variables find a value.

Rich (BB code): 
'Report Loop
For Each NewCll In .Range(rng1st, rng2nd)
MsgBoxString1 = MsgBoxString1 & NewCll.Offset(, -1).Value & " " & NewCll.Offset(, 1).Value & vbNewLine
ActiveCell.Offset(1, 0).Activate
ActiveCell = NewCll.Value
ActiveCell.Offset(0, 1).Value = NewCll.Offset(0, -1)
ActiveCell.Offset(0, 2).Value = NewCll.Offset(0, 1)

Next

The problem occurs when the last column date/time entry falls before BeginDate1, say yesterday at 4am. So now rng1st value = Nothing. When the code gets to the report loop, it faults because the rng1st = Nothing. I tried a variation of your suggestion as follows:
Rich (BB code): 
 For xRw = 2 To LastRow
            If .Cells(xRw, "B") > BeginDate1 Then
                Set rng1st = .Cells(xRw, "B")
                Exit For
            Else: Set rng1st = BeginDate1
            End If


            Next

I got another type mismatch.

What I am failing to do in that case is Set the rng1st to yesterday 6am (BeginDate1). I tried the following just before the report loop:

If rng1st = Nothing Then
Set rng1st = BeginDate1.

Got another type mismatch. rng1st and 2nd are dim'd as Range. BeginDate and EndDate are dim'd as double. Would you suggest new dim types?

Thanks again.
TheRookie@4AM
 
Upvote 0
TheRookie said:
If rng1st = Nothing Then
Set rng1st = BeginDate1.

Got another type mismatch. rng1st and 2nd are dim'd as Range. BeginDate and EndDate are dim'd as double. Would you suggest new dim types?

Thanks again.
TheRookie@4AM
Click to expand...

Think you have answered your own question - you are attempting to Set a Range Object variable to another data type hence the error.

Perhaps if you published all your code I or others here may be able to assist you further.

Dave
 
Upvote 0
I hadn't thought the data types would mismatch because the original rng1st & rng2nd loops set to the same type data located in "B". I managed a work around based on your suggestion... it worked but created a new problem (I guess that means it didn't work). Using your suggestion for the Else without If, I only needed to declare the entire path to the date I wanted to set IF = Nothing. Below is the entire code of this user form, and the values that are being retrieved. 'remarks' are left in, so you can see my trip-ups.

Rich (BB code): 
Private Sub CommandButton4_Click()


    Dim rng1st As Range, rng2nd As Range
    Dim StateTime1 As Variant
    Dim BeginDate1 As Double, MsgBoxString1 As String
    Dim EndDate1 As Double, LastRow As Long
    Dim xRw As Long, cll As Range, NewCll As Range
    
Worksheets("Washer_S25").Activate
ActiveSheet.Range("N2").Activate
    With Worksheets("Washer_S25")
BeginDate1 = .Range("L8")
        EndDate1 = .Range("L9")


        LastRow = .Cells(Rows.Count, "B").End(xlUp).Row


'>>>>>>>>>>>
'Look up last rng1st
'>>>>>>>>>>>>
        
        For xRw = 2 To LastRow
            If .Cells(xRw, "B") > BeginDate1 Then
                Set rng1st = .Cells(xRw, "B")
                Exit For
            Else: Set rng1st = Worksheets("Washer_S25").Range("L11")
            
            End If


            Next
MsgBox (rng1st)


        For xRw = LastRow To 2 Step -1
            If .Cells(xRw, "B") < EndDate1 Then
                Set rng2nd = .Cells(xRw, "B")
                Exit For
            End If
        Next
'If rng1st Is Nothing Then
'Set rng1st = BeginDate1

'Report Loop
        For Each NewCll In .Range(rng1st, rng2nd)
            MsgBoxString1 = MsgBoxString1 & NewCll.Offset(, -1).Value & " " & NewCll.Offset(, 1).Value & vbNewLine
            'ActiveCell.Offset(1, 0).Activate
            'ActiveCell = NewCll.Value
            'ActiveCell.Offset(0, 1).Value = NewCll.Offset(0, -1)
            'ActiveCell.Offset(0, 2).Value = NewCll.Offset(0, 1)
                                    
        Next
MsgBox (rng2nd)




    End With
Worksheets("Washer_S25").Range("T3").Activate
ActiveCell.Value = EndDate1 - rng2nd


Worksheets("MAP.Click to open form.S2").Activate
ActiveSheet.Range("A1").Activate


   MsgBox MsgBoxString1
MsgBox ("Uptime was " & "100%")
   
End Sub

"L11" above is set to retrieve the 2nd to last value in column B. So now, BeginDate1 is 12/28/18 5:45, and EndDate1 is 12/28/18 5:50, out of the column data below. This is A,B,C,D on worksheet named "Washer_S25"
Rich (BB code): 
[TABLE="width: 686"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]UM-Notif[/TD]
[TD="align: right"]12/24/18 5:50[/TD]
[TD="align: right"]0:00:06[/TD]
[TD] No hot water[/TD]
[TD]KG[/TD]
[/TR]
[TR]
[TD]UM-Maint[/TD]
[TD="align: right"]12/24/18 5:50[/TD]
[TD="align: right"]0:00:05[/TD]
[TD][/TD]
[TD]KG[/TD]
[/TR]
[TR]
[TD]UM-Comp[/TD]
[TD="align: right"]12/24/18 5:50[/TD]
[TD="align: right"]9:28:56 [/TD]
[TD]Replaced overload for heater[/TD]
[TD]KG[/TD]
[/TR]
[TR]
[TD]Qualify[/TD]
[TD="align: right"]12/24/18 15:19[/TD]
[TD="align: right"]0:00:05[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Running[/TD]
[TD="align: right"]12/24/18 15:19[/TD]
[TD="align: right"]4:34:04[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Running[/TD]
[TD="align: right"]12/24/18 19:53[/TD]
[TD="align: right"]5:48:09[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Idle[/TD]
[TD="align: right"]12/25/18 1:41[/TD]
[TD="align: right"]0:28:48[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Running[/TD]
[TD="align: right"]12/25/18 2:10[/TD]
[TD="align: right"]2:18:46[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ChangeOver[/TD]
[TD="align: right"]12/25/18 4:29[/TD]
[TD="align: right"]0:08:54[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Qualify[/TD]
[TD="align: right"]12/25/18 4:38[/TD]
[TD="align: right"]0:10:04[/TD]
[TD][/TD]
[TD]SS[/TD]
[/TR]
[TR]
[TD]Running[/TD]
[TD="align: right"]12/25/18 4:48[/TD]
[TD="align: right"]1:02:03[/TD]
[TD][/TD]
[TD]SS[/TD]
[/TR]
[TR]
[TD]UM-Notif[/TD]
[TD="align: right"]12/25/18 5:50[/TD]
[TD="align: right"]0:03:06 [/TD]
[TD]No hot water[/TD]
[TD]KG[/TD]
[/TR]
[TR]
[TD]UM-Maint[/TD]
[TD="align: right"]12/25/18 5:53[/TD]
[TD="align: right"]0:06:05[/TD]
[TD][/TD]
[TD]KG[/TD]
[/TR]
[TR]
[TD]UM-Comp[/TD]
[TD="align: right"]12/25/18 5:59[/TD]
[TD="align: right"]11:27:56[/TD]
[TD] Replaced overload for heater[/TD]
[TD]KG[/TD]
[/TR]
[TR]
[TD]Qualify[/TD]
[TD="align: right"]12/25/18 17:27[/TD]
[TD="align: right"]0:02:05[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Running[/TD]
[TD="align: right"]12/25/18 17:29[/TD]
[TD="align: right"]13:10:15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Running[/TD]
[TD="align: right"]12/26/18 6:39[/TD]
[TD="align: right"]12:03:46[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Idle[/TD]
[TD="align: right"]12/26/18 18:43[/TD]
[TD="align: right"]2:00:07[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Running[/TD]
[TD="align: right"]12/26/18 20:43[/TD]
[TD="align: right"]9:01:40[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Idle[/TD]
[TD="align: right"]12/27/18 5:45[/TD]
[TD="align: right"]0:05:08[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ChangeOver[/TD]
[TD="align: right"]12/27/18 5:50[/TD]
[TD="align: right"]0:15:08[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Qualify[/TD]
[TD="align: right"]12/27/18 6:05[/TD]
[TD="align: right"]0:40:04[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Running[/TD]
[TD="align: right"]12/27/18 6:45[/TD]
[TD="align: right"]22:59:40[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Idle[/TD]
[TD="align: right"]12/28/18 5:45[/TD]
[TD="align: right"]0:05:08[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ChangeOver[/TD]
[TD="align: right"]12/28/18 5:50[/TD]
[TD="align: right"]0:15:08[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
All I should be getting now is the Idle and ChangeOver data. But the Report Loop is grabbing all sorts of data from the offsets in it. I should have posted the entire code to begin with, but always think that it's overkill and confusing. But for RockStars, it's the beat that makes the music go.

Normally, the report would retrieve and enter the "Idle" then the Date/Time, then the Elapsed Time. Column C & D are frequently blank and not needed for the report.
THANK YOU for being an early riser (and Happy New Too).
 
Upvote 0
Solved. Not sure why I continue to be amazed at how easy it is to drive off a cliff with only 3 wheels. Thank you for your help, because it guided me to the solution. The type mismatch should have been my first clue. I still don;t get why VB was okay setting the rng to one cell value with a date/time, but not another. I used the line below and it worked:

Else: Set rng1st = .Cells(LastRow - 2, "B")

The good nature of individuals on this forum is refreshing.
 
Upvote 0
TheRookie said:
Solved.
Else: Set rng1st = .Cells(LastRow - 2, "B")

The good nature of individuals on this forum is refreshing.
Click to expand...

Glad you resolved & happy to have been of some assistance

In-between cooking meal before guests before they arrive I have sketched following out which you may want to try

Code: 
Private Sub CommandButton4_Click()


    Dim BeginDate1 As Double, EndDate1 As Double
    Dim rng As Range, cell As Range, c As Range, DateRange As Range
    Dim MsgBoxString1 As String
    Dim wsWasherS25 As Worksheet
    
    Set wsWasherS25 = ThisWorkbook.Worksheets("Washer_S25")
    
    With wsWasherS25
        BeginDate1 = .Range("L8")
        EndDate1 = .Range("L9")
        Set DateRange = .Range(.Range("B2"), .Range("B" & .Rows.Count).End(xlUp))
    End With


    For Each c In DateRange.Cells
        If IsDate(c.Value) Then
            If rng Is Nothing Then
                If c.Value > BeginDate1 Then Set rng = c
            Else
                If c.Value < EndDate1 Then Set rng = Union(rng, c)
            End If
        End If
    Next c
    
'Report Loop
    For Each cell In rng.Cells
        MsgBoxString1 = MsgBoxString1 & cell.Offset(, -1).Value & " " & cell.Offset(, 1).Value & vbNewLine
    Next cell


End Sub

Idea untested but should if works, build a range of cells that meet your criteria.
Hopefully may be of some help.

Dave
 
Last edited:
Upvote 0
I'm not surprised. Meaning that I tend to find highly intelligent people are generally early risers, good cooks, and gracious hosts! Something of a self-serving compliment to you Dave, as I just finished feeding the crew a holiday breakfast of Eggs Benedict and honey pecans.

I'll try the test code after the *******'s clean.

Thanks again. Happy, healthy, safe, and prosperous New Year to you and yours.

Shawn
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Question Question
VBA Search Functionality Issue with Arabic Supplier Names in Excel Help Needed???
Replies
1
Views
41
Peter_SSs
Peter_SSs
T
  • Solved
Setting up Two Pie charts in VBA
Replies
4
Views
207
TheTiredEngineer
T
I
  • Question Question
Listbox columns dont alter when i set the width advice please
Replies
7
Views
140
Dan_W
D
lojanica
  • Question Question
How to execute VBA every 10 minutes?
2 3 4
Replies
33
Views
439
Logit
Logit
B
  • Solved
More Efficient Looping
2
Replies
19
Views
244
bhsoundman
B

Forum statistics

Threads
1,224,825
Messages
6,181,190
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top