[Excel] Maximum waarden naar een nieuw bestand schrijven

[DarkShadow]

Hallo, ik heb een klein probleem, ik heb excel bestanden varierend van 100 tot 500 kollommen breed. Deze kollommen zijn op hun beurt weer 5000 cellen lang, in elk van deze kollommen zit een waarde waar ik in geinteresseerd ben, deze wil ik er uithalen en netjes onder elkaar zetten in een nieuw bestand met misschien nog wat informatie erbij. Hoe kan ik dit het beste doen? alvast bedankt

edit: Kleine wijziging in de plannen, ik heb een knop gemaakt met daaronder een macro. Nu kan ik al wel een macro maken dat de maxima uit een ander bestand haalt en ze dan in het bestand met de knop plaatst. Het probleem is dat ik liever voordat ik de macro run een bestand moet selecteren (aangezien alle bestanden dezelfde opmaak hebben) en dat vervolgens wegschrijf naar een nieuw bestand.

Het bestand met de macro knop werkt dan dus eigenlijk als een klein programmaatje.

[ Voor 39% gewijzigd door DarkShadow op 15-09-2005 10:29 ]

[Maasluip]

Een waarde of de maximum waarde? Als het de maximumwaarde is dan zou ik een lege regel bovenin toevoegen, de formule max(a2:a5001) erin zetten (natuurlijk a vervangen door de kolomnaam), die regel selekteren en kopiëren en dan paste-special doen en het vinkje bij "transpose" aanzetten.

Als je het in een macro opneemt kun je het dan vaker uitvoeren.

[ Voor 43% gewijzigd door Maasluip op 15-09-2005 10:30 ]

[DarkShadow]

Ja, het is idd het maximum, het maximum eruit krijgen is ook niet echt het probleem, het is wel een probleem dat ik eigenlijk nu elke keer de macro moet aanpassen omdat ik met een ander bestand werk. Ik wil dus dat als ik op de button click, dat ik dan een bestand moet selecteren. Verder zou het ook mooi zijn om het allemaal naar een nieuw bestand weg te schrijven.

[Maasluip]

Dat is allemaal in een macro op te nemen behalve het prompten voor een filenaam. De simpelste manier om dat te doen met de functie InputBox en de returnwaarde daarvan bij .SaveAs in de macro als variabele meegeven.

[DarkShadow]

Ik heb nu dit stukje code als max van een kolom:

code:

    Range("B13").Select
    ActiveCell.FormulaR1C1 = "=MAX('F-test-13-09-2005.xls'!R26C2:R5050C2)"

Nu moet het stukje F-test-13-09-2005.xls een variabele worden die ik het liefst zelf zou selecteren maar dat is geen vereiste. (als ik het in een cel kan zetten vind ik het ook prima).

Nu dacht ik het ongeveer zo te doen, maar dit stukje code werkt niet:

code:

    Range("B13").Select
    ActiveCell.FormulaR1C1 = "=MAX(' Range("E3").Select '!R26C2:R5050C2)"

en in E3 staat dat de naam van het bestand. Hoe krijg ik zoiets werkend.

[onkl]

Kijk een naar GetOpenFileName in je VBA help, die geeft een mooi dialoogje waar een variabele uitkomt. In de help slingert geloof ik ook een voorbeeld rond wat het goed uitlegt.

[DarkShadow]

hehe, bedankt, ik zal het eens even goed gaan bekijken

edit: ik kan die hele functie nergens vinden, ook niet in de help

Verder dacht ik gewoon even een variable aan te maken maar dat werkt ook niet:

code:

    Dim myFile As String
    Set myFile = Range("F3").Select

    Range("B13").Select
    ActiveCell.FormulaR1C1 = "=MAX("myFile"!R26C2:R5050C2)"

het probleem is dat ik wel eens met visual basic heb gewerkt, maar dat is al weer een flinke tijd geleden en ik heb een beetje problemen met het gewoon normaal uitschrijven.

[ Voor 82% gewijzigd door DarkShadow op 15-09-2005 11:57 ]

[onkl]

DarkShadow schreef op donderdag 15 september 2005 @ 11:44:
[...]
edit: ik kan die hele functie nergens vinden, ook niet in de help

Verder dacht ik gewoon even een variable aan te maken maar dat werkt ook niet:

code:

    Dim myFile As String
    Set myFile = Range("F3").Select

    Range("B13").Select
    ActiveCell.FormulaR1C1 = "=MAX("myFile"!R26C2:R5050C2)"

het probleem is dat ik wel eens met visual basic heb gewerkt, maar dat is al weer een flinke tijd geleden en ik heb een beetje problemen met het gewoon normaal uitschrijven.

