• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How To Resolve Type Mismatch Error-Set firstfound = found.Address

C

Chirag R Raval

Member
Dear Sir,

My macro stop at "Set firstfound = found.Address"-Says "Type Mismatch"
basically its just "Subtotalled Rows Formatting Macro"
is here
Code: 
Sub Loop_Find_And_Format()
Dim myrange As Range

Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range

Set sht = ActiveSheet
Set StartCell = Range("A1")

'Refresh UsedRange
  ActiveSheet.UsedRange

'Find Last Row
  LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row

'Select Range
  sht.Range("A1:J" & LastRow).Select


Const gWORD As String = "Total"
Dim found As Range
Dim firstfound As Range

With myrange
Set found = ActiveSheet.Range("myrange").Cells.Find( _
What:=gWORD, _
LookIn:=xlValues, _
lookat:=xlPart, _
MatchCase:=False)

If Not found Is Nothing Then
Set firstfound = found.Address

Do
found.Select
With ActiveCell
myrange("myrange").Rows.Select
End With
With Selection.interiro
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.799981688894314
.paterntintandshade = 0
End With
Set found = .FindNext(found)
Loop While Not found Is Nothing And found.Address <> firstfound
End If
End With
End Sub

Macro always stop at "Set firstfound = found.Address"
I already change variable as "Dim firstfound As Range" to "Ranges", "Variant", and do
all as I can but can't resolve..

actually it will be become Start Point of Loop this "first address" of loop.
& loop end if this address found again ...

Help appreciated.

Regards,
Chirag
 
Hi ,

An address is a data of type String.

Secondly , once you correct the declaration of firstfound , you need to correct the statement assigning the address of found , as follows :

firstfound = found.Address

The keyword Set has been removed , since this is used to assign references to objects.

Also , if you are coding a construct such as :

Range("myrange")

it means that myrange is a named range.

What is the meaning of a construct such as :

myrange("myrange")

Always use names for variables so that they are easily read and understood. Avoid using names which are very similar. The declaration :

Dim myrange As Range

implies that you are declaring a VBA variable called myrange , which is confusing , since as I have already mentioned , in another place , you have used myrange as a named range.

Narayan
 
Dear Sir,

I already update my code & remove "MyRange" portion as per you suggestion which careated for just selection of Range & update code , you suggestion worked ..but now macro stop at "Loop While "error shown that "Loop While Without Do" which I already declared after Code "firstfound = found.Address"

here is my modified code
Code: 
Option Explicit

Sub Loop_Find_And_Format()
Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range

Set sht = ActiveSheet
Set StartCell = Range("A1")

'Refresh UsedRange
  ActiveSheet.UsedRange

'Find Last Row
  LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row

'Select Range
  sht.Range("A1:J" & LastRow).Select

Const gWORD As String = "Total"
Dim found As Range
Dim firstfound As String

With Selection
Set found = Selection.Cells.Find( _
What:=gWORD, _
LookIn:=xlValues, _
lookat:=xlPart, _
MatchCase:=False)

If Not found Is Nothing Then
firstfound = found.Address
Do
found.Select
With ActiveCell
Range(Cells(.Row, "A"), Cells(.Row, "J")).Select
With Selection.interiror
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.799981688894314
.paterntintandshade = 0
End With
Set found = .FindNext(found)
Loop While Not found Is Nothing And found.Address <> firstfound
End If
End With
End Sub

Help will be appreciated,

Regards,
Chirag Raval
 
Hi ,

Always indent your code , so that finding the ending keywords of compound keywords becomes easier.
Code: 
Option Explicit

Sub Loop_Find_And_Format()
    Const gWORD As String = "Total"

    Dim sht As Worksheet
    Dim found As Range, StartCell As Range
    Dim firstfound As String
    Dim LastRow As Long, LastColumn As Long

    Set sht = ActiveSheet
    Set StartCell = Range("A1")

'  Refresh UsedRange
    ActiveSheet.UsedRange

'  Find Last Row
    LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row

'  Select Range
    sht.Range("A1:J" & LastRow).Select

    With Selection
        Set found = Selection.Cells.Find(What:=gWORD, _
                                          LookIn:=xlValues, _
                                          lookat:=xlPart, _
                                          MatchCase:=False)

        If Not found Is Nothing Then
            firstfound = found.Address
            Do
              found.Select
              With ActiveCell
                    Range(Cells(.Row, "A"), Cells(.Row, "J")).Select
                    With Selection.interiror
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorAccent1
                        .TintAndShade = 0.799981688894314
                        .PatternTintAndShade = 0
                    End With
              End With
              Set found = .FindNext(found)
            Loop While Not found Is Nothing And found.Address <> firstfound
        End If
    End With
End Sub
Narayan
 
Dear Sir,

thanks for Suggest "Indentation"
code till not work...
its now stay on "Run Time Error 438-Object Dose Not support This Property Or Method" on ".Pattern=xlsolid"
please help.

