Excel-kopiranje vrstic ter nato izračuna na nov list

Pomoč pri izdelavi makrov
drglzr
Prispevkov: 31
Pridružen: Če Feb 21, 2013 10:06 pm

Excel-kopiranje vrstic ter nato izračuna na nov list

Odgovor Napisal/-a drglzr »

Pozdravljeni!
Priznati moram, da se z VBA doslej prav veliko nisem ukvarjal, čeprav ugotavljam, da je moč z njim marsikatero opravilo olajšati. Tako se sedaj učim tudi tega (pač prebiram nekaj literature in brskam po spletu). Tudi s pomočjo tega foruma mi je uspelo izdelati makro z naslednjo kodo:

Sub Izracun()
Dim vrstica As Long: vrstica = 2

While (Cells(vrstica, 3).Value <> "")
Range("C1") = Cells(vrstica, 3)
Range("D1") = Cells(vrstica, 4)
Range("E1") = Cells(vrstica, 5)
Range("I2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Select Case Sheets("List2").Range("A2") = ""
Case True
Sheets("List2").Range("A2").PasteSpecial Paste:=xlPasteValues

Case False
Sheets("List2").Range("IV2").End(xlToLeft).Offset(0, 1).PasteSpecial Paste:=xlPasteValues
End Select
vrstica = vrstica + 1
Wend
End Sub

Zadeva torej deluje tako, da podatke v konkretni vrstici (ki se spreminja navzdol do vključno zadnje polne) in navedenih stolpcih kopira v 1. vrstico. To v stolpcu "I" od 2. vrstice navzdol vrne rezultate, ki jih nato kot vrednosti prekopira v "List2", za vsako vrstico v svoj stolpec.

Kot rečeno, zadeva mi deluje, vendar jo poskušam izpopolniti, pri tem pa imam težave. Predvsem jo poskušam pohitriti, saj je njeno izvajanje ob večjem številu vrstic precej počasno. Poskušal sem Range nastaviti na ("C1:E1") = obsegu celic "vrstica, 3" do "vrstica, 5", česar zaradi svojega neznanja ne uspem. Prav tako "List2" ne znam spremeniti v "sosednji list" (to je ActiveList.Next), s čimer bi se izognil konkretnemu poimenovanju lista. Prav tako mi ne uspe računalnika prepričati, da začne v izbrani (lahko vedno drugi) vrstici in se "sprehodi" do zadnje polne, saj mi vedno izvede kodo od prve do zadnje polne vrstice.

Verjamem, da so moje VELIKE težave za strokovnjake minorne. Zato bi bil za vsakršen dobrohoten namig hvaležen.
admin
Site Admin
Prispevkov: 3687
Pridružen: Sr Jul 20, 2005 10:06 pm

Re: Excel-kopiranje vrstic ter nato izračuna na nov list

Odgovor Napisal/-a admin »

Pozdravljeni,

Lepo, da ste se naučili uporabljati VBA in da se izpopolnjijete... Da vaša koda je zagotovo počasna in največji razlog za to je uporaba COPY-PASTE mateode... to je skrajno počasno in nepotrebno... V VBA lahko preprosto prepišete vsebino ene celice v drugo celico ali pa enega območja v drugega, torej lahko preprosto zapišete:

Koda: Izberi vse

range("F4:I5").value = range("A1:D2").Value
Ali če to zapišete v obliki spremenljivk vrstica, celica

Koda: Izberi vse

Sub test()
  Dim visina As Integer, sirina As Integer
  
  visina = 3
  sirina = 4
  Range(Cells(15, 8), Cells(14 + visina, 7 + sirina)).Value = Range(Cells(1, 1), Cells(visina, sirina)).Value
End Sub
Najprej torej popravite ta del potem pa bomo pogledali še nadalnje probleme...
lp,
Matjaž Prtenjak
Administrator
drglzr
Prispevkov: 31
Pridružen: Če Feb 21, 2013 10:06 pm

Re: Excel-kopiranje vrstic ter nato izračuna na nov list

Odgovor Napisal/-a drglzr »

Podravljeni!

Hvala za odgovor in uporabne nasvete.

Za določitev območja (Range) sem sedaj uporabil prvo opcijo, torej z vrednostjo (Value). Razlika v hitrosti je očitna.

Prosil bi še za kakšen predlog glede mojih ostalih težav.
admin
Site Admin
Prispevkov: 3687
Pridružen: Sr Jul 20, 2005 10:06 pm

Re: Excel-kopiranje vrstic ter nato izračuna na nov list

Odgovor Napisal/-a admin »

Pozdravljeni,

Priložite trenutno kodo in bova videla, kje so še možne izboljšave...
lp,
Matjaž Prtenjak
Administrator
drglzr
Prispevkov: 31
Pridružen: Če Feb 21, 2013 10:06 pm

Re: Excel-kopiranje vrstic ter nato izračuna na nov list

Odgovor Napisal/-a drglzr »

Pozdravljeni!

Trenutna koda je:

Sub Izracun()
Dim vrstica As Long: vrstica = 2

While (Cells(vrstica, 3).Value <> "")
Range("C1:E1").Value = Range(Cells(vrstica, 3), Cells(vrstica, 5)).Value
Range("I2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Select Case Sheets("List2").Range("A2") = ""
Case True
Sheets("List2").Range("A2").PasteSpecial Paste:=xlPasteValues

Case False
Sheets("List2").Range("IV2").End(xlToLeft).Offset(0, 1).PasteSpecial Paste:=xlPasteValues
End Select
vrstica = vrstica + 1
Wend
End Sub

Spremenjen je zapis prve vrstice obsega (C1:E1 itn.). Z morebiti drugačnim (hitrejšim) zapisom kopiranja vrednosti stolpca "I" se še ukvarjam, vendar zaenkrat ne najdem izboljšave. Še vedno pa ne uspem rešiti drugih dveh težav (pomembnejša mi je rešitev zadnje, saj bi tako obdelal le od določene vrstice naprej in ne vsega, kar je že bilo). Za morebiten namig vnaprej hvala. Lep pozdrav.
admin
Site Admin
Prispevkov: 3687
Pridružen: Sr Jul 20, 2005 10:06 pm

Re: Excel-kopiranje vrstic ter nato izračuna na nov list

Odgovor Napisal/-a admin »

Pozdravljeni,

Kodo, ki sem vam jo zapisal, sem zapisal zato, da bi jo uporabili namesto COPY-PASTE funkcije, ki je počasna in popolnoma nepotrebna. Ob tem vi vedno kopirate VSEBINO ISTIH celic, kar je potratno. Napišite raje, kaj točno želite, da bi program počel. Vaša funkcija namreč ne naredi nič drugega, kot stolpec L kopira na list 2 tolikokrat, kolikor vrstic je na trenutnem listu...
lp,
Matjaž Prtenjak
Administrator
drglzr
Prispevkov: 31
Pridružen: Če Feb 21, 2013 10:06 pm

Re: Excel-kopiranje vrstic ter nato izračuna na nov list

Odgovor Napisal/-a drglzr »

Pozdravljeni!

Res mi funkcija kopira stolpec "i" na list 2 tolikokrat, kolikor je vrstic na trenutnem listu, vendar se vrednosti v stolpcu "i" vsakokrat spremenijo (morda nisem bil dovolj jasen - v celicah so formule), saj se pred tem spremenijo vrednosti v obsegu C1:E1. Tudi moj namen je imeti vsakokratne vrednosti tega stolpca na sosednjem listu, saj jih nato lahko naprej obdelujem.

Z namenom pospešitve funkcije sem poskusil tudi kopiranje stolpca "i" izvesti v podobni obliki, kot kopiranje vrstic v obseg C1:E1 (z uporabo .Value). Torej vsakokratni obseg stolpca "i", od vrstice 2 navzdol do zadnje polne celice, sem z uporabo funkcije "Select Case" in ".Value" poskusil prilepiti v prvi prazen stolpec sosednjega lista (od celice A2 navzdol), kar pa mi ne uspeva. Če že, mi v 2. vrstico prilepi le prvo ali le zadnjo celico stolpca "i", ne pa celotnega obsega, kot mi sicer "Select Case" izvaja v delujoči kodi (nekje pač "brcam v temo").

Ta del kode, ki mi skopira le vsakokratno vrednost celice "I2", je takšen:

Sub Izracun()
Dim vrstica As Long: vrstica = 2

While (Cells(vrstica, 3).Value <> "")
Range("C1:E1").Value = Range(Cells(vrstica, 3), Cells(vrstica, 5)).Value
Select Case Sheets(2).Range("A2") = ""
Case True
Sheets(2).Range("A2").Value = Sheets(1).Range(Cells(2, 9), Cells(Rows.Count, 9).End(xlUp)).Value
Case False
Sheets(2).Range("IV2").End(xlToLeft).Offset(0, 1).Value = Sheets(1).Range(Cells(2, 9), Cells(Rows.Count, 9).End(xlUp)).Value
End Select
vrstica = vrstica + 1
Wend
End Sub

Sem pa med tem našel rešitev za nedoločno poimenovanje listov, to je z uporabo zaporedne številke lista znotraj delovnega zvezka (npr. Sheets(2) – kar predstavlja 2. list v delovnem zvezku, ne glede na njegovo poimenovanje).

Kolikor pa razumem vaš prvi odgovor, sta v njem zapisani dve možnosti, ki dasta enak rezultat. Vendar pa ne ene ne druge ne znam uporabiti za hitrejše kopiranje stolpca "i".

Saj vem, da sem nadležen.

S spoštovanjem.
admin
Site Admin
Prispevkov: 3687
Pridružen: Sr Jul 20, 2005 10:06 pm

Re: Excel-kopiranje vrstic ter nato izračuna na nov list

Odgovor Napisal/-a admin »

drglzr napisal/-a:... Saj vem, da sem nadležen....
:) Ah, dajte no... Saj forum je zato, da vprašate... in če/ko imam čas ali pa ko/če ima čas kdo drug, vam pač pomaga.

Da bi ne dolgovezila, sam vam (zato ker ste se potrudili tudi sami!) vašo funkcijo pač prepisal tako da je hitra in vam zraven zapisal tudi vse potrebne komentarje, da boste vedeli kaj katera vrstica počne.

Koda: Izberi vse

Sub HitrejsiIzracun()
  Dim r As Long: r = 2
  
  ' izracunamo od katere kolone na drugem listu moramo podatke kopirati
  Dim c As Integer
  c = Sheets(2).Range("IV2").End(xlToLeft).Column
  
  ' če smo v prvi koloni potem je ok, sicer imamo polno kolono in moramo iti eno naprej
  If (c > 1) Then c = c + 1
  
  ' izračunamo koliko vrstic moramo kopirati
  Dim r1 As Long
  r1 = Range("I2").End(xlDown).Row
  
  ' sprehodimo se po vrsticah in kolono I kopiramo na drugi list
  While (Cells(r, 3).Value <> "")
    Sheets(2).Range(Sheets(2).Cells(2, c), Sheets(2).Cells(r1, c)).Value = Range(Cells(2, 9), Cells(r1, 9)).Value
    c = c + 1
    r = r + 1
  Wend
    
  ' na koncu še zadnjo polno vrstico (od C do E) prekopiramo v prvo
  Range("C1:E1").Value = Range(Cells(r - 1, 3), Cells(r - 1, 5)).Value
End Sub
To je torej takšna lepa, šolska koda, "prava programerska", pa bi bila takšnale ;):