Visual basic help, niet Excel help.
Maar probeer dit vehikel eens. Foutjes niet uitgesloten:)

Visual Basic:

Sub geval()
Dim uitbestand As Workbook
Dim naarbestand As Workbook
Dim uitbestandnaam As String
Dim naarbestandnaam As String
uitbestandnaam = Application.GetOpenFilename(, , "Maxima uit")
naarbestandnaam = Application.GetSaveAsFilename(, , , "Opslaan in")
Set uitbestand = Workbooks.Open(uitbestandnaam)
Set naarbestand = Workbooks.Add
For i = 1 To 500 'aantal kolommen
    'Worksheets(1) pakt je eerste tabblad. Je kan er ook Worksheets("PietjePuk") van maken als je die moet hebben
    naarbestand.Worksheets(1).Cells(i, 1).Value = Application.WorksheetFunction.Max(uitbestand.Worksheets(1).Range(Cells(1, i), Cells(5000, i)))
Next i
naarbestand.SaveAs naarbestandnaam
uitbestand.Close
naarbestand.Close
End Sub

[ Voor 5% gewijzigd door onkl op 15-09-2005 12:42 ]

[DarkShadow]

Ik zal het proberen, zit nu op mn werk en moet helaas weer me richten op iets anders. In ieder geval alvast bedankt!

[DarkShadow]

Hey ik heb het stukje code getest, maar helaas werkt het niet helemaal, alhoewel ik denk dat je het er dichtbij zit. Hij opent het bestand gewoon, maar als hij het wil wegschrijven genereerd hij een error bij deze zin:

Visual Basic:

For i = 1 To 500 'aantal kolommen
    'Worksheets(1) pakt je eerste tabblad. Je kan er ook Worksheets("PietjePuk") van maken als je die moet hebben
    naarbestand.Worksheets(1).Cells(i, 1).Value = Application.WorksheetFunction.Max(uitbestand.Worksheets(1).Range(Cells(1, i), Cells(5000, i)))
Next i

Het stukje code is wel precies wat ik bedoel (enige aanpassing was dat het bestand begint bij cel 26 ipv 1)


hij geeft btw error code 1004 --> object or aplication defined error

edit, de fout zit in dit stukje:

Application.WorksheetFunction.Max(uitbestand.Worksheets(1).Range(Cells(1, i), Cells(5000, i)))

Ik kan daar ook gewoon een stukje tekst inzetten en dan opent hij het nieuwe bestand en plaatst de daar neer gezet tekst er netjes i aantal keer onder elkaar.

edit2:
als ik dit invul:
naarbestand.Worksheets(1).Cells(i, 1).Value = Application.WorksheetFunction.Max(1, 3, 5, 800, 2)

werkt het gewoon en krijg ik i keer 800 onder elkaar te staan. Het probleem zit dus in het feit dat de max functie niet de manier begrijpt waarop hij de range krijgt gepresenteerd.
Als ik dat dus weet is het opgelost

[ Voor 43% gewijzigd door DarkShadow op 16-09-2005 12:03 ]

[DarkShadow]

Al iemand die weet hoe je de max van een column kan krijgen met max in de bovenstaande opmaak?

[Spotmatic]

Wat ik niet begrijp: Excel kan maar maximaal 256 kolommen aan. Hoe krijg je het voor elkaar om met 500 kolommen te werken???

[DarkShadow]

hmm ik heb de bestanden idd nog niet 500 kolommen breed meegemaakt, we hebben maximaal nog maar 200 shots gedaan, dus het is even nog niet relevant

Het enige wat ik nu werkend heb gekregen tot nu toe is als ik het doe met dit stukje code:

code:

For i = 1 To 3 'aantal kolommen
        naarbestand.Worksheets(1).Cells(i, 1).Value = Application.WorksheetFunction.Max(uitbestand.Worksheets(1).Range("A1:A25"))
Next i

Het probleem is dat ik nu de kolommen variabel moet zien te maken. Het is volgens mij gemakkelijker om de rij variabel te maken door gewoon i als variabele te nemen en dan range("A" & i: "A" & i) te doen ofzo. Maar het probleem is dus dat dit met letters wat lastiger gaat

(met deze code haal ik gewoon de max uit de eerste kolom van een testbestandje, dat plaatst hij vervolgens in het nieuwe bestand 3 keer onder elkaar)

[ Voor 79% gewijzigd door DarkShadow op 19-09-2005 09:10 ]

[TXC]

Je kunt met de functie:

Visual Basic:

chr(asciiwaarde as integer)

Integers naar de bijbehorende ASCII waarde omzetten. Dan doe je

Visual Basic:

chr(tellertje + 64) 

en dan krijg je de 'A' (A = ASCII 65).

Voor de ASCII codes zie hier

[DarkShadow]

