[Dakkar]

Cześć. Czy jakiś specjalista mógłby mi wytłumaczyć czemu to makro zwraca czasem błąd przy przeliczaniu wartośći (wtedy kiedy nic się nie zmieniło w danym zakresie ale czego rozumiem, nie za każdym razem):

Private Sub Worksheet_Calculate()
'Updateby Extendoffice
 Dim Xrg As Range
 Set Xrg = Range("C2:C8")
 If Not Intersect(Xrg, Range("C2:C8")) Is Nothing Then
 Macro1
 End If
End Sub

Nie jestem informatykiem i dopiero poznaje zasady działania makr w excelu dlatego prosiłbym łopatologicznie. Czy da się temu jakoś zaradzić?

I jeszcze jedno. Chciałbym przypisać niektóre makra do przycisków. Przypisuje je, po czym kiedy zmienie nazwe pliku na którym pracuje, makro przestaje działać. Wywala błąd z którego wynika, że makro to odnosi się do starej nazwy pliku, chociaż w samym kodzie nie ma żadnych odniesień do nazw plików. To samo dzieje się kiedy przypisze makro do wstążki.

Czy jedynym rozwiązaniem jest ustawienie danego makra, jako makra osobistego?
#programowanie
#excel

[brak_nicku]

@Dakkar: jak usuniesz linijkę

Macro1

To przestanie zwracać błąd, konkretniejsza wskazówka wymaga:
- pokazania co zawiera Macro1
- podania jaki to błąd i która linia kodu go powoduje - co zwykle jest doskonale widoczne w momencie wystąpienia błędu

[Dakkar]

@brak_nicku: Błąd powstaje po wykonaniu tej linii kodu (więc nie odnosi się w ogóle do tego co wrzucam później w treść Macro 1)

If Not Intersect(Xrg, Range("C2:C8")) Is Nothing Then

[brak_nicku]

@Dakkar: a można uprzejmie prosić o treść błędu? Bo z aktualnych informacji wynika:
- ta linia kodu biorąc pod uwagę poprzednią jest kompletnie bez sensu - możesz bez zmiany działania zastąpić to:

Private Sub Worksheet_Calculate()
Macro1
End Sub

Nadal wiadomo, że problem jest w Macro1, być może powoduje nieskończone wywoływanie Worksheet_Calculate i może pomóc dodanie:

Application.EnableEvents = False

Na początku, ale to wszystko zgadywanka.

[Dakkar]

@brak_nicku: Poniżej cały kod

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Dim Xrg As Range
Set Xrg = Range("B3:B100")
If Not Intersect(Xrg, Range("B3:B100")) Is Nothing Then
Range("C3:E100").Select
ActiveWorkbook.Worksheets("Pomocnicza").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Pomocnicza").Sort.SortFields.Add Key:=Range( _
"C3:C100"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Pomocnicza").Sort.SortFields.Add Key:=Range( _
"D3:D100"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Pomocnicza").Sort
.SetRange Range("C2:E100")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("B3").Select
ActiveCell.FormulaR1C1 = "=IF(OR(RC[1]="""",RC[2]=""""),"""",RC[1]&RC[2])"
Selection.Copy
ActiveCell.Offset(0, 1).Range("A1").Select

[brak_nicku]

@Dakkar: czyli EnableEvents tam jest. Dwukrotnie pytałem jaki to błąd, nadal nie doczekałem się odpowiedzi, więc już daruję sobie dociekanie, nie chcę nawet wiedzieć co by było, gdybym zapytał czemu ma to służyć.

[Dakkar]

@brak_nicku: Metoda Select z klasy Range nie powiodła się

[brak_nicku]

Metoda Select z klasy Range nie powiodła się

@Dakkar: OK, to już zawęża możliwości. Błąd ten zazwyczaj spowodowany jest, gdy polecenie typu:

Range("B3").Select

Odnosi się do skoroszytu, który w danej chwili nie jest aktywny. Widzę tylko sam kod, więc mogę tylko zgadywać:
- kod jest umieszczony w module arkusza o nazwie Pomocnicza
- zatem każde polecenie typu Range("B3").Select odnosi się właśnie do tego arkusza
- jeśli zdarzenie Calculate arkusza Pomocnicza zostanie

[Dakkar]

@brak_nicku:
Dzięki. Ten przykład z powyżej wygląda na sensowny ale jak widzisz w samym kodzie dość często przewija się funkcja select i chyba dość trudno będzie mi się jej pozbyć.

Czy to powinno wyglądać tak?

 Range("C3:E100").Worksheets("Pomocnicza").Sort.SortFields.Clear

Zamiast:

 Range("C3:E100").Select
 ActiveWorkbook.Worksheets("Pomocnicza").Sort.SortFields.Clear

Nie bardzo wiem też jak poradzić sobie z tą częścią:

 Selection.Copy
 ActiveCell.Offset(0, 1).Range("A1").Select
 Selection.End(xlDown).Select
 ActiveCell.Offset(0, -1).Range("A1").Select
 ActiveSheet.Paste
 ActiveCell.Offset(1, 0).Range("A1").Select
 Range(Selection, Selection.End(xlDown)).Select
 Selection.ClearContents
 Selection.End(xlUp).Select
 Selection.Copy
 Selection.End(xlUp).Select
 ActiveCell.Offset(1, 0).Range("A1").Select
 Range(Selection, Selection.End(xlDown)).Select

[brak_nicku]

@Dakkar:

Czy to powinno wyglądać tak?

Range("C3:E100").Worksheets("Pomocnicza").Sort.SortFields.Clear

Niestety nie...

ActiveWorkbook.Worksheets("Pomocnicza").Sort.SortFields.Clear

Nie wymaga zmiany - prawie. ActiveWorkbook lepiej zastąpić ThisWorkbook, czyli ten skoroszyt, w którym znajduje się kod VBA, a nie ten, który jest aktywny.
Polecenie Range("C3:E100").Select nie jest tam w ogóle do niczego potrzebne i można je spokojnie usunąć, bo kolejne polecenie dodające pole sortowania zawiera adres.

Selection.Copy

Zastąpisz

Range("B3").Copy

(B3 nadal jest aktywną komórką).

1*) To:

ActiveCell.Offset(0, 1).Range("A1").Select
Selection.End(xlDown).Select

[brak_nicku]

Range("B3").Select
ActiveCell.FormulaR1C1 = "=IF(OR(RC[1]="""",RC[2]=""""),"""",RC[1]&RC[2])"
Selection.Copy
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Selection.End(xlUp).Select
Selection.Copy
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