Koda: Izberi vse

Sub HitrejsiIzracun1()
  Dim st As Single: st = Timer

  Dim wsSrc As Worksheet: Set wsSrc = Sheets(1)
  Dim wsDest As Worksheet: Set wsDest = Sheets(2)
    
  Dim c As Integer: c = wsDest.Range("IV2").End(xlToLeft).Column
  If (c > 1) Then c = c + 1
  
  Dim r1 As Long: r1 = wsSrc.Range("I2").End(xlDown).Row
  Dim rngSrc As Range: Set rngSrc = wsSrc.Range(wsSrc.Cells(2, 9), wsSrc.Cells(r1, 9))
  Dim rngDest As Range: Set rngDest = wsDest.Range(wsDest.Cells(2, 1), wsDest.Cells(r1, 1))
  
  Dim r As Long: r = 2
  While (wsSrc.Cells(r, 3).Value <> "")
    rngDest.Offset(0, c - 1).Value = rngSrc.Value
    c = c + 1
    r = r + 1
  Wend
    
  wsSrc.Range("C1:E1").Value = wsSrc.Range(wsSrc.Cells(r - 1, 3), wsSrc.Cells(r - 1, 5)).Value
  Debug.Print Timer - st
End Sub
lp,
Matjaž Prtenjak
Administrator
drglzr
Prispevkov: 31
Pridružen: Če Feb 21, 2013 10:06 pm

