Bardzo częstą praktyką stosowaną w firmach jest nadawanie kodów zaczynających się od zera. Wydaje się, że firmy nie wiedzą jaki problem na siebie sprowadzają. Kody z prefiksem 0 będą im w przyszłości mocno utrudniać raportowanie.
Odczyt kodów z prefiksem 0
Niedawno stanąłem przed wyzwaniem, które dotyczyło zaczytania kodów produktów z zewnętrznego raportu na podstawie ich nazwy. Na potrzeby tego opracowania, umieszczę jednak dane w tym samym arkuszu.
Problem obciętych zer
Na pozór błaha sprawa. Wstawiamy makrem formułę w notacji R1C1 korzystając z funkcji WYSZUKAJ.PIONOWO lub kombinacji INDEKS i PODAJ.POZYCJĘ.
Dokładnie tak zrobiłem, ale …. o ile formuła zwróciła mi prawidłowy wynik, o tyle – przy zamianie formuł na wartości, zostały obcięte zera…
Jak widać, kody z prefiksem 0 stanowią pewne zagrożenie.
Rozwiązanie? Zmiana formatu!
Cały haczyk polega na tym, aby przed zamianą formuł na wartości, ustawić format tekstowy.
Kod VBA
Poniższy kod pokazuje w jaki sposób możemy to zrobić prawidłowo.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
Sub WstawFormuly() Dim lKodyOst As Long 'Ostatni wiersz w kol.G 'Sprawdź ost. wiersz w kol. G 1 lKodyOst = lOstatni(wksKody.Range("G:G")) 2 With wksKody 'Zaczytaj cenę 3 With .Range("G3:G" & lKodyOst) 4 .FormulaR1C1 = "=VLOOKUP(RC6,R2C2:R10C4,3,FALSE)" 5 .Value = .Value 6 End With 'Zaczytaj kod 7 With .Range("H3:H" & lKodyOst) 8 .NumberFormat = "General" 9 .FormulaR1C1 = "=VLOOKUP(RC6,R2C2:R10C4,2,FALSE)" 10 .NumberFormat = "@" 11 .Value = .Value 12 End With 13 End With End Sub |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Public Function lOstatni(ByRef rngKolumna As Range) As Long '// Funkcja zwraca numer ostatniego niepustego wiersza w zakresie jednokolumnowym. Dim lTekst As Long Dim lLiczba As Long 'Sprawdź pozycję ostatniego tekstu i ostatniej liczby 1 On Error Resume Next 2 lTekst = WorksheetFunction.Match("żżż", rngKolumna, 1) 3 lLiczba = WorksheetFunction.Match(9.99999999999999E+307, rngKolumna, 1) 4 On Error GoTo 0 'Ostatni wiersz jest w tym przypadku wartością największą 5 lOstatni = WorksheetFunction.Max(lTekst, lLiczba) End Function |
Analiza makra
Skupmy się na liniach 8:11 makra WstawFormuly.
- Ustawiamy format kolumny na Ogólny. Jest to konieczne, aby formuła została wstawiona do komórki poprawnie.
- Wstawiamy formułę w notacji R1C1. W tym momencie mamy zwrócone prawidłowe kody.
- Zmieniamy format kolumny na tekstowy. Jeżeli tego nie zrobimy – zera zostaną obcięte przy zamianie formuł na wartości.
- Zamieniamy formuły na wartości. Pozbyliśmy się formuł – mamy prawidłowo zaczytane kody.
Film na Vlogu
Plik XLSM