Regards,
Chirag
 
Dear Sir,

I Restarted Excel.. "Run TIme Error" ....Gone.

but now macro stop at "Loop While "error shown that "Loop While Without Do" which I already declared after Code "firstfound = found.Address"

Please Do needful.

Regards,
Chirag
 
Hi ,

We cannot keep on debugging like this.

Please upload your workbook complete with the data and code in it , so that members can run the code and check for its running to completion.

Narayan
 
Dear Narayan/ Deepak,
Thanks for your co-operation
I attached my file with code.
you can see that "Loop Without Do" displayed
Waiting for you favourable reply.

Regards,

Chirag
 

Attachments

  • TEST OF SUBTOTAL FORMATTING.xlsm
    53.2 KB · Views: 5
Dear Sir,

Amazing, its work...perfectly..Thabk a lot to you for give me your valuable time & Suggestion......but...I feel wonder that your added "Set StartCell = Range("A1")" not used in loop ..so what is its real use? in this macro??
After "Do" .....to........ "Loop While Not found Is Nothing And found.Address <> firstfound " there are not use "Startcell".. & Macro Successfully do its job ...How can its possible??

my problem already solved but I want to know ...if you wish to answer...
hope you reply with some hidden feature of this macro..

regards,
Chirag
 
Dear Sir,

can Macro can modify for different formatting on First Column's ("A")' Found "total" And Different formatting on second columns found ("B")??

Hope your Co-Operation..
Regards,
Chirag
 
Hi ,

I have not added the statement you mention ; that was present in the file you uploaded.

You are right that the statement does nothing , since the variable StartCell is not used anywhere else in the code.

You can remove the statement.

As far as different formatting is concerned , there is no problem in implementing it. Just upload a file where you have manually colored the different columns differently , and the code can be written to do the same.

Narayan
 
Dear Sir,

I remove from my code "Set startcell =Range ("A1")..

Different Columns means "Each Subtotalled row of Columns A, and Each Subtotalled row of Columns B should have different formatting" Not Whole Vertical Columns have different formatting...

I attached same file for your reference with your code in it you can see different formatting on each Columns Subtotal row ...with Outside Border of whole range.
(which I format manually to just sample purpose)

my requirement is that this formatting done by this macro...

hope your favourable reply..
Regards,

Chirag Raval
 

Attachments

  • TEST OF SUBTOTAL FORMATTING.xlsm
    23.2 KB · Views: 2
Dear Sir,

Amazing.. Thanks for your Valuable Code..its work like a charm...
another Extra formatting I will add in it my self...
i amaze ...its How fast you can change the situation...like your code do now...
thanks again for your co-operate.. I always trust in your this forums that helps
millions peoples...

regards,

Chirag Raval
 
Dear All

I want to just added border around this range

All as per above.......Code..
.PatternColorIndex = xlAutomatic
.Color = Theme_Color
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 1
.BorderAround _
ColorIndex:=3, Weight:=xlThick

.BorderAround _
ColorIndex:=3, Weight:=xlThick

When I try to just give borders around this range after all colour formatting
it raised error ..
"ERROR: (438) OBJECT DOSN'T SUPPORT THIS PROPERTY OR METHOD"
this raised on

please help..

Regards,
Chirag raval
 
Hi ,

Try this :
Code: 
Option Explicit

Sub Loop_Find_And_Format()
    Const gWORD As String = "Total"
    Const InnerColor = vbYellow
    Const OuterColor = vbBlue
   
    Dim Theme_Color As Long
    Dim sht As Worksheet
    Dim found As Range
    Dim firstfound As String
    Dim LastRow As Long, LastColumn As Long

    Set sht = ActiveSheet
   
'  Refresh UsedRange
  ActiveSheet.UsedRange

'  Find Last Row
  LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row

'  Select Range
  sht.Range("A1:J" & LastRow).Select

    With Selection
        .ClearFormats
        Set found = Selection.Cells.Find(What:=gWORD, _
                                          LookIn:=xlValues, _
                                          lookat:=xlPart, _
                                          MatchCase:=False)

        If Not found Is Nothing Then
            firstfound = found.Address
            Do
              found.Select
              With ActiveCell
                  If ActiveCell.Column = 2 Then Theme_Color = InnerColor Else Theme_Color = OuterColor
                  Range(Cells(.Row, "A"), Cells(.Row, "J")).Select
                  With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = Theme_Color
                        .TintAndShade = 0.799981688894314
                        .PatternTintAndShade = 0
                        Selection.BorderAround ColorIndex:=3, Weight:=xlThick
                    End With
              End With
              Set found = .FindNext(found)
            Loop While Not found Is Nothing And found.Address <> firstfound
        End If
    End With
End Sub
Narayan
 
Dear Sir,

Yes Its work....Thanks Again for your effort for me..
I always appreciate you .ORG work & you all.
& I always be thankful for your doing help to us.

Regards,
Chirag
 
You must log in or register to reply here.
Back
Top