Re: Excel-kopiranje vrstic ter nato izračuna na nov list

Odgovor Napisal/-a drglzr »

Z vsem spoštovanjem, NAJLEPŠA HVALA za vaš trud in pomoč. Kolikor se le da, poskušam stvari reševati sam (tako se po mojem mnenju lahko človek največ nauči). Tokrat se mi je ustavilo in vaša pomoč je bila več kot dobrodošla. Kodo sem moral sicer nekoliko predelati. V zadnji vrstici sem "r - 1" zamenjal z "r" in vrstico premaknil za vrstico z "While", saj mi sicer vedno kopira rezultate dobljene na podlagi zadnje vrstice. Vendar pa sedaj stori, kar želim. In to neprimerno hitreje.

Še enkrat hvala.

Lep pozdrav.
blacky1
Prispevkov: 11
Pridružen: Pe Jan 13, 2012 8:52 am

Re: Excel-kopiranje vrstic ter nato izračuna na nov list

Odgovor Napisal/-a blacky1 »

Pozdravljeni,

Ponovno se obračam na vašo pomoč, ker me zanima ali se lahko pohitri izvajanje teh dveh kod.
Sam se že kr nekaj časa ukvarjam z pohitritvijo in mi nikakor ne gre.
Pri prvi kodi iz enega lista kopiram na drugi novi list na katerem ne sme vsebovati formul ampak dejanske vrednosti.
Ker mislim, da bi se dalo to rešiti drugače kot sem izvedel to jaz se obračam na vašo pomoč, ki je res super.
s to kodo kopiram vse podatke na novi list in potem določena stolpca prepišem z kopiranimi podatki.

