Type Mismatch error question

[julhs]

I need help to resolve the “Type Mismatch” error.
Posting this is a bit of a cop out!! But having spent last few hours trying to solve it myself, I turn to my trusted source for help!!
Code is this:-

Sub MeUsedRangetest()
Dim lastRow As Long, firstrow As Long
Dim rng As Range
Dim sht As Worksheet
       Set sht = ThisWorkbook.ActiveSheet
       Set rng = sht.Range("T:T").Find(what:="CF rules", LookIn:=xlValues, LookAt:=xlWhole).Row
       Set rng = sht.Range("T:T").Find(what:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole).Row

          firstrow = sht.Range("T:T").Find(what:="CF rules", LookIn:=xlValues, LookAt:=xlWhole).Row
          lastRow = sht.Range("T:T").Find(what:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole).Row

      MsgBox "Range is " & rng.Address

End Sub

Getting Compile error = Type Mismatch, on this line:

Set rng = sht.Range("T:T").Find(what:="CF rules", LookIn:=xlValues, LookAt:=xlWhole).Row

 

[vtito_site]

I hope you need something like this:

Sub MeUsedRangetest()
Dim lastRow As Long, firstrow As Long
Dim rng As Range
Dim sht As Worksheet
       Set sht = ThisWorkbook.ActiveSheet
       firstrow = sht.Range("T:T").Find(what:="CF rules", LookIn:=xlValues, LookAt:=xlWhole).Row
       lastRow = sht.Range("T:T").Find(what:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole).Row
       Set rng = sht.Range("T" & firstrow & ":T" & lastRow)
       MsgBox "Range is " & rng.Address

End Sub

 

[shinigamilight]

Set rng = sht.Range("T:T").Find(what:="CF rules", LookIn:=xlValues, LookAt:=xlWhole).Row
       Set rng = sht.Range("T:T").Find(what:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole).Row

remove .row from these 2 lines

 

[6StringJazzer]

Type Mismatch is a runtime error, not a compile error.

The result of the expression on the right side of the "=" is the row number of the cell that is found. You are trying to assign a number to rng, which is a Range.

If you drop ".Row" this will work by setting rng to the cell that was found.

 

[6StringJazzer]

Also you are setting rng twice in a row, so the first one is thrown away. It would help for you to describe what you want this code to do.

 

[julhs]

Shingamilight
Removing the .row, results in “Run time Error 91, Object variable or With block not set”

Jeff
I’m trying to return to a message box the range coordinates of Col T, between;

firstrow = sht.Range("T:T").Find(what:="CF rules", LookIn:=xlValues, LookAt:=xlWhole).Row

and

lastRow = sht.Range("T:T").Find(what:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole).Row

 

[vtito_site]

...

and what can you say about my proposal? After all, my code does exactly that: "I’m trying to return to a message box the range coordinates of Col T, between;"

 

[julhs]

Sorry vtito, but your suggestion is exactly what I posted, so same Mismatch error

 

[vtito_site]

Sorry vtito, but your suggestion is exactly what I posted, so same Mismatch error

But absolutely not
This rows deleted:
Set rng = sht.Range("T:T").Find(what:="CF rules", LookIn:=xlValues, LookAt:=xlWhole).Row
Set rng = sht.Range("T:T").Find(what:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole).Row
This row added:
Set rng = sht.Range("T" & firstrow & ":T" & lastRow)
Just copy and try my code.

 

[shinigamilight]

Try this

Sub MeUsedRangetest()
Dim lastRow As Long, firstrow As Long
Dim rng As Long
Dim sht As Worksheet
Set sht = ThisWorkbook.ActiveSheet
        firstrow = sht.Range("T:T").Find(what:="CF rules", LookIn:=xlValues, LookAt:=xlWhole).Row
        lastRow = sht.Range("T:T").Find(what:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole).Row

         
      MsgBox "First row is " & firstrow & vbNewLine & "Last row is " & lastRow
     

End Sub