IF THEN ELSE...

[gtd526]

Hello, Just wondering why this gives me an error.
Dim Rng As Range
Dim Rng2 As Range
'this is working because foundRng is = to Rng

Set Rng = ActiveSheet.Range("A3") 'defines date played, define date looking to match
Sheets("Schedule").Select
Set foundRng = Range("1:1").Find(Rng) 'using Row 2 to find Rng(A3)
If foundRng = Rng Then
foundRng.Select
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Copy
Sheets("Favs").Select
Range("A5").Select
ActiveSheet.Paste
Else

End If

'this is NOT working because foundRng is not = to Rng2, I want it to work when they are <>
foundRng = Nothing, because the date doesn't exist in Row 1:1 ?I assume? This is where I get the error (object variable or With block variable not set).
I just want it to End If , if the date doesn't exist otherwise do the code.

Set Rng2 = ActiveSheet.Range("A32") 'day after original, define date looking to match
Sheets("Schedule").Select
Set foundRng = Range("1:1").Find(Rng2) 'using Row 2 to find Rng2(A32)
If foundRng = Rng2 Then (error on this line)
foundRng.Select
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Copy
Sheets("Favs").Select
Range("A33").Select
ActiveSheet.Paste
Else

End If

Hopefully this makes sense.
thank you

 

[gtd526]

Hello, Just wondering why this gives me an error.
Dim Rng As Range
Dim Rng2 As Range
'this is working because foundRng is = to Rng

Set Rng = ActiveSheet.Range("A3") 'defines date played, define date looking to match
Sheets("Schedule").Select
Set foundRng = Range("1:1").Find(Rng) 'using Row 2 to find Rng(A3)
If foundRng = Rng Then
foundRng.Select
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Copy
Sheets("Favs").Select
Range("A5").Select
ActiveSheet.Paste
Else

End If

'this is NOT working because foundRng is not = to Rng2, I want it to work when they are <>
foundRng = Nothing, because the date doesn't exist in Row 1:1 ?I assume? This is where I get the error (object variable or With block variable not set).
I just want it to End If , if the date doesn't exist otherwise do the code.

Set Rng2 = ActiveSheet.Range("A32") 'day after original, define date looking to match
Sheets("Schedule").Select
Set foundRng = Range("1:1").Find(Rng2) 'using Row 2 to find Rng2(A32)
If foundRng = Rng2 Then (error on this line)
foundRng.Select
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Copy
Sheets("Favs").Select
Range("A33").Select
ActiveSheet.Paste
Else

End If

Hopefully this makes sense.
thank you

I found something that works.

If Not (foundRng Is Nothing) Then 'this is the new line
foundRng.Select
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Copy
Sheets("Favs").Select
Range("A33").Select
ActiveSheet.Paste
Else

End If

 

[joeu2004]

If Not (foundRng Is Nothing) Then 'this is the new line
[.... statements ....]
Else

End If

FYI, you do not need the ELSE statement if there are no statements after it. Simply:

If Not (foundRng Is Nothing) Then
    foundRng.Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Copy
    Sheets("Favs").Select
    Range("A33").Select
    ActiveSheet.Paste
End If

 

[jasonb75]

You don't need to select either, most things can be done by simply referring to the sheet name. Also, I don't think that there is any practical reason for setting a range to a variable if it is only being used once (as you have done with rng and rng2).

Option Explicit
Sub gtd526()
Dim rFound As Range

Set rFound = Worksheets("Schedule").Range("1:1").Find(Range("A3").Value) 'using Row 2 to find Rng(A3)
    If Not rFound Is Nothing Then Range(rFound.Offset(1), rFound.End(xlDown)).Copy Sheets("Favs").Range("A5")

Set rFound = Worksheets("Schedule").Range("1:1").Find(Range("A32").Value) 'using Row 2 to find Rng(A32)
    If Not rFound Is Nothing Then Range(rFound.Offset(1), rFound.End(xlDown)).Copy Sheets("Favs").Range("A33")
End Sub