Excel Forum - Porady, Pomoc,  Excel Help, Excel FAQ Strona Główna
 FAQ  RegulaminRegulamin  Szukaj   Użytkownicy   Grupy   Rejestracja   Profil   Twoje wiadomości   Zaloguj 


Poprzedni temat «» Następny temat
Przesunięty przez: Kaper
07-08-2022, 14:42
Makro – zmiana wartości we wszystkich arkuszach
Autor Wiadomość
Pindelek 
Exceloholic


Pomógł: 1 raz
Posty: 171
Wysłany: 06-08-2022, 10:24   Makro – zmiana wartości we wszystkich arkuszach

Cześć,
Prośba o napisanie makra, które dokona zmiany nazw wg. arkusza „ustawienia” z „Miejscowość” na „ Miejscowość_ok” we wszystkich arkuszach (widoczne i ukryte, łącznie z arkuszem ustawienia).
Liczba arkuszy i miejscowości może być różna.
Plik wzorcowy z przykładami w załączeniu.
Z góry dziękuję

makro_zmiana_we wszystkich_arkuszach.xlsm
Pobierz Plik ściągnięto 10 raz(y) 27.19 KB

ID posta: 419465 Skopiuj do schowka
 
 
Prot 
Fan Excela


Wersja: Win Office 2016
Pomógł: 14 razy
Posty: 68
Wysłany: 06-08-2022, 13:48   

Pindelek napisał/a:
we wszystkich arkuszach (widoczne i ukryte, łącznie z arkuszem ustawienia)

W Twoim przykładzie akurat nie ma żadnego arkusza ukrytego :mrgreen: więc ukryłem "Arkusz3" - i wówczas dla tak sformułowanych warunków możesz wykorzystać do pracy np. makro z kodem:
Kod:
 Sub Makro1()
Dim sch As String
Dim zm As String
Dim x As Integer
Sheets("Arkusz3").Visible = True
x = 1
Do
x = x + 1
Sheets("ustawienia").Select
    sch = Cells(x, 1)
    zm = Cells(x, 2)
    Cells.Replace What:=sch, Replacement:=zm, LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Loop Until Cells(x, 1) = ""
Sheets("Arkusz3").Visible = False
End Sub
ID posta: 419470 Skopiuj do schowka
 
 
Pindelek 
Exceloholic


Pomógł: 1 raz
Posty: 171
Wysłany: 06-08-2022, 15:15   

Prot dzięki za pomoc, ale makro działa tylko w arkusz „ustawienia” a zależy żeby zadziałało na wszystkich arkuszach
ID posta: 419473 Skopiuj do schowka
 
 
Prot 
Fan Excela


Wersja: Win Office 2016
Pomógł: 14 razy
Posty: 68
Wysłany: 06-08-2022, 15:53   

Pindelek napisał/a:
działa tylko w arkusz „ustawienia”

A na jakiej podstawie tak twierdzisz :lol: Wklej to makro do swojego pliku, ukryj "Arkusz3", potem uruchom i sprawdź :-D Arkusz "ustawienia" służy tylko jako wzorzec do przeprowadzenia zmian we wszystkich arkuszach :tak
ID posta: 419475 Skopiuj do schowka
 
 
Pindelek 
Exceloholic


Pomógł: 1 raz
Posty: 171
Wysłany: 06-08-2022, 20:52   

Zrobiłem tak jak piszesz, lecz nadal to samo
Zmiany nastąpiły tylko w arkuszu „ustawienia”.
Plik w załączeniu
ps. pracuję na Excel 2016

makro_zmiana_we wszystkich_arkuszach.xlsm.xlsx
Pobierz Plik ściągnięto 6 raz(y) 32.86 KB

ID posta: 419476 Skopiuj do schowka
 
 
Prot 
Fan Excela


Wersja: Win Office 2016
Pomógł: 14 razy
Posty: 68
Wysłany: 06-08-2022, 21:42   

Pindelek napisał/a:
Plik w załączeniu

