[VBA-Excel] ActiveCell verplaatsen?

[Darkvater]

Wat ik wil doen is een incomplete data compleet maken. Ik heb een lijstje met getallen en als ik een waarde mis, wil ik deze gaan trenden. Het probleem is als bijvoorbeeld de twee getallen achter elkaar missen. Dan trend ik eerst de eerste en dan de andere op basis van de trended value.
Dit werkt, alleen excel berekent de waardes zo vreemd dat hij niet automatisch de eerdere pakt, en ik dus geen waarde krijg. Als ik op die cell ga, en recalc, heb ik wel een waarde.

Ik dacht dus om recursief elke missende te laten berekenen. Hiervoor wilde ik ActiveCell.Row/Column eerst en daarna Selection.Row/Column gebruiken. Maar bij elke recursieve aanroep blijven deze steken op de originele row/column en Excel flipt natuurlijk met circular reference na een tijdje

Hoe is dit op te lossen zonder dat ik een extra interne recursieve functie aanmaak met parameters voor de active-cell?

Visual Basic:

Public Function GetDataV(Cell As Range, Count As Single) As Variant
  GetDataV = Cell
  
  If Not IsNumeric(Cell) Then
    Dim MyRange As Range
    Set MyRange = Range(Cells(Selection.Row - Count, Selection.Column), Cells(Selection.Row - 1, Selection.Column))
    ActiveCell.Offset(-1, 0).Select
    Selection.Calculate
    'MyRange.Calculate ' force the recalculation of the precedents
    
    ' Count needs to be single otherwise #$@!#$ Excel gives an error
    GetDataV = Application.WorksheetFunction.Trend(MyRange, , Count + 1)
  End If
End Function

Ik heb ook dit geprobeerd, maar werkte ook niet

Visual Basic:

...
    Set MyRange = Range(Cells(ActiveCell.Row - Count, ActiveCell.Column), Cells(ActiveCell.Row - 1, ActiveCell.Column))
    ActiveCell.Offset(-1,0).activate
...

[ Voor 13% gewijzigd door Darkvater op 02-02-2006 16:26 ]

[CoRrRan]

Deze was niet heel makkelijk, maar ik heb wel iets gevonden wat een uitkomst geeft.

Visual Basic:

Public Function GetDataV(rngSelection As Range, newX As Double)
' rngSelection moet 2 kolommen bevatten: eerste kolom = known x's, tweede kolom = known y's
' Hoe Count gebruikt moet worden weet ik niet 1-2-3, maar als ik een waarde invul die ik nog
' niet weet, dan krijg ik wel het goede antwoord eruit.
  
  Dim myArr1() As Double, myArr2() As Double, myArr3()
  Dim i As Integer, j As Integer
  
  ReDim myArr1(1 To UBound(rngSelection(), 1)), myArr2(1 To UBound(rngSelection(), 1))
  
  j = 1
  
  For i = 1 To UBound(rngSelection(), 1)
    If rngSelection(i, 2).Value <> "" Then
      myArr1(j) = rngSelection(i, 1)
      myArr2(j) = rngSelection(i, 2)
      j = j + 1
    End If
  Next i
  
  ReDim Preserve myArr1(1 To j - 1), myArr2(1 To j - 1)
  
  myArr3 = Application.Trend(myArr2, myArr1, newX)
  
  GetDataV = myArr3(1)
  
End Function

Test situatie: in A1:A10 staan de cijfers 1 tot en met 10. In B1:B5 staat {100,200,300,400,500}, in B7:B10 staat {700,800,900,1000}. Als je nu in cell C6 hetvolgende invult:

code:

=GetDataV(A1:B10;A6)

krijg je als antwoord:

code:

600

Ongeacht het aantal lege cellen wat je hebt, je zult nu altijd een waarde krijgen voor de x-waarde waarbij je geen y-waarde hebt.

Let wel, dit is nog geen volledige oplossing voor je. Op deze manier kun je nog steeds geen trend-functie toepassen op een eerder berekende waarde. Het KAN echter wel en daarvoor moet je een Array-functie maken van mijn voorbeeld, waarbij je door de selectie heen loopt en elke keer dat je een lege cel tegenkomt een waarde berekend, deze daarna toevoegd in myArr2 (en natuurlijk de 'known x' in myArr1 gooien), waarna je myArr3 weer laat berekenen. De resultaten moet je dan in een nieuwe array gooien (bijv. myArr4) en

Visual Basic:

GetDataV = myArr4

als laatste regel in je functie zetten. Je moet dan je formule met CTRL+SHIFT+ENTER in Excel invoeren. (Het spreekt dan ook voor zich dat je de "newX"-parameter niet meer hoeft te gebruiken.)

Dit is zoals gezegd nog geen oplossing voor je probleem, maar het is een andere aanpak waar je mogelijk wat aan hebt. Je kunt het in ieder geval gebruiken als een startpunt tot de oplossing die je zelf zoekt. Deze functie is ook robuster dan de functie die jij gebruikt, want eigenlijk moet je nooit naar andere cellen gaan springen binnen een functie. Daar heb je namelijk de "Sub"/"End Sub"-statements voor. Ik heb ook even voor het debuggen geen gebruik gemaakt van de "IsNummeric"-toets, die er eigenlijk nog wel in moet zitten om ervoor te zorgen dat de input goed is.

Succes ermee.

[ Voor 3% gewijzigd door CoRrRan op 02-02-2006 23:44 ]

[Lustucru]

Eigenlijk snap ik het hele probleem niet. Als de berekening van cel A afhangt van berekende cel B zal excel altijd eerst cel B uitrekenen en dan pas A. Hangt A ook weer van B af, ja, dan heb je een cirkelverwijzing en piept excel of stopt na x iteraties.

Ik neem even aan dat je in A:A de incomplete reeks hebt, in B:B de formules wilt proppen om de reeks te completeren en dat de trend alleen gebaseerd is op de voorgaande cellen.

In B2:

code:

=ALS(ISGETAL(A2);A2;TREND(B$1:$B1;;RIJ()))

en klikken en slepen...

Met VBa kan natuurlijk ook. Onderstaande code werkt met een simpele lus op de actieve selectie

Visual Basic:

Sub PutTrendValue()
    Dim cellstart As Range, eenCel As Range, trendbasis As Range
    Set cellstart = Selection.Cells(1, 1)
    For Each eenCel In Selection.Cells
        If (Not IsNumeric(eenCel.Value)) Or IsEmpty(eenCel.Value) Then
            Set trendbasis = Range(cellstart, eenCel.Offset(-1, 0))
            eenCel = Application.WorksheetFunction.Trend(trendbasis, , CDbl(trendbasis.Count + 1))
        End If
    Next eenCel
End Sub

offtopic:
en die verplichte conversie is idd een beetje on-vba. Excel is in C geschreven

[ Voor 40% gewijzigd door Lustucru op 03-02-2006 01:29 ]

[Darkvater]

Ah, dank je jongens, ik zal eens maandag als ik weer naar werk ga, kijken hoe dit uitpakt?