calculatie opslaan in array

[Jors]

Hallo,

ik heb een hele simpele vraag verwacht ik maar ik kom er niet helemaal uit.

Ik heb twee kolommen, laten we zeggen H en I die ik door mekaar deel door de volgende formule:

Sub test1()
    Dim i As Integer
    
    For i = 2 To 6
        Cells(i, "J").Value = Cells(i, "H").Value / Cells(i, "I")
    Next i
End Sub

Dat is simpel en gaat goed. Nou wil ik dat deze getallen niet getoond worden in kolom J, maar dat deze in een array worden opgeslagen, omdat ik alleen de laagste uitkomst nodig heb van deze array met waardes.

Ik probeer dit als volgt:

Sub test2()
    Dim sym(2 To 6) As Integer
    Dim i As Integer
    
    For i = 2 To 6
        sym(i) = Cells(i, "H").Value / Cells(i, "I")
    Next i
    
    MsgBox sym(2)
    
End Sub

Maar mijn msgbox geeft 0 als uitkomst (zou 0.5 moeten zijn). Waar zit de fout?

 

[wampier]

Als je alleen geïnteresseerd bent in de laagste waarde hoef je ook geen array te gebruiken. Neem een enkele variabele en verander die indien nodig als er een lagere waarde wordt gevonden.

 

[Elsendoorn2134]

Jors,

De fout is simpel, je gebruikt een array van integers, integers kunnen alleen gehele getallen bevatten.
0.5 wordt dus automatisch omgezet naar 0 verander de Dim sym(2 to 6) as integer in een andere definitie,
bijvoorbeeld naar Single of Double.

Veel Succes.

 

[Jors]

Dank! Wat een idiote fout zeg Maar op zich wel fijn dat ik de code op zich goed geschreven heb. Nu werkt hij prima inderdaad!

Nog een vraag; ik wil nu dat de laatste rij variabel (= lastRowArray), maar ik krijg een foutmelding als ik dit als code gebruik:

 Dim sym(firstRowArray To lastRowArray) As Double
    Dim i As Integer
    With Wb1.Sheets(1)
        For i = 61 To 111
            sym(i) = .Cells(i, "H").Value / .Cells(i, "I")
        Next i
    End With
    ThisWorkbook.Sheets(1).Range("E" & useRow) = Application.WorksheetFunction.Min(sym)

 

[OctaFish]

Je definieert de array met variabele waarden, maar in je lus gebruik je vaste waarden (61 to 111). Zijn die waarden wel hetzelfde als je in de aanroep meegeeft? Zo ja, gebruik dan de parameters en geen vaste waarde in je lus.

Dim sym(firstRowArray To lastRowArray) As Double
    Dim i As Integer
    With Wb1.Sheets(1)
        For i = firstRowArray To lastRowArray)

 

[Jors]

Ja dat zijn idd dezelfde waarden, die had ik even laten staan om wille van het debuggen.

Ik heb het nu als volgt opgelost:

Dim sym() As Double
Redim sym(firstRowArray To lastRowArray)
Dim i As Integer
With Wb1.Sheets(1)
    For i = firstRowArray To lastRowArray)
         sym(i) = .Cells(i, "H").Value / .Cells(i, "I")
    Next i
End With

En dat lijkt te werken! Nu loop ik meteen weer tegen een probleem aan. Het komt wel eens voor dat een cell in de reek die door elkaar gedeeld wordt een 0 bevat. Ik wil graag dat deze niet worden meegenomen in de berekening. Hoe lost ik dit op?

 

[wampier]

Met een If/then statement? overigens blijf ik bij mijn vorige antwoord: als je vervolgens weer door deze array gaat lopen om de kleinste waarde te vinden verspil je onnodig een hoop rekenkracht en geheugen

 

[Jors]

Met een If/then statement? overigens blijf ik bij mijn vorige antwoord: als je vervolgens weer door deze array gaat lopen om de kleinste waarde te vinden verspil je onnodig een hoop rekenkracht en geheugen

Ok ik zal hier eens naar kijken. Was net blij dat ik dit aan de praat had

 

[wampier]

Niets mis mee, maar het is een lineaire group met slechts 1 onbekende, dus twee keer dooplopen is per definitie onnodig.