Maar dan heb ik nog alleen maar het alfabet, daarna krijgen de kolommen dubbele nummers, maargoed, ik zal het eens uitproberen

[DarkShadow]

Ik heb nu zoiets:

code:

For i = 1 To 3 'aantal kolommen
    'Worksheets(1) pakt je eerste tabblad. Je kan er ook Worksheets("PietjePuk") van maken als je die moet hebben
    naarbestand.Worksheets(1).Cells(i, 1).Value = Application.WorksheetFunction.Max(uitbestand.Worksheets(1).Range(("(i+64) & 1 :(i +64) & 25")))
Next i

Maar dat werkt helaas niet, het vervelende is dat het me wel lukt om de max te krijgen, maar niet dat de kolom dan elke keer veranderd met een teller. Bestaat er gewoon niet zoiets als next.column?

En waar kan ik op internet de aanwezige functies met een voorbeeld goed bekijken, want uit de help wordt ik niet veel wijzer

[TXC]

Anders maak je een array waar je al je kolomnamen inzet. Daarna lees je met de teller de juiste kolom naam uit het array.

Visual Basic:

Dim MijnKolommen(705) as string
Dim teller1, _
    teller2, _
    teller3 as integer

'Eerst gewoon A t/m Z opslaan
for teller1 = 1 to 26
  MijnKolommen(teller1) = chr(teller1+64)
next teller1

'Nu de geneste kolommen opslaan
for teller2 = 1 to 26
  for teller3 = 1 to 26
    teller1 = teller1 + 1
    MijnKolommen(teller1) = chr(teller2+64) & chr(teller3+64)
  next teller3
next teller2

'En nu kun je MijnKolommen als het goed is gebruiken om je Excel kolomnamen
'uit te lezen met als index je kolomnummer

Zoiets? Niet getest en niet verantwoordelijk

En waar kan ik op internet de aanwezige functies met een voorbeeld goed bekijken, want uit de help wordt ik niet veel wijzer

MSDN

[ Voor 14% gewijzigd door TXC op 19-09-2005 12:10 ]

[onkl]

Sorry, dom foutje in mijn originele post.
Als je kijkt naar:
Application.WorksheetFunction.Max(uitbestand.Worksheets(1).Range(Cells(1, i), Cells(5000, i)))
in mijn macrootje, dan zal excel heel goed snappen hoe 'ie uitbestand.Worksheets(1).Range moet interpreteren.
Waar die Cells op slaat zal Excel tijdens de uitvoering echter een raadsel zijn. De defenitie van het range object is:
-Of: Parent.Range("verwijzing"), verwijzing als string
-Of: Parent.Range(linksboven,rechtsonder), linksboven en rechtsonder als range. Cells(1,i) levert natuurlijk geen range op maar een fout.
Ik denk (een gokje bezien de berichten nadien van TS) dat dit wel gaat werken:

Visual Basic:

Application.WorksheetFunction.Max(uitbestand.Worksheets(1).Range(uitbestand.Worksheets(1).Cells(1, i), uitbestand.Worksheets(1).Cells(5000, i)))

(en ja, ik heb wel eens nettere code gegenereerd. )

[ Voor 21% gewijzigd door onkl op 19-09-2005 12:12 ]

[DarkShadow]

hehe, hij werkt

[onkl]

Misschien kan je voor 't archief nog even de hele code posten die je aan de gang hebt gekregen?

[DarkShadow]

Hier is de code:

code:

Sub fileconversion()
'
' fileconversion Macro

Dim uitbestand As Workbook
Dim naarbestand As Workbook
Dim uitbestandnaam As String
Dim naarbestandnaam As String

uitbestandnaam = Application.GetOpenFilename(, , "Maxima uit")
naarbestandnaam = Application.GetSaveAsFilename(, , , "Opslaan in")
Set uitbestand = Workbooks.Open(uitbestandnaam)
Set naarbestand = Workbooks.Add

For i = 1 To 250 'aantal kolommen
    'Worksheets(1) pakt je eerste tabblad. Je kan er ook Worksheets("PietjePuk") van maken als je die moet hebben
   naarbestand.Worksheets(1).Cells(i, 1).Value = Application.WorksheetFunction.Max(uitbestand.Worksheets(1).Range(uitbestand.Worksheets(1).Cells(26, i), uitbestand.Worksheets(1).Cells(5026, i)))
Next i

naarbestand.SaveAs naarbestandnaam
uitbestand.Close
'naarbestand.Close
'
End Sub

Bedankt!

(ga nu nog wat kleine dingen aanpassen en proberen van de verkregen grafiek automatisch een chart te maken)

[ Voor 5% gewijzigd door DarkShadow op 19-09-2005 14:18 ]