Elektroda.pl
Elektroda.pl
X
Proszę, dodaj wyjątek dla www.elektroda.pl do Adblock.
Dzięki temu, że oglądasz reklamy, wspierasz portal i użytkowników.

Makro excel znajdywanie danych i zamiana

13 Sty 2012 14:06 3399 25
  • Poziom 8  
    Mam rozbudowany arkusz excela z nazwami artykułów oraz cenami. Chciałem zrobić makro które znajduje dany produkt po nazwie i zmienia jego ceny zawarte w tym samym wierszu co nazwa w całym arkuszu
    Przykład

    Olej 1,50
    Czekolada 2 zł
    Olej 1,50
    Sól 1

    Po wpisaniu nazwy produktu np olej w drugim okienku wpisuję cenę i funkcja zmienia mi wszystkie ceny dla etykiety olej.
    Doszedłem do tego co poniżej ale to jest rozwiązanie tylko dla znajdywania ceny i jej zamiany. Jak to usprawnić aby znajdowało nazwę i potem zmieniało cenę na nową podaną. Proszę o pomoc.

    Sub ChgInfo()

    Dim WS As Worksheet
    Dim Search As String
    Dim Replacement As String
    Dim Prompt As String
    Dim Title As String
    Dim MatchCase As Boolean

    Prompt = "Podaj jaką wartość chcesz zastąpić"
    Title = "Wartość szukana"
    Search = InputBox(Prompt, Title)

    Prompt = "Podaj nową wartość"
    Title = "Wartość zmieniana"
    Replacement = InputBox(Prompt, Title)

    For Each WS In Worksheets
    WS.Cells.Replace What:=Search, Replacement:=Replacement, _
    LookAt:=xlPart, MatchCase:=False
    Next

    End Sub
  • Poziom 18  
    Moim zdaniem trzeba każdą z komórek rozbić na dwie sekcje czyli nazwę i cenę.
    Można to zrobić tworząc tabelę np tabela (1 to 1000, 1 to 2) gdzie 1000 to jest liczba rekordów czy też komórek, w których znajdują się dane.
    Podział robimy w każdej komórce za pomocą pętli for począwszy od lewej strony szukamy w ciągu znaków znaku CHR(32) czyli spacji.
    No i to przed spacją to będize nazwa a po spacji to będzie cena
    za pomocą podwojnej petli for tworzymy tablicę danych:

    np.
    for i= 1 to 1000
    for j =1 to 2
    totaj dajemy procedurę rozbijania na nazwę i cenę
    później warunek:
    if j = 1 then
    tablica(i,j) = nazwa
    end if
    if j = 2 then
    tablica(i,j) = cena
    end if
    next j
    next i
    W ten sposób mamy gotową tablicę z rozdzielonymi danymi ->cena; nazwa
    Elementy tablicy np tablica(2,1) oraz tablica(2,2) odpowiadają -> cenie i nazwie drugiego rekordu(czy też komórki)
    następnie wyszukujemy nazwę w stworzonej tablicy i dla tego samego parametru "i" przypisujemy nową cenę.
    na koniec przepisujemy wartości z tablicy do arkusza.
    Nieco skomplikowany proces. Ale do zrobienia.
    Może ktoś wpadnie na coś łatwiejszego.
    W razie pytań pisać.
  • Poziom 40  
    Proszę - twoja koncepcja z inną petlą

    Oczywiście wiesz że to się będzie robić w każdym arkuszu.



    Sub ChgInfo()

    Dim WS As Worksheet
    Dim Search As String
    Dim Replacement As String
    Dim Prompt As String
    Dim Title As String
    Dim MatchCase As Boolean

    Prompt = "Podaj nazwę asortymentu"
    Title = "Wartość szukana"
    Search = InputBox(Prompt, Title)

    Prompt = "Podaj nową wartość"
    Title = "Wartość zmieniana"
    Replacement = InputBox(Prompt, Title)



    For Each WS In Worksheets
    For Each kom In WS.Range("A1:C100")
    If kom = Search Then
    WS.Cells(kom.Row, kom.Column + 2) = Val(Replacement)
    End If
    Next
    Next

    End Sub
  • Moderator Programowanie
    Z czystego lenistwa :) lubię proste rozwiązania.
    Dla pewności sprawdziłbym też kolumnę B. A nuż olej występuje w różnych opakowaniach z tą samą nazwą w kolumnie A.
    Przykład procedury zdarzeniowej. Aktualizuje ceny przy zmianie w kolumnie C.
    Kod: vb
    Zaloguj się, aby zobaczyć kod
  • Poziom 18  
    Mam pytanie do adamas_nt, dlaczego skopiowana procedura zdarzenia działała u mnie na początku, a w tej chwili nie uruchamia się nawet procedura worksheet_change?
    Czy możliwe, zę excel na stałw wyłaczył obsługę zdarzeń?

    Dodano po 2 [minuty]:

    Chyba już wykryłem mój błąd bawiłem się parametrami enableevents i po zakończeniu zdarzenia excel wyłaczył zdarzenia... chciałem później zmienić to w zdarzeniu na true lecz przecież zdarzenia nie działały, wystarczyło stworzyć moduł z nowym makrem i jedną linijką Application.EnableEvents = True
    Tak tylko pisze, jakby ktoś też coś pokićkał,
    Pozdrawiam!
  • Poziom 8  
    Plik w załącznik
    Konkretnie chodzi o to że po podaniu nazwy która jest w pierwszej kolumnie podaję starą cenę która np jest w 7 kolumnie ale w tym samy wierszu i podaję nową cenę i wtedy makro zamienia wszystkie ceny oleju w całym arkuszu na nową cenę.
  • Poziom 28  
    Podglądając załącznik zastanawiam się nad sensem parametrów w pozostałych kolumnach. Biorąc pod uwagę, że są identyczne dla danego asortymentu sądzę, że trzy oleje na raz to przesada. Jeżeli jednak chcesz:
    Cytat:
    Konkretnie chodzi o to że po podaniu nazwy która jest w pierwszej kolumnie podaję starą cenę która np jest w 7 kolumnie ale w tym samy wierszu i podaję nową cenę i wtedy makro zamienia wszystkie ceny oleju w całym arkuszu na nową cenę.

    to wystarczy znaleźć w pierwszej kolumnie przykładowy "olej", zastosować przesunięcie (offset), zmienić wartość komórki, powtórzyć wyszukiwanie do końca arkusza. Przykład kol. adamas_nt jest jak najbardziej trafiony z tym, że zgodnie z Twoim opisem nie komplikowałbym warunku. Przecież chcesz zmienić cenę wszystkich olei na nową (po co więc podawać starą?).
  • Poziom 8  
    Te pozostałe parametry w pliku to tylko dla przykłady żeby pokazać że cena nie jest w drugiej kolumnie tylko dalej. Muszę podać starą ceną żeby zmienić na nową wynika to ze specyfiki arkusza do którego chcę to zastosowac. Proszę jednak o przykład z zastosowaniem offset bo niestety nie posiadam wystarczającej wiedzy żeby samemu to zastosować
  • Poziom 28  
    W poście nr 5 napisanym przez kol. adamas_nt masz przykład użycia offset. Jeśli potrzebny jest Ci szerszy opis odsyłam do:
    Excel>>F1 (lub) Help. Na prawdę to żaden wstyd z tego korzystać.
  • Poziom 8  
    Tak mam opisany offset ale chodzi mi o to żeby po wpisaniu nazwy towaru skrypt nie przechodził określoną liczbę kolumn w prawo i podmieniał na sztywno określoną kolumnę tylko, po podaniu nazwy produktu poprosił o starą cenę i w jej miesce wpisał nową podaną cenę. Ta cena może być w różnych miejscach tego wiersza dlatego też musi być podana stara. Gdybym mógł sam to zrobić nie prosiłbym o pomoc i zawsze najpierw szukam i próbuję a potem dopiero zwracam się na forum i w tym przypadku dalej proszę o pomoc.
  • Poziom 28  
    Cytat:
    Ta cena może być w różnych miejscach tego wiersza dlatego też musi być podana stara.

    Nie bardzo rozumiem. Jak więc za jakiś czas (nie mówię o momencie tworzenia pliku bo wtedy wszystko wydaje się oczywiste) stwierdzisz która z np. dziesięciu różnych cen w wierszu jest tą właściwą starą.
    W momencie ustalania nowej (tak mi się wydaje) wszystkie dotychczasowe, ile by ich nie było stają się starymi.[/quote]
  • Poziom 8  
    Cena zmienia się kilka razy do roku i będę wiedział jaka jest chodzi o ułatwienie żeby w arkuszu nie zmieniać ręcznie np 100 pozycji tylko automatem. Nie można tego zrobić funkcją znajdź i zamień ponieważ musi być skojarzona z produktem.

    Dodano po 4 [godziny] 50 [minuty]:

    Sub ChgInfo()

    Dim WS As Worksheet
    Dim Search As String
    Dim Replacement As String
    Dim Prompt As String
    Dim Title As String
    Dim MatchCase As Boolean

    Prompt = "Podaj nazwę asortymentu"
    Title = "Wartość szukana"
    Search = InputBox(Prompt, Title)

    Prompt = "Podaj starą wartość"
    Title = "Wartość zmieniana"
    Search = InputBox(Prompt, Title)

    Prompt = "Podaj nową wartość"
    Title = "Wartość zmieniana"
    Replacement = InputBox(Prompt, Title)



    For Each WS In Worksheets
    For Each kom In WS.Range("A1:C100")
    If kom = Search Then
    WS.Cells( co tu wpisać żeby działało ) = Val(Replacement)
    End If
    Next
    Next
  • Moderator Programowanie
    Wszystko jedno, i tak nie będzie działać...
    Zastanów się: tej samej zmiennej "Search" dwa razy przypisujesz wartości. Za pierwszym razem nazwę, za drugim starą cenę.
    Jeśli podstawisz cells(kom.row,3) zamieni Ci wszystko ze starą ceną bez względu na nazwę.
  • Poziom 8  
    Sub ChgInfo()

    Dim WS As Worksheet
    Dim Search As String
    Dim Search2 As String
    Dim Replacement As String
    Dim Prompt As String
    Dim Prompt1 As String
    Dim Title As String
    Dim Title1 As String
    Dim MatchCase As Boolean

    Prompt = "Podaj nazwę asortymentu"
    Title = "Wartość szukana"
    Search = InputBox(Prompt, Title)

    Prompt = "Podaj starą wartość"
    Title = "Wartość stara"
    Search = InputBox(Prompt, Title)

    Prompt1 = "Podaj nową wartość"
    Title1 = "Wartość nowa"
    Replacement = InputBox(Prompt1, Title1)



    For Each WS In Worksheets
    For Each kom In WS.Range("A1:C100")
    If kom = Search Then
    WS.Cells(Cells, kom.Row, 3) = Val(Replacement)
    End If
    Next
    Next


    Dodałem nowe zmienne ale i tak się nie kompiluje.
  • Poziom 28  
    Wydaje mi się, że nie zrozumiałeś i dalej masz ten sam błąd. :cry:

    Edit:
    A tak na marginesie wklejaj kody korzystając z "SYNTAX". Łatwiej się czyta.
  • Moderator Programowanie
    Błąd składni w linii: WS.Cells(Cells, kom.Row, 3) = Val(Replacement)
    Prawidłowy: WS.Cells(kom.Row, 3) = Val(Replacement)

    Zanim uruchomisz zrób kopię arkusza. Wszystkie ceny =Search (stara wartość) zostaną zastąpione...
  • Poziom 8  
    Kod: vb
    Zaloguj się, aby zobaczyć kod


    Ale dalej kod nie działa tak jak należy bo nie zamienia mi starej ceny na nową tylko liczbę w 3 kolumnie
  • Moderator Programowanie
    nugat napisał:
    Kolumny są 3, nie trzeba ich rozdzielać
    Nazwa waga cena
    :?:

    Jeśli cena w walucie, to z reguły będzie liczbą. Kiedyś kupowało się kanister benzyny za dwie flaszki, ale te czasy chyba już minęły.
    Widzę, że pozostali stracili już cierpliwość. Kolego, drepczesz w miejscu i koncentrujesz się nie na tym, co jest rzeczywistym problemem w Twoim kodzie.
  • Poziom 8  
    Masz rację nie wiem w czym problem ale jeżeli Ty wiesz to czemu mi nie możesz pomóc.
  • Pomocny post
    Poziom 40  
    Jak byś wytłumaczył wszystko spokojnie na początku to by teraz nie było tej dyskusji.
    Po prostu co chwila zmieniasz założenia. Albo my nie nadążamy za twoją myślą.

    Kolega adamas wskazał ci błąd z "search" Owszem nazwałeś zmienne search i search2 ale nigdzie nie wykorzystujesz search2 a zmienna search jest zaraz po określeniu jej w pierwszym okienku zmieniana dana z drugiego okienka.


    Poniżej zastępowanie dla danego asortymentu starej ceny nowa ceną. (wg twojej koncepcji) "wierszami"

    ALE!!!

    Jezeli któryś z parametrów (w tym samym wierszu z nazwą szukanego asortymentu na początku) bedzie miał wartość starej ceny (np. przez przypadek)
    ta wartość też zostanie zmieniona.

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    W załączeniu przykład.

    Czy może jeszcze inaczej to makro powinno działać?
    Nadmieniam że zakres nazw w makrze ograniczyłem do 100 w kolumnie A ("A1:A100") i założeniem kodu jest jeszcze to że nazwy asortymentów sa zawsze w tej samej kolumnie (ten sam zakres) w każdym arkuszu
  • Poziom 8  
    Wielkie dzięki wszystko działa jak należny. Problem pojawia się gdy cena jest z miejscem po przecinku, wtedy jej nie zamienia. Ewentualnie co w przypadku gdy nazwa produktu nie będzie w kolumnie A tylko gdzieś dalej w tym samym wierszu co cena.
    Jeszcze raz dziękuję za pomoc.

    Dodano po 18 [minuty]:

    Próbowałem pozmieniać zmienne ze string na single ale dalej nie zmienia mi cen, które są po przecinku.
  • Pomocny post
    Poziom 40  
    To pewien "mankament" VB

    dla VB liczba z przecinkiem jest "ciągiem" a z kropką jest wartością.

    W związku z tym trzeba takie przecinki zamieniać "ręcznie"

    Poniżej kod

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    Jeżeli nazwy mogą być w innej kolumnie/kolumnach to trzeba zmienić/poszerzyć zmienną "Zakres" na np "A1:G1000"
    Ale to wydłuży czas wykonywania procedury (i właściwie ile jest kolumn więcej niż 1 tyle razy niepotrzebnie wykona się pętla w tym samym arkuszu)
  • Poziom 8  
    Dzięki za wyjaśnienia
  • Poziom 8  
    Proszę o pomocy. Makro nie działa jeżeli nazwa produktu okazuje się liczbą, czym to może być spowodowane.
    Jeżeli w nazwie jest cyfra np W5 to jest ok, ale jeżeli jest samo 5 to pętla nie zamienia wartości.
    Z tego co doszedłem samemu to problem pojawił się po dodaniu zamiany kropek na przecinki
  • Pomocny post
    Poziom 40  
    "Nie działa" bo okienko inputbox'a pomimo tego że wpiszesz cyfry/liczbę zwraca ją jako tekst.

    Wstaw za kodem:
    Kod: vb
    Zaloguj się, aby zobaczyć kod

    kod zamieniający liczbę jako tekst na liczbę jako liczbę:

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    UWAGA!
    W takim rozwiązaniu jeżeli liczbowa nazwa asortymentu będzie równa starej cenie to nazwa takiego asortymentu też zostanie zamieniona na nową cenę.

    edit:
    ----------------------------------------------

    Dla zasady poprawności kodu zmień w deklaracji zmiennej zmienną Search

    Wykasuj ją z listy dotyczącej String'a i dodaj

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    (podmieniłem załącznik)