Trudno przewidzieć co Ty robisz, że utworzyłeś plik ... . xlsm.xlsx :?:
Proponuję zatem otworzyć moją kopię Twojego pliku (w załączeniu), który ma już ukryty "Arkusz3" i włączyć makro (skrót ctrl+z) i sprawdzić jak to działa :mrgreen:

Prot_zmiana_we wszystkich_arkuszach.xlsm
Pobierz Plik ściągnięto 8 raz(y) 28.57 KB

ID posta: 419478 Skopiuj do schowka
 
 
Maciej Gonet 
Excel Expert


Wersja: Win Office 365
Pomógł: 2828 razy
Posty: 8516
Wysłany: 06-08-2022, 22:19   

Nie chciałem wchodzić w rolę rozjemcy, ale ta dyskusja powoli staje się irracjonalna.
Wykonałem kod z załącznika i zmiany następują tylko w arkuszu "Ustawienia".
Jeśli przeanalizować kod, to nie ma tam żadnej pętli po arkuszach, a Cells bez kwalifikatora oznacza tyle co Activesheet.Cells.
A że aktywny w momencie wykonywania metody Replace jest arkusz "Ustawienia", więc tylko w nim dokonuje się zamiana.
Arkusz3 jest tylko odkrywany i ukrywany, ale nic w nim się nie dzieje.

Prot_zmiana_we wszystkich_arkuszach_po_wykonaniu.xlsm
Pobierz Plik ściągnięto 9 raz(y) 26.27 KB

ID posta: 419480 Skopiuj do schowka
 
 
Tajan


Pomógł: 5091 razy
Posty: 11145
Wysłany: 07-08-2022, 08:33   

Moja propozycja:
Kod:
Sub tst()
Dim sht As Worksheet
Dim i As Long
Dim tbl

tbl = Worksheets("Ustawienia").ListObjects(1).DataBodyRange.Value

For Each sht In Worksheets
  For i = 1 To UBound(tbl)
    sht.Cells.Replace What:=tbl(i, 1), Replacement:=tbl(i, 2), _
                  LookAt:=xlPart, SearchOrder:=xlByRows, _
                  MatchCase:=False, SearchFormat:=False, _
                  ReplaceFormat:=False
  Next
Next

Worksheets("Ustawienia").ListObjects(1).DataBodyRange.Value = tbl

End Sub
ID posta: 419486 Skopiuj do schowka
 
 
Pindelek 
Exceloholic


Pomógł: 1 raz
Posty: 171
Wysłany: 07-08-2022, 10:25   

Tajan – działa idealnie, dziękuję
Pkt dla Ciebie
ps. w kolumnie „Miejscowość” błędny wpis Miejscowość1 zamieniłem na Warszawa
ID posta: 419489 Skopiuj do schowka
 
 
Prot 
Fan Excela


Wersja: Win Office 2016
Pomógł: 14 razy
Posty: 68
Wysłany: 07-08-2022, 20:42   

Maciej Gonet napisał/a:
dyskusja powoli staje się irracjonalna

Całe nieporozumienie związane jest jedynie ze specyficznymi właściwościami funkcji Replace :idea: Podczas gdy ja miałem ustawienie opcji wyszukiwania w całym skoroszycie przedstawiony kod dokonywał pożądanych zmiany we wszystkich arkuszach :!:
Domyślnie jednak ustawiona jest opcja wyszukiwania tylko na aktywny arkusz i wówczas oczywiście dokonuje zmian tylko w aktywnym arkuszu :-?

Zatem żeby uzyskać pożądany efekt zamian mojego makra konieczne jest uprzednie ustawienie opcji wyszukiwania na skoroszyt.

2022-08-07.png
Plik ściągnięto 3 raz(y) 14.39 KB

ID posta: 419503 Skopiuj do schowka
 
 
Maciej Gonet 
Excel Expert


Wersja: Win Office 365
Pomógł: 2828 razy
Posty: 8516
Wysłany: 08-08-2022, 00:58   

Prot, teraz dopiero sprawa się wyjaśniła. Bo samym kodem nie da się (chyba) ustawić wyszukiwania w całym skoroszycie.
Swoją drogą jest tu chyba jakaś niekonsekwencja. Bo Cells wg Microsoftu powinno oznaczać komórki arkusza aktywnego, a jeśli zmiany są w całym skoroszycie, to coś tu się chyba nie zgadza.
ID posta: 419510 Skopiuj do schowka
 
 
Prot 
Fan Excela