Koda: Izberi vse

 Sheets("ZAKLJUČI").Select
    Sheets("ZAKLJUČI").Copy
    ActiveSheet.ShowAllData
    Range("H5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("H5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("J5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("J5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.AutoFilter Field:=4, Criteria1:="="
    Range("B5:K200").SORT Key1:=Range("C4"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("E5").Select


In druga koda pri kateri filtriram 4 točno določene stolpce v točno določenem zaporedju.
Drugače povedano, rad bi sortiral obseg podatkov od stolpca A do J po vrstnem redu 1. E4, 2. G4, 3. D4, in 4. C4

Koda: Izberi vse

Range("A4:J300").SORT Key1:=Range("E4"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
   Range("A4:J300").SORT Key1:=Range("G4"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("A4:J300").SORT Key1:=Range("D4"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("A4:J300").SORT Key1:=Range("C4"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("E5").Select
Upam, da sem dovolj natančno razložil in da se bo našla rešitev za pohitritev.

Za pomoč se vam že v naprej zahvaljujem.


Lep pozdrav
admin
Site Admin
Prispevkov: 3687
Pridružen: Sr Jul 20, 2005 10:06 pm

Re: Excel-kopiranje vrstic ter nato izračuna na nov list

Odgovor Napisal/-a admin »

Pozdravljeni,

Glede prvega makra, vam lahko pohitritev izvedem samo tako, da se problema pač lotim na drug (pravi?) način, da bi lahko naredil to, pa potrebujem natančnejši opis, kaj točno želite

Drugi makro pa ne morem nikakor pohitriti, saj izvajate pač sortiranje podatkov in sortiranje ja pač toliko hitro, kolikor ga Excel zmore. Morate pa se zavedati, da je potrebno sortiranje po stolpcih izvajati v obratnem vrstnem redu! Če torej želite KONČEN rezultat, kjer bo sort pa stolpcih E, G, D, C, morate najprej sortirati po stolpcu C, potm D, potem G in na koncu E (torej v obratnem vrstnem redu). Ali pa, ker Excel pozna tudi sortiranje treh stolpcev hkrati, najprej sortirate (hkrati) po stolpcih G, D,C in na koncu še po E.
lp,
Matjaž Prtenjak
Administrator
blacky1
Prispevkov: 11
Pridružen: Pe Jan 13, 2012 8:52 am

Re: Excel-kopiranje vrstic ter nato izračuna na nov list

Odgovor Napisal/-a blacky1 »

Pozdravljeni Matjaž,
Kaj točno bi želel, da se mi izvede je to, da iz lista na katerem so zapisani podatki z uporabo funkcij (vlookup, mid, samodejni vnos datuma), prekopiram na drugi nov list kjer bi bilo vse zapisano brez funkcij, katerega bi lahko potem poslal po mailu.

Če obstoječi list kopiram in ga pošljem po mailu, se na listu izbrišejo podatki, ker jih excel ne najde več (logično).

Ker sem že večkrat zasledil, da se lahko podatek prekopira brez formul se obračam na Vas, ker si nikakor ne znam razložit kako prekopirati list brez formul.

Danes to počnem tako, da prekopiram na nov list izberem ključne stolpce katere z metodo copy-paste ponovno prepišem z vrednostmi namesto formul.

Kaj dejansko si želim iz Vaš strani je to, da bi me usmerili v pravilno razmišljanje oz. kje bi lahko našel to razlago kako se to uredi, saj se želim naučiti zapisati samostojno kodo.
Ker sem že iskal Vašo knjigo, katere žal ni mogoče več kupiti si pomagam z Vašimi nasveti na forumu.

V kolikor me lahko usmerite na pravo pot bi vam bil zelo hvaležen.

Lep pozdrav.
admin
Site Admin
Prispevkov: 3687
Pridružen: Sr Jul 20, 2005 10:06 pm

Re: Excel-kopiranje vrstic ter nato izračuna na nov list

Odgovor Napisal/-a admin »

Pozdravljeni,

to kar želite je dokaj preprosto. Postopek pa je sledeč.
1. List kopirate in kopirajo se tudi formule
2. Na kopiranem listu vse formule spremenite vrednosti:

Koda: Izberi vse

Sub KopirajListInIzFormulNarediVrednosti()
  ActiveSheet.Copy
  Cells.Select
  Selection.Copy
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  Range("A1").Select
End Sub
lp,
Matjaž Prtenjak
Administrator
blacky1
Prispevkov: 11
Pridružen: Pe Jan 13, 2012 8:52 am

Re: Excel-kopiranje vrstic ter nato izračuna na nov list

Odgovor Napisal/-a blacky1 »

Drži, tako sem tudi do nekdaj delal.

Ker pa se v stolpec K zapisuje datum z macro kodo mi vedno javlja napako, ker ne more zapisati vseh datumov (kar pa nebi želel, da spremeni).
Macro je zapisan na listu ne v modulu.

Lahko mogoče prekopiram list brez macroja?
admin
Site Admin
Prispevkov: 3687
Pridružen: Sr Jul 20, 2005 10:06 pm

Re: Excel-kopiranje vrstic ter nato izračuna na nov list

Odgovor Napisal/-a admin »

Pozdravljeni,

V tem primeru pa lahko na koncu še celice z vašim makrom prekopirate na nov list in bi moralo biti vse OK. Vam pa priporočam, da se tega makra na listu res čimprej znebite, ker ti makri niso prava rešitev in bodo prej ali slej nepodprti...
lp,
Matjaž Prtenjak
Administrator
Odgovori