Overigens werkt je huidige oplossing ook generiek zonder start / eind cellen te weten (of zelfs de hoeveelheid cellen), door middel van dynamische arrays. Dynamische arrays werken ook om je probleem met delen door 0 heen omdat je ze gewoon niet vult indien er foute invoer is.

 

[Jors]

Overigens werkt je huidige oplossing ook generiek zonder start / eind cellen te weten (of zelfs de hoeveelheid cellen), door middel van dynamische arrays. Dynamische arrays werken ook om je probleem met delen door 0 heen omdat je ze gewoon niet vult indien er foute invoer is.

Dat klinkt inderdaad als een goede oplossing. Maar is mijn array niet een dynamisch array doordat ik een variabel start en eindpunt heb of werkt het zo niet?

 

[wampier]

Dynamische arrays hebben geen start en eindpunten. Je kunt ze groter maken op het moment dat je dat nodig hebt. Je bepaalt dus helemaal niet hoeveel je nodig hebt.

Overigens is de "juiste" manier om dit te doen zoiets als dit (zonder beveiliging en checks etc):

kleinste = a1/b1
for i = 2 to 100
   if (ai / bi) < kleinste then
      kleinste = ai/bi
   end if
next i

Na eenmalig doorlopen van deze loop is "kleinste" het kleinste getal. opslag in arrays is dus niet nodig. met de "delen door 0 problemen" moet je wat extra regels inbouwen om errors te voorkomen, maar dat veranderd in principe niets aan de basisoplossing.

 

[snb]

Sub M_snb()
     y = [min(A1:A26/B1:B26)]
End Sub

of

Sub M_snb()
   y = [min(A1:A26/(if(B1:B26=0,1,B1:B26)))]
End Sub

 

[wampier]

@SNB in principe werkbaar, maar je oplossing "B" is niet universeel, omdat je de uitkomst van "ongeldig" stelt op "1". Het nadeel van elke "substitutie" methode is dat de oplossing niet universeel toepasbaar is op elk bereik. Natuurlijk kun je de standaardwaarde stellen op 9999999999 en in de praktijk 100% werkbaarheid afdwingen.

 

[Jors]

Dynamische arrays hebben geen start en eindpunten. Je kunt ze groter maken op het moment dat je dat nodig hebt. Je bepaalt dus helemaal niet hoeveel je nodig hebt.

Overigens is de "juiste" manier om dit te doen zoiets als dit (zonder beveiliging en checks etc):

kleinste = a1/b1
for i = 2 to 100
   if (ai / bi) < kleinste then
      kleinste = ai/bi
   end if
next i

Na eenmalig doorlopen van deze loop is "kleinste" het kleinste getal. opslag in arrays is dus niet nodig. met de "delen door 0 problemen" moet je wat extra regels inbouwen om errors te voorkomen, maar dat veranderd in principe niets aan de basisoplossing.

Bedankt wampier! Ik heb een soortgelijke code gebruikt en het werkt inderdaad prima!

Ik geloof zelfs dat ik mijn hele code nu min of meer af heb. Het idee er van is dat ik data importeer vanuit een andere excel file en dat hij deze automatisch invult in vooraf bepaalde kolommen.

Nou loop ik net tegen een nieuw probleem aan. Ik wilde de excelfile inclusief macro's verplaatsen (kopieren) van de test-map waarin ik deze geplaatst heb naar de echte werkmap. Bij het uitvoeren van de macro zie ik inderdaad gewoon netjes de file geopend worden van waaruit geimporteerd moet worden, maar ik de data wordt niet overgenomen in mijn "verzamelsheet". Wel wordt de test.xlsm geopend.

ik heb de hele code doorzocht en nergens staat een verwijzing naar test.xlsm. Het opgegeven path naar de te importeren bestanden is ook relatief dmv:

Set Wb1 = Workbooks.Open(ThisWorkbook.Path & Application.PathSeparator & "analyses" & Application.PathSeparator & procnr & ".xls")

Waarin procnr een variable is die verwijsd naar het te importeren bestand. Zoals ik al zei wordt deze ook gewoon geopend, maar worden de waarden niet overgenomen.

Wat zou er aan de hand kunnen zijn?

Edit: volgens mij heb ik de oplossing al.
Ik had een snelkoppeling naar de macro op mijn werkbalk geplaatst. Kennelijk linkte deze nog naar de macro's in de oude file, niet naar die in de gekopieerde file.