Wersja: Win Office 2016
Pomógł: 14 razy
Posty: 68
Wysłany: 08-08-2022, 12:35   

Maciej Gonet napisał/a:
samym kodem nie da się (chyba) ustawić wyszukiwania w całym skoroszycie.

No wg literatury istnieje możliwość (dla Excela powyżej 2007) uruchomienia pola dialogu "Znajdź/Zamień" np. kodem
Kod:
Application.CommandBars.FindControl(ID:=1849).Execute
, ale nie znajduję nic na temat możliwości ustawienia parametru gdzie wyszukiwać przy pomocy VBA :-?
Pozostaje ręczne ustawienie zakresu wyszukiwania i to ustawienie utrzymywane jest dla całej sesji aplikacji Excel :idea: Jak w jednym pliku ustawi się wyszukiwanie w skoroszycie :!: , to ustawienie to obowiązuje również w innych otwartych plikach (aż do resetu aplikacji) :-D
Nie miałem świadomości takich właściwości funkcji Replace i stąd wynikło całe nieporozumienie :cry:
ID posta: 419522 Skopiuj do schowka
 
 
Maciej Gonet 
Excel Expert


Wersja: Win Office 365
Pomógł: 2828 razy
Posty: 8516
Wysłany: 09-08-2022, 12:46   

Ten kod:
Kod:
Application.CommandBars.FindControl(ID:=1849).Execute
wg moich obserwacji nie zmienia ustawienia Arkusz/Skoroszyt, pozwala natomiast na jego ręczną zmianę.
Natomiast starsza wersja tego kodu:
Kod:
Application.Dialogs(xlDialogFormulaReplace).Show
nie zawiera opcji zmiany Arkusz/Skoroszyt, ale wymusza ustawienie domyślnie na Skoroszyt (z tym, że to nie zawsze działa i nie bardzo rozumiem, dlaczego).
W tej wersji po Show można też podać parametry. Jest dostępny ich wykaz, natomiast nie znalazłem opisu. Trzeba by poeksperymentować.
Kod:
xlDialogFormulaReplace    find_text, replace_text, look_at, look_by, active_cell, match_case, match_byte
xlDialogFormulaFind    text, in_num, at_num, by_num, dir_num, match_case, match_byte
Tylko użycie tego wyświetla okno dialogowe i wymaga ręcznej aktywacji. I działa też jakoś kapryśnie. Jeśli ręcznie zaznaczamy sekwencję Znajdź następny/Zamień to działa, natomiast jeśli zaznaczymy Zamień wszystko to raz faktycznie zamienia wszystko, a innym razem tylko pierwsze wystąpienie i kończy działanie. Nie wiem od czego to zależy.
W sumie jest to sposób niepewny i myślę, że jawna pętla po arkuszach jest bezpieczniejsza.
ID posta: 419551 Skopiuj do schowka
 
 
Wyświetl posty z ostatnich:   
Odpowiedz do tematu
Nie możesz pisać nowych tematów
Nie możesz odpowiadać w tematach
Nie możesz zmieniać swoich postów
Nie możesz usuwać swoich postów
Nie możesz głosować w ankietach
Nie możesz załączać plików na tym forum
Możesz ściągać załączniki na tym forum
Dodaj temat do Ulubionych
Wersja do druku

Skocz do:  

Powered by phpBB modified by Przemo © 2003 phpBB Group
Theme xandgreen created by spleen& Programosy modified v0.3 by warna
Opieka techniczna www.wip.pl

Archiwum

Strona używa plików cookies.

Kliknij tutaj, żeby dowiedzieć się jaki jest cel używania cookies oraz jak zmienić ustawienia cookie w przeglądarce.
Korzystając ze strony użytkownik wyraża zgodę na używanie plików cookies, zgodnie z bieżącymi ustawieniami przeglądarki.
Sprawdź, w jaki sposób przetwarzamy dane osobowe