Zavrieť

Porady

Upraviť

Hlavné

Ďalšie rubriky

Excel - MAKRO - Definovanie oblasti buniek o ktorej dopredu nevieme veľkosť. Vloženie vzorca do stĺpca o ktorom dopredu nevieme koľko bude mať riadkov

Ahojte.
Potreboval by som poradiť ako vyznačiť oblasť buniek a vložiť do nej riadkovanie keď dopredu neviem koľko bude v tabuľke riadkov a stĺpcov. Makro predtým vyfiltruje dáta podľa podmienok a ja neviem koľko riadkov a stĺpcov mi zostane. Do upravenej tabuľky potrebujem vložiť jednoduché orámovanie. Ešte by som potreboval vložiť vzorec do stĺpca "C" ale tiež len do oblasti kde budú údaje aj v stĺpci "B". Pomôcka - hlavička tabuľky bude vždy vyplnená, ako aj všetky stĺpce, okrem stĺpca "B" tam sa môže stat že nemáme ukončenie. Tam potom nechceme ani vzorček na výpočet minút..

Doplnenie:

Posledný vyplnený stĺpec bude stále "J"

Oblasť teda bude "A1":"J?"

Ja to ale neviem zapísať do makra..

Doplnil som makierko ktoré som našiel na nete a prispôsobil si ho. Viem teda už určiť ktorý posledný riadok bude vyplnený .

Ako ale zapísať to vloženie vzorca a určenie oblasti ešte neviem..





Ďakujem veľmi pekne každému kto si na to nájde troška času



EDIT:

Prikladám novú prílohu aj z makrom ktoré som tam doteraz dal (je strašné) to je jasné..

Nebude Vám fungovať celé, ukladá mi to kópie súborov v .xls a .pdf do zložiek a vytvorí novú čistú tabuľku pripravenú na ďaľší deň.

Funguje to tak že na Hárok1 si prilepím tabuľku ktorú získavam z iného zdroja a upravujem ju makrom..

Zostáva mi vyriešiť to vloženie jednoduchého orámovania do oblasti ktorá zostane po úprave tabuľky a vloženie vzorca do stĺpca "C"



Ďakujem všetkým
Naposledy upravil sances : 18.01.17 at 15:14
Pravidlá a tipy
  • Každý móže napísať len 1 odpoveď. Neskor mozete svoju odpoveď vylepšiť.
  • Odpoveď má priniesť riešenie na otázku, vyvarujte sa hodnotenia otázky.
  • Odpoveď má byť viac o faktoch ako o názoroch.
