VBA - zliczanie ilości wystąpień danego słowa w komórkach arkusza

Question

Cześć, moje makro ma za zadanie pobrac od uzytkownika jakies slowo i zliczyc komorki arkusza, w ktorych znajduje sie to slowo. Niestety w pewnym momencie kodu wyskakuje mi blad. Czy ktos wie czym moze byc spowodowany i co powinienem zmienic? 

Sub ilewystapienwpliku2()
Dim ws As Worksheet
Dim komorka As Range
Dim szukanawartosc As Variant
Dim wartosckomorki As Variant
Dim IleWystapienwPliku As Integer
szukanawartosc = InputBox("Wprowadz szukana wartosc: ")

For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    ActiveSheet.UsedRange.Select
    For Each komorka In Selection
        wartosckomorki = komorka.Value
        If wartosckomorki = szukanawartosc Then
            IleWystapienwPliku = IleWystapienwPliku + 1
        End If
    Next komorka
Next ws
MsgBox IleWystapienwPliku
End Sub

 

Comments

Możesz, proszę, podać treść błędu.

---

Run time error 13

Type mismatch

dzieje sie to w tej linijce kodu: "If wartosckomorki = szukanawartosc Then"

Wlasnie zauwazylem dlaczego - w pliku w niektorych komorkach mam formuly, ktore generuja blad i makro wtedy sie wysypuje. Zmodyfikowalem troche if w petli i teraz wszystko dziala.

For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    ActiveSheet.UsedRange.Select
    For Each komorka In Selection
        wartosckomorki = komorka.Value
        If IsError(wartosckomorki) Then
            GoTo label
        ElseIf wartosckomorki = szukanawartosc Then
            IleWystapienwPliku = IleWystapienwPliku + 1
        End If
label:
    Next komorka
Next ws

.Uzylem instrukcji goto, aby w momencie gdy napotyka komorke z bledem ja pomijal. Zastanawiam sie czy jest jakis inny sposob, aby w instrukcji if zawrzec cos w stylu: "if warunek then NIE ROB NIC". Wiem, ze moge podstawic tam jakas prosta czynnosc np. zaznaczenie jakiejs komorki, co nie bedzie wplywalo na cale makro, ale jest to niepotrzebna linijka kodu wtedy.

Answer 1

Nie ma sensu pisać kod warunku, w którym będzie pusta linia ("nic nie rób"), wtedy można np.: "odwrócić" warunek.  

Ja w przykładzie zastosowałem Not.

For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    ActiveSheet.UsedRange.Select
    For Each komorka In Selection
        wartosckomorki = komorka.Value
        If Not IsError(wartosckomorki) Then
            If wartosckomorki = szukanawartosc Then
               IleWystapienwPliku = IleWystapienwPliku + 1
            End If
        End If
    Next komorka
Next ws

Zapis Not IsError - w "uproszczeniu" oznacza, że wartość False z wrócona z funkcji IsError to w kodzie jest Nasze True.

Można takiego "łamańca" zrobić, ale pierwszy zapis jest bardziej "czytelny".

For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    ActiveSheet.UsedRange.Select
    For Each komorka In Selection
        wartosckomorki = komorka.Value
        If Not IsError(wartosckomorki) And wartosckomorki = szukanawartosc Then
           IleWystapienwPliku = IleWystapienwPliku + 1
        End If
    Next komorka
Next ws

można i tak

For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    ActiveSheet.UsedRange.Select
    For Each komorka In Selection
        wartosckomorki = komorka.Value
        If Not IsError(wartosckomorki) 
           And wartosckomorki = szukanawartosc Then
           IleWystapienwPliku = IleWystapienwPliku + 1
        End If
    Next komorka
Next ws

Ponieważ masz

Dim szukanawartosc As Variant
Dim wartosckomorki As Variant

Możesz spróbować użyć CVar, czyli zostaje tak jak miałeś na początku, z jedną zmianą

For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    ActiveSheet.UsedRange.Select
    For Each komorka In Selection
        wartosckomorki = CVar(komorka.Value) ' Zmiana
        If wartosckomorki = szukanawartosc Then
            IleWystapienwPliku = IleWystapienwPliku + 1
        End If
    Next komorka
Next ws

drugi wariant

Sub ilewystapienwpliku2()
  Dim ws As Worksheet
  Dim komorka As Range
  Dim szukanawartosc As Variant
  ' Dim wartosckomorki As Variant ' Do usunięcia
  Dim IleWystapienwPliku As Integer

  szukanawartosc = InputBox("Wprowadz szukana wartosc: ")
 
  For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    ActiveSheet.UsedRange.Select
      For Each komorka In Selection
        ' wartosckomorki = komorka.Value ' Do usunięcia
        If CVar(komorka.Value) = szukanawartosc Then
            IleWystapienwPliku = IleWystapienwPliku + 1
        End If
      Next komorka
  Next ws

  MsgBox IleWystapienwPliku
