[VBA & Excel] Functie kan niet verder kijken dan kolom Z?

[Verwijderd]

Beste mede tweakers,

Ik heb een functie geschreven (met wat hulp van het forum ) die uit een grote tabel één kolom kan halen. Je voert in een cel de datum waar je de gegevens van wilt hebben en de functie zoekt de bijbehorende waarde in de tabel. De functie ziet er als volgt uit:

---

Public Function analysel(datum As Date)
Dim tabel(100)
analysel = 0
Application.Volatile True
For i = 1 To 20

tabel(i) = Range(Chr(Asc("Blad1!A") + i - 1) & "1")
If tabel(i) = datum Then
analysel = Range(Chr(Asc("Blad1!A") + i - 1) & Application.Caller.Row)

End If

Next i
End Function

---

Dit werkt allemaal prima, behalve als ik "i" van 1 tot een getal hoger dan 26 zet. Dit komt waarschijnlijk doordat als de functie bij kolom Z van excel komt hij hierna niet kolom AA herkent.

Kent iemand het probleem of weet er iemand hier een oplossing voor??

[Dido]

Zoek nog eens goed uit wat asc en chr doen
Chr(Asc("Blad1!A") + i - 1
Is vrij onzinnig: Asc("Bladenzovoort") is namelijk 66, (= Asc("B"))

Vervolgens tel je er dus I bij op, en zet dat weer om naar een character... AA is geen character.

Er zijn manieren om met kolomnummers te werken ipv letters, of om een offset te gebruiken (x kolommen naar rechts.) Zoek daar eens op, want CHR geeft nooit AA terug.

[Maasluip]

RTFM

dit is de laatste regel uit het voorbeeld dat bij de Range property in de help van Excel 2000 staat:

This example sets the font style in cells A1:C5 on Sheet1 to italic. The example uses Syntax 2 of the Range property.

code:

Worksheets("Sheet1").Range(Cells(1, 1), Cells(5, 3)). _
    Font.Italic = True

[ Voor 3% gewijzigd door Maasluip op 18-08-2005 11:34 ]

[onkl]

Visual Basic:

Public Function analysel(datum As Date)
'Dim tabel(100) Deze kan dus weg, is nogal inefficient.
analysel = 0
Application.Volatile True
For i = 1 To 20
If activeworksheet.cells(1,i).value = datum then

analysel = activeworksheet.cells(Application.Caller.Row,i).value 
'value, want anders krijg je een range object terug, niet de waarde uit de cel.

End If

Next i
End Function

[ Voor 7% gewijzigd door onkl op 18-08-2005 13:23 ]

[Daos]

onkl schreef op donderdag 18 augustus 2005 @ 13:22:

Visual Basic:

analysel = activeworksheet.cells(Application.Caller.Row,i).value 
'value, want anders krijg je een range object terug, niet de waarde uit de cel.

Dit werkt bij mij goed zonder .value:

Visual Basic:

Sub test()
    x = Cells(1, 1)
    MsgBox x
    Cells(1, 1) = x + 1
End Sub

Verwijderd schreef op donderdag 18 augustus 2005 @ 11:16:
tabel(i) = Range(Chr(Asc("Blad1!A") + i - 1) & "1")

Zoals Dido al zei heeft dat weinig zin. Het volgende zal wel goed werken van 1 tot 26:

Visual Basic:

tabel(i) = Range("Blad1!" & Chr(Asc("A") + i - 1) & "1")

In een eerder topic van je had ik al gezegd dat je ook Cells() kan gebruiken.

[Dido]

Daos schreef op donderdag 18 augustus 2005 @ 15:42:
Zoals Dido al zei heeft dat weinig zin. Het volgende zal wel goed werken van 1 tot 26:

Visual Basic:

tabel(i) = Range("Blad1!" & Chr(Asc("A") + i - 1) & "1")

Grapjas
Dat is hetzelfde als

Visual Basic:

tabel(i) = Range("Blad1!" & Chr(64 + i) & "1")

[onkl]

Dido schreef op donderdag 18 augustus 2005 @ 15:56:
[...]

Grapjas
Dat is hetzelfde als

Visual Basic:

tabel(i) = Range("Blad1!" & Chr(64 + i) & "1")

Leer eens coden. Er is al vaak genoeg gezegd dat je met Cells moet werken. ´t is toch overduidelijk dat je dat zo aanpakt:

code:

tabel(i) = Range(Cells(1,Chr(Asc("A") + i - 65))

[bloedserieus]Anders werkt het misschien wel niet.[/bloedserieus]

[sanfranjake]

Ik haal even de vraagtekens en uitroeptekens uit de titel, dat staat zo schreeuwerig. Eentje is meer dan voldoende

[Daos]

Dido schreef op donderdag 18 augustus 2005 @ 15:56:
[...]

Grapjas
Dat is hetzelfde als

Visual Basic:

tabel(i) = Range("Blad1!" & Chr(64 + i) & "1")

Dat klopt, maar om het enigszins overzichtelijk te houden werk ik nooit met de ascii codes, maar met Asc(). Mijn reactie was serieus bedoeld.

Je kan ook nog functies schrijven die de kolomnummers omzetten van lettertjes naar cijfertjes en omgekeerd. Bijvoorbeeld zo:

Visual Basic:

Private Function fromAA(ByVal c As String) As Integer
    While (c <> "")
        fromAA = fromAA * 26 + Asc(c) - Asc("A") + 1
        c = Right(c, Len(c) - 1)
    Wend
End Function

Private Function toAA(ByVal c As Integer) As String
    While (c > 0)
        toAA = Chr(Asc("A") + (c - 1) Mod 26) + toAA
        c = (c - 1) \ 26
    Wend
End Function

Public Function analysel(datum As Date)
    Application.Volatile True
    
    For i = fromAA("A") To fromAA("BZ")
        kolom = toAA(i)
        If Range("Blad1!" & kolom & "1") = datum Then
            analysel = Range("Blad1!" & kolom & Application.Caller.Row)
        End If
    Next i
End Function

[ Voor 8% gewijzigd door Daos op 19-08-2005 13:00 ]

[onkl]

Ik denk dat het bijster onverstandig is kolomletters te gebruiken in VBA. Het leidt enorm af en je foutopsporing wordt een hel. Het is niet ondenkbaar dat er een goede reden is voor het idee van de VBA ontwikkelaars om daar gewoon getalletjes voor te gebruiken.
Dit gezegd zijnde: @fromAA & toAA. Niet bijzonder moeilijk, wel erg stijlvol.