• 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.

Macro Error

akinkaraman

Member
One macro works in all excel files but it gives error in my excel file.

The error is "Run-time error '13': Type is mismatch

And debugged problem points to "If Alan.Value Like kelime Then"

This macro finds with search and it colors it.

Can you tell me why is this macro not working in my sheet and what should I do?

Code:
    Sub Bul_ve_Renklendir()
        Dim Alan As Range, say As Integer, kacinci As Integer
        Dim Seçim
        Seçim = InputBox("Seçiminizi Girin" & vbCrLf & "Bul ve Renklendir için 1" & vbCrLf & "Renklendirmeleri temizlemek için 2", "Seçiminiz", 1)
        Select Case Seçim
              Case "1"
                    kelime = InputBox("Aranacak Değeri Giriniz", "Bul ve Renklendir")
                    say = Len(kelime) - Len(Replace(kelime, "*", ""))
                    kacinci = InStr(1, kelime, "*")
                    kelime = Replace(kelime, "*", "")
                    ActiveSheet.UsedRange.Interior.Color = xlNone
                    For Each Alan In ActiveSheet.UsedRange
                        If say = 0 Then
                          kelime = kelime
                        ElseIf say = 1 And kacinci = 1 Then
                          kelime = "*" & kelime
                        ElseIf say = 1 And kacinci > 1 Then
                          kelime = kelime & "*"
                        ElseIf say > 1 Then
                          kelime = "*" & kelime & "*"
                        End If
      
                        If Alan.Value Like kelime Then Alan.Interior.ColorIndex = 6
                    Next
                    MsgBox "Bul ve Renklendir Tamamlandı !", vbInformation + vbOKOnly, "Sonuç"
              Case "2"
                    Cells.Interior.ColorIndex = 0
                    MsgBox "Renklendirmeler Temizlendi !", vbInformation + vbOKOnly, "Sonuç"
              Case Else
                    MsgBox "Herhangi bir işlem yapılmadı !", vbInformation + vbOKOnly, "Sonuç"
        End Select
End Sub
 
Last edited:
Hi ,

The variable kelime is of type String ; when this error occurs , can you press the Debug button , and check what Alan.Value contains ?

In the Immediate window , you can type in the following and press the Enter key to have the results displayed :

?Alan.Address


?Alan.Value


?TypeName(Alan.Value)

Can you post these results ?

Narayan
 
Have you tried:
Code:
If Alan.text Like kelime Then Alan.Interior.ColorIndex = 6
 
Hello Mr. Hui..Thank you, this solved the debugged problem.

I have one more problem. When I use these codes, it clears all cells's colors as well. I couldn't find a solution.

This macro allows me to find what I search and it shows those cells in yellow color but suddenly it clears all the cells's colors.

How can I prevent it?

Such as in attached file I select 1 then search TR* and it finds the cell "TRIP FUEL : (kgs)" which starts with TR however all the cell colors are being cleared.
 

Attachments

  • deneme.xlsm
    23.7 KB · Views: 6
Have you stepped through the code using F8 and watching what happens on the Excel screen?

The choice of Seçim in the Select case command both 1 and 2 have options that set the color to 0 or None

ActiveSheet.UsedRange.Interior.Color = xlNone
&
Cells.Interior.ColorIndex = 0
 
1 is for to search and then it colors the cell, 2 is for to clean the colored cells.

I use 2 after I used 1. I can not do Control Z with macros so we put second option to run it again but this time to remove back.

Sir I couldn't understand what you have meant by "Have you stepped through the code using F8 and watching what happens on the Excel screen?"

Could you explain it? My macro knowledge is not much.
 
Mr. Narayan but this time when I use 2 to remove colored cells (which I found with by using 1), it clears not only the cells which are colored by macro but also all cells are being cleared.
 
Yes. First I select 1 and search something and it finds what I searched and colors them. Second I try to use 2 to removed cells by macro but it removes all other color cells too. Selection 1 is working great but because of I can not do Control Z then I have to use Selection 2 to move back but it clears everything.
 
Hi ,

If you see , for selection 1 itself , the code has been changed so that when you exit , the original colors are restored ; you do not need to use 2 to clear the colors.

See this file.

Narayan
 

Attachments

  • deneme.xlsm
    25.1 KB · Views: 6
The problem is I can not walk in the sheet to see the colored cells now unless I click okay and when I click okay, it reverse back and I loose the colored cells that I want to find.
 
If you goto VBA (Alt+F11)
Place the cursor anywhere in the macro
Then Press F8, The macro starts but only executes a single line at a time
So you can press F8 again and step through the code

If you place the VBA window to the side you can see the excel sheet behind
That way you can see what is happening line by line in both the VBA and on the Excel sheet
 
Hi ,

If you want it like that , either you stay with the code as it is , in which case it will clear all the cells , or if you want that the original colors should be restored , then it will take some time to revise the code.

Narayan
 
When the first time I was thinking about that macro, selection 2 should have restored original colors back but it removes all colors in the sheet. But we couldn't succeed. It removes everything. 1 to find and change their colors and 2 to remove back to original. Is that possible?
 
Last edited:
If you goto VBA (Alt+F11)
Place the cursor anywhere in the macro
Then Press F8, The macro starts but only executes a single line at a time
So you can press F8 again and step through the code

If you place the VBA window to the side you can see the excel sheet behind
That way you can see what is happening line by line in both the VBA and on the Excel sheet

Woa very nice tip that I've never seen. I like that. :)
 
I wish I could have used Control Z to reverse back after I run macro so I wouldn't have needed to use selection 2. It would be easy. I thought if I put second selection may be cleared back. The macro can search *23 and finds 4576723 or search 23* and finds 23687687 easily which lets me see what I want to find. But reversing was the problem so everytime I was closing the sheet without saving and reopen it. So maybe Selection 2 can help me to see the search and continue on working.
 
Hi ,

CTRL Z works only when you are using the worksheet , working with Excel , not using VBA.

Once you use VBA , and modify a worksheet cell , then the UNDO facility is no longer available. You can no longer use CTRL Z.

It is possible to rewrite the code so that you can see all the cells , and then exit ; you will have to wait for some time.

Narayan
 
Hi ,

I have already commented out the code relating to selection 2 ; thus even if you select 2 , nothing will happen.

Narayan
 
Back
Top