End Sub

 

Comments

Dziekuje za bardzo obszerna odpowiedz, dobry pomysl z tym Not, zastosowalem sie do powyzszych wskazowek :)

Procedure przerobilem sobie na funkcje, jednak w tej postaci mi ona nie dziala, poniewaz zawiera pewien blad logiczny. W momencie gdy funkcja dochodzi do komorki, w ktorej jest ona zapisana pojawia sie blad odwolania cyklicznego. Czy da sie w jakis sposob sprawic aby petla omijala komorke, w ktorej wpisana jest funkcja? Problem polega na tym, ze nigdy nie wiadomo w jakiej komorce w arkuszu zostanie wpisana ta funkcja. Poki co moja funkcja wyglada tak:

Function IleWystapienwPliku(szukanawartosc As String) As Long
Dim ws As Worksheet
Dim komorka As Range

For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    ActiveSheet.UsedRange.Select
    For Each komorka In Selection
        If Not IsError(komorka) Then
            If CVar(komorka.Value) = szukanawartosc Then
                IleWystapienwPliku = IleWystapienwPliku + 1
            End If
        End If
    Next komorka
Next ws

End Function

 

---

Błąd jest bo ten zapis w VBA

IleWystapienwPliku = IleWystapienwPliku + 1

powoduje, że funkcja zwraca wartość, a co za tym idzie, będąc w  pętli

For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    ActiveSheet.UsedRange.Select
    For Each komorka In Selection
        If Not IsError(komorka) Then
            If CVar(komorka.Value) = szukanawartosc Then
                IleWystapienwPliku = IleWystapienwPliku + 1
            End If
        End If
    Next komorka
Next ws

Stworzyłeś w pewnym sensie "wyjście" z funkcji

rozwiązaniem jest nadanie innej nazwy zmiennej "zliczającej" ilość wystąpień.

Function IleWystapienwPliku(szukanawartosc As String) As Long
  Dim ws As Worksheet
  Dim komorka As Range
  Dim IleWystapien As Integer
 
  For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    ActiveSheet.UsedRange.Select
    For Each komorka In Selection
        If Not IsError(komorka) Then
            If CVar(komorka.Value) = szukanawartosc Then
                IleWystapien = IleWystapien + 1
            End If
        End If
    Next komorka
  Next ws

  IleWystapienwPliku = IleWystapien
 
End Function

ten zapis

IleWystapienwPliku = IleWystapien

to jest odpowiednik jak np.: c++

int IleWystapienwPliku(std::string szukanawartosc)
{
   int IleWystapien;
   ...
   return IleWystapien;
}

 

---

Dodalem zmienna, ale funkcja nadal nie dziala i zawsze wyskakuje 0 i informacja o odwolaniu cyklicznym.

Function IleWystapienwPliku(szukanawartosc As String) As Long
Dim ws As Worksheet
Dim komorka As Range
Dim roboczylicznik As Long

For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    ActiveSheet.UsedRange.Select
    For Each komorka In Selection
        If Not IsError(komorka) Then
            If CVar(komorka.Value) = szukanawartosc Then
                roboczylicznik = roboczylicznik + 1
            End If
        End If
    Next komorka
Next ws
IleWystapienwPliku = roboczylicznik
End Function

Edit: Wlaczylem w opcjach odwolania cykliczne w excelu, ale to nie rozwiazalo problemu i wynik funkcji to zawsze 0 a w lewym dolnym rogu excela widze ciagle napis "Oblicza".

---

Nie mam dostępu do VBA (piszę z głowy).

tu jeszcze trzeba dopisać

If Not IsError(komorka.Value) Then

czy wartość z komórki zwraca błąd

bo w tym zapisie, komórka to obiekt Range.Cells

    For Each komorka In Selection

    Next komorka

a Range.Cells zawiera więcej "właściwości" m. in. Value

---

Pokaż kod, proszę, który wywołuję funkcję: Function IleWystapienwPliku .

 

 

BTW. znalazłem coś takiego
          Zliczanie wystąpień określonej wartości

---

Nie ma nic wiecej oprocz kodu funkcji, ktory zamiescilem w poprzednim komentarzu. Ja chce ją wywolac poprzez uzycie w arkuszu excel.

Ogolnie to zaczalem sie uczyc VBA i to jest jedno z zadan, ktore znalazlem w internecie. Wiem, ze ta funkcja dziala dosc podobnie do licz.jezeli w excelu, wiec rozwiazanie tego nie jest mi jakos strasznie potrzebne. Po prostu utknalem na tym zadaniu i bylem ciekaw rozwiazania tego problemu.