..|| Blog || Produkti/Storitve || MExcel || MDodatki || 300 nasvetov ||..

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

Pomoč pri izdelavi makrov

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

OdgovorNapisal/-a drglzr » Sr feb 27, 2013 12:56 pm

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.
drglzr
 
Prispevkov: 29
Pridružen: Če feb 21, 2013 10:06 pm



Matjazev.NET
 

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

OdgovorNapisal/-a admin » Sr feb 27, 2013 1:18 pm

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
admin
Site Admin
 
Prispevkov: 3524
Pridružen: Sr jul 20, 2005 10:06 pm

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

OdgovorNapisal/-a drglzr » Sr feb 27, 2013 3:45 pm

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.
drglzr
 
Prispevkov: 29
Pridružen: Če feb 21, 2013 10:06 pm

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

OdgovorNapisal/-a admin » Sr feb 27, 2013 4:27 pm

Pozdravljeni,

Priložite trenutno kodo in bova videla, kje so še možne izboljšave...
lp,
Matjaž Prtenjak
Administrator
admin
Site Admin
 
Prispevkov: 3524
Pridružen: Sr jul 20, 2005 10:06 pm

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

OdgovorNapisal/-a drglzr » Sr feb 27, 2013 5:41 pm

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.
drglzr
 
Prispevkov: 29
Pridružen: Če feb 21, 2013 10:06 pm

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

OdgovorNapisal/-a admin » Če feb 28, 2013 8:23 am

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
admin
Site Admin
 
Prispevkov: 3524
Pridružen: Sr jul 20, 2005 10:06 pm

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

OdgovorNapisal/-a drglzr » Pe mar 01, 2013 6:26 pm

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.
drglzr
 
Prispevkov: 29
Pridružen: Če feb 21, 2013 10:06 pm

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

OdgovorNapisal/-a admin » Pe mar 01, 2013 9:32 pm

drglzr je 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
admin
Site Admin
 
Prispevkov: 3524
Pridružen: Sr jul 20, 2005 10:06 pm

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

OdgovorNapisal/-a drglzr » So mar 02, 2013 1:16 pm

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.
drglzr
 
Prispevkov: 29
Pridružen: Če feb 21, 2013 10:06 pm

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

OdgovorNapisal/-a blacky1 » Če apr 14, 2016 10:59 am

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
blacky1
 
Prispevkov: 11
Pridružen: Pe jan 13, 2012 8:52 am

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

OdgovorNapisal/-a admin » Če apr 14, 2016 12:49 pm

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
admin
Site Admin
 
Prispevkov: 3524
Pridružen: Sr jul 20, 2005 10:06 pm

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

OdgovorNapisal/-a blacky1 » To apr 19, 2016 7:34 am

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.
blacky1
 
Prispevkov: 11
Pridružen: Pe jan 13, 2012 8:52 am

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

OdgovorNapisal/-a admin » To apr 19, 2016 9:01 am

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
admin
Site Admin
 
Prispevkov: 3524
Pridružen: Sr jul 20, 2005 10:06 pm

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

OdgovorNapisal/-a blacky1 » To apr 19, 2016 10:08 am

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?
blacky1
 
Prispevkov: 11
Pridružen: Pe jan 13, 2012 8:52 am

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

OdgovorNapisal/-a admin » Sr apr 20, 2016 7:42 am

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
admin
Site Admin
 
Prispevkov: 3524
Pridružen: Sr jul 20, 2005 10:06 pm

Naslednja

Vrni se na VBA

Kdo je prisoten

Po forumu brska: 0 registriranih uporabnikov in 2 gostov

cron