@Dakkar: moim zdaniem to co wyżej, jest równoznaczne z:

Set Xrg = Range("C3").End(xlDown).Offset(0, -1)
Range(Range("B3"), Xrg).FormulaR1C1 = "=IF(OR(RC[1]="""",RC[2]=""""),"""",RC[1]&RC[2])"
Range(Xrg.Offset(1, 0), Xrg.Offset(1, 0).End(xlDown)).ClearContents

Dalej jest Calculate i kolejny zbędny Select. Jak napisałem wyżej - to tylko zgadywanie.

[Dakkar]

@brak_nicku: Wygląda na to, że jesteś całkiem dobry w zgadywaniu, bo dokładnie o to chodziło. Chciałem aby dane z kolumny B zostały automatycznie posortowane od A do Z, kiedy wprowadzi się nowe wartości w pozostałe kolumny. Jednocześnie w kolumnie B nie mogły znaleźć się żadne formuły, nawet takie których wynikiem była pusta komórka, jeżeli w kolumnach C i D nie było danych.

Wszystko te warunki musiały być spełnione, żeby zastosować trick

[brak_nicku]

@Dakkar: Cieszę się, że działa. Korzystanie z wiedzy Charlesa Williamsa to dobry pomysł, zwłaszcza jeśli ktoś ma zamiar sporo używać VBA. Jego blog https://fastexcel.wordpress.com to kopalnia cennych porad.

Pobrałem plik, co prawda jest komunikat, że jest uszkodzony, ale chyba nic ważnego nie zginęło. Na pewno:

Set Xrg = Range("B3:B100")
 If Not Intersect(Xrg, Range("B3:B100")) Is Nothing Then

I

End if

Są niepotrzebne. Moim zdaniem całość niepotrzebnie jest w zdarzeniu Calculate zamiast Change

[Dakkar]

@brak_nicku: Co do tej części kodu, to znalazłem ją w necie i po prostu przekopiowałem. Ktoś inny też zastanawiał się po co ona jest i znalazłem do tego takie wytłumaczenie:

Intersect(zakres1, zakres2) zwraca zakres będący częścią wspólną zakresów zakres1 i zakres2

Czyli:

Intersect("a1:a10","a1:b3")

zwróci zakres

"a1:a3"

Idąc dalej:

If Intersect(zakres1, zakres2) Is Nothing then instrukcje

oznacza:

jeśli zakres1 i zakres2 nie pokrywają się

[brak_nicku]

@Dakkar: ale ja doskonale wiem, co oznacza Intersect, tylko w kodzie, który masz, jest warunek:

- jeśli zakres "B3:B100" ma wspólne komórki z "B3:B100" to wykonaj następne polecenia

[brak_nicku]

Całość jest w zdarzeniu Calculate, bo kiedy było w zdarzeniu Change było to niewygodne

Moim zdaniem teraz jest niewygodne, w zdarzeniu Change powinno działać lepiej, tylko wtedy trzeba testować, czy zmiana dotyczy kolumn C:D

[brak_nicku]

Filtrujesz najpierw kolumne B żeby pokazywała tylko 1, a później kolumne B żeby pokazywała tylko SPN2. Następnie na tych odfiltrowanych danych chcesz zamienić wszystkie SPN2 na SPN1 <--- i to chciałbym zrobić za pomocą makra

Znowu zgadywanki, bo nie wiadomo:
- czy kryteria filtrowania wybiera użytkownik, czy są stałe
- ile jest danych

[Dakkar]

@brak_nicku: Kryteria są stałe. Danych jest ok 700 tys.