Dalšie pravidla a tipy
Neregistrovaný
    Ak potrebujete v otázke niečo upresniť, najskôr sa spýtajte na podrobnosti.
    Koncept slúži na uloženie rozpracovanej odpovede, koncept sa zobrazuje len Vám, až kým ho nezverejníte.
    Ak máte podobnú otázku, založte Novú otázku alebo Súvisiacu otázku.
    ❤ Buďte priateľskí ❤
    Sme súčasťou jednej komunity, ktorá si chce vzájomne pomáhať, rozdieľnosť je vítaná ak neubližuje!
    Usporiadať podľa času

    xxn je offline (nepripojený) xxn

    xxn
    K orámovaniu výsledku: Neviem či Ti to pomôže ale podľa mňa stačí keď sa postavíš na bunku "A1", zadáš CurrentRegion a uvedieš typ orámovania ktoré chceš použiť. Napr:

    Sub Makro2()

    Range("A1").CurrentRegion.Select

    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    End Sub

    Hlasovalo 1 pouzivatelov

    Užitočné (1)
    afrikánka
    1
    26 komentáre - rozbaľ     zbaliť
    sances Ďakujem, ďakujem, ďakujem, vyskúšam, dám vedieť. Ešte by som potreboval ten vzorček do "C". S tým neviem pohnúť. Vďaka
    xxn Návod nájdeš na stránke: . Pokúsil som sa vytvoriť vzorec aj k Tvojej tabuľke "Uprav tabulku"; neviem však aký vzorec to má byť tak som vytvoril makro ktoré vloží nový stĺpec D a do neho vloží jednoduchý vzorec "=Year(C2)":

    Sub AddFormula()

    Dim sh As Worksheet
    Dim LastRow As Long
    Dim i As Long

    'otvorí Hárok 1
    Set sh = Sheets("Hárok1")
    sh.Select

    'vloží nový stĺpec D naformátuje ho ako číslo a do D1 vloží Záhlavie stĺpca
    Columns("D").Select
    Selection.Insert Shift:=xlToRight
    Selection.NumberFormat = "0"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "'Vzorec"
    'spočíta riadky v stĺpci A
    LastRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).row

    'do stĺpca D vloží vzorec; tvar vzorca zodpovedá vzorcu pre prvú bunku ktorú chceme vložiť
    Range("D2" & LastRow).formula = "=Year(C2)"

    End Sub

    Mne to fungovalo.
    xxn Sorry zmenilo mi to znaky v makre. Zameň znak BIG GRIN za dvojbodku a veľké D...tu sa to nedá napísať lebo to zase zmení.
    xxn Ešte malá poznámka ku CurrentRegion. Pri nahrávaní makra nemusíš chodiť do voľby Nájsť a vybrať; Môžeš použiť klávesovú skratku (shortcut). Keď súčasne stlačíš klávesy "Ctrl" a "*" (hviezdička) tak Ti takisto označí CurrentRegion.
    sances Ďakujem
    sances Ďakujem, ďakujem veľmi pekne, musel si tomu obetovať veľa času. V "C" stlpčeku má byť výpočet rozdielu "B" - "A" v hh:mm
    sances Makierku rozumiem, funguje, upravujem pre svoje potrebyyyy, dáva mi to ale vzorček len do posledného riadku, tak je to aj naapísané, ja by som potreboval vložiť vzorec A - B do každého riadku kde je hodnota v stlpci "B" alebo "A" lebo stlpec "A" je začiatok udalosti, stĺpec "B" ukončenie udalosti. Niekedy udalosť v daný den nie je ukončená, teda v stlpci "B" nie je hodnota tak vzorec vráti chybu #HODNOTA. To by i až tak nevadilo, ale ak sa chytíme stĺpca "B" chybe predídeme.
    xxn OK, ešte raz som si pozrel Tvoju tabuľku a dátum/čas je uvedený v stĺpcoch C a D takže som to našil na ne.

    Vzorec pre výpočet rozdielu dátumu a času pre riadok 2 je potom: =IF(D2<>"";TEXT(D2-C2;"d") &" dní + " & TEXT(D2-C2;"h:mm");""). Zakomponuj ho do makra.
    Výsledok sa zobrazí iba vtedy keď bunka v ktorej je časový údaj o konci akcie nie je prázdna.

    Link na takéto riešenie výpočtu rozdielu časov som našiel tu: https://support.office.com/en-us/art...e-737715505ff6
    sances Ďakujem veľmi pekne, idem na to kuk, bude to B - A zle som to napísal. Makro odstranuje prve dva stlpce, preto je to v original tabulke inak. Ale to už by som si mal dokázať upraviť. Ďakujem veľmi pekne
    sances Zatial to neviem dať do makra.Stále tam mám compile error.. skúšam to aj na čistom liste, ale nejde mi to.
    Najprv mu vadí rovná sa IF, potom ; ..skúšal som dávať adresy buniek do uvodzoviek, skrátiť to kompletne aby to fungovalo aspoň na ten prvý (druhý) riadok ale stale compile error.. no skušam dalej..
    sances Chcel by som vylepšiť otázku pridaním novej prílohy ale nedarí sa mi. Vytvoril som nový čistý skúšobný súbor kde riešim len ten vzorec pre stlpec "C" naformátovaný hh:mm - rozdiel časov B - A. Vzorček potom vyzerá takto : =IF(B2<>"";(B2-A2);("")). Samozrejme, do makra ho neviem dať, tam to takto nefunguje, a už vôbec neviem zadať vzorec tak aby vypočítal hodnoty všade kde je v stlpci "B" hodnota.. Stačilo by prepočítať napr. prvých 120 riadkov aby bolo makro rýchlejšie a nemuselo preverovať všetky riadky až na koniec listu.
    Ako môžem pridať ďalšiu prílohu? Dáva mi Permission denied ked chcem vylepšiť otázku..
    xxn Skús nasledujúcu procedúru:

    Sub AddFormula()

    Dim sh As Worksheet
    Dim LastRow As Long
    Dim i As Long

    'otvorí Hárok 1
    Set sh = Sheets("Hárok1")
    sh.Select

    'vloží nový stĺpec E naformátuje ho ako číslo a do D1 vloží Záhlavie stĺpca
    Columns("E").Select
    Selection.Insert Shift:=xlToRight
    Selection.NumberFormat = "General"

    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Rozdiel(vzorec)"

    i = 2
    'spočíta riadky v stĺpci A
    LastRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row

    'preloopuje riadky od 2 po koniec a do každého vsunie vzorec
    For i = 2 To LastRow
    Range("E" & i).Formula = "=IF(D" & i & "<> """",TEXT(D" & i & "-C" & i & ",""d"") & "" dní "" & TEXT(D" & i & "-C" & i & ",""hh:mm:ss""),"""")"
    Next i
    End Sub

    Dosť som sa s tým natrápil, stále mi to totiž zobrazovalo vzorec ako text -chyba bola samozrejme v nesprávnom umiestnení a počte úvodzoviek ... Je to našité na súbor ktorý je v tomto vlákne, ak máš dátumy v iných stĺpcoch tak si to musíš prerobiť.
    sances Ahoj, aj ja som skúšal, aj mne vpisovalo vzorec ako text. Akurát som nevedel určiť tie riadky čo bol zásadný problém.Skúšal som použiť na to tiez UsedRange ale nebol som úspešný. Tvoj vzorec funguje! Ďakujem, dakujem veľmi pekne, za čas ochotu.. a tiež som sa naučil niečo nové. Ďakujem, pomôže to chlapcom, niektorí to zabúdali prekopírovať aj keď som im na prvých 20 riadkov na tvrdo v makre vpísal aby tam dalo vzorec, ak bolo riadkov viac už to tam nedali.. Prerobil som si to aby kontrolovalo hodnotu v stĺpci "B" tým som predišiel chybe #Hodnota keď udalosť nebola ukončená.

    i = 2
    'spočíta riadky v stĺpci A
    LastRow = sh.Cells(sh.Rows.Count, "B").End(xlUp).Row

    'preloopuje riadky od 2 po koniec a do každého vsunie vzorec
    For i = 2 To LastRow
    Range("C" & i).Formula = "=IF(B" & i & "<> """",TEXT(B" & i & "-A" & i & ",""d"") & "" dní "" & TEXT(B" & i & "-A" & i & ",""hh:mm:ss""),"""")"
    Next

    Zmenil som tiež stĺpce v ktorých to mám, tak ako si písal.
    Veľmi pekne Ti ešte raz ďakujem, prajem pekný deň, ja ho mám dnes krajší vďaka Tebe.

    Dík
    xxn OK, som rád že to ide. Ale zistil som že ten vzorec nie je dokonalý. Skús si niektorý koniec akcie posunúť o dva alebo viac mesiacov (napr. zo 17.1.2017 urob 17.3.2017) a uvidíš že to nepočíta korektne. V MS Access je na to funkcia DATEDIFF ale v Exceli som nič také nenašiel. Možno by bolo dobre pozrieť sa na nete po nejakom inom vzorci a poskladať ho do makra.
    sances Vzorček som sa snažil prerobiť formát, väčšinou časový úsek nepresiahne 24 hodín. Nie je potrebné aby tam boli dni.
    xxn Skúšal som zadať do excelu "=DAT." a ponúklo mi iba funkcie "=DATE" a "=DATEVALUE". Skúsil som aj natvrdo napísať "=DATEDIF(E2;H2;"d")" pričom v stĺpcoh E a H sú dátumové údaje ale výsledkom bola chyba #CÍSLO!...takže neviem.
    sances Tak som to skúšal ale veľmi mi to nefungovalo.
    Na orámovanie mi ale jeden veľmi dobrý kamarát odporučil toto:

    ActiveSheet.UsedRange.Select

    Application.CutCopyMode = False
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone

    Toto funguje.Len s tým vzorcom do "C" stlpca neviem pohnúť.
    Veľká vdaka ale že si si našiel čas.
    CurrentRegion je pre mna tiež novinka.
    Možno využijem inde.

    Ďakujemm
    sances Tu je pekne video na CurrentRegion.
    Ak som ho chcel použiť, takto som na to mal ísť.
    sances Zdravím priatelia, akosi mi prestalo fungovať toto makierko na orámovanie oblasti. Zastanie na: .Weight = xlThin - Run-time error "1004" Nie je možné nastaviť vlastnosť Weight triedy Border. Zatiaľ som nič nevygooglil, prebehla len možno nejaká aktualizácia office365. Skúšal som to rôzne upraviť ale nič nepomáha.

    Sheets("Hárok1").Range("A1").CurrentRegion.Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
    End With

    Ďakujem za každú pomoc
    marjankaj A na normálnom exceli si to neskúšal? Prečo excel 365?
    sances No čuduj sa svete, chcel som vyskúšať doma, ale tiež mám Office365, predtým (15:30) som ale vyskúšal v robote a teraz to funguje! Nerozumiem prečo to predtým na troch PC nešlo, teraz to ale funguje. Office 365 máme v robote a aj na doma sme si mohli kúpiť v rámci Office home use programu alebo ako sa to volalo.. no nerád by som aby sa to zopakovalo lebo potom chalani neurobia jeden report.. teraz som im to upravil že natvrdo orámuje oblasť A1:J50 no kdeže začalo fungovať aj CurrentRegion asi to dám znovu do užívania v pôvodnom stave. No chcel by som vedieť prečo to nešlo.. Na jednom NB mam doma este office 2007 tak tu by som skúšal či je to verziou Excelu.
    marjankaj No k excelu 365 sa nevyjadrujem. Nemám ho a ani ma nenapadne si ho inštalovať.
    PotPalo Office 365 sa nekupuje, ten sa prenajíma. Na rok. Potom prestane fungovať.
    sances V práci si nemôžem vyberať, prešlo sa Outlook, OneDrive, SkypeForBussiness..
    marjankaj Ja viem. Asi máte "kvalitné" vedenie. Ale aj na našom Slovensku máme také.
    sances Áno, to by bola kapitola.., všade sa nájdu takí aj onakí riadiaci..manageros
      zbaliť

    Excel - MAKRO - Definovanie oblasti buniek o ktorej dopredu nevieme veľkosť. Vloženie vzorca do stĺpca o ktorom dopredu nevieme koľko bude mať riadkov

    Vieš, kto pozná odpoveď?
    Zdieľaj, možno sa nájde odborník Pozvi odborníka, firmu, inštitúciu atď.

    Porady, ktoré by vás mohli zaujímať

    Prihláste sa a sledujte len tie Porady, ktoré Vás zaujímajú.

    Pridať pochvalu

    Ďakujem Dobre píšes Super rada
    Vtipné Si cool Milé
    Poznámočka