vba declareren van reeks zowel getallen als getallen met letters

[Driesumdre]

Goedenavond Excelhelden,

Ik heb een vraag over het declareren van een variabele.
Ik heb een VBA-code waarbij een artikelnummer van een leverancier gedeclareerd wordt.

Tot nu toe ging dat prima, aangezien het artikel alleen cijfers bevatte.
Nu (nieuwe extra leverancier) hanteert bijvoorbeeld 45b782c als artikelnummer en dat vind de code niet leuk en werkt niet op dat artikelnummer.
Logisch want het is geen alfanumerieke karakters worden niet gevangen in Long.

Maar wat dan wel? op variant loopt hij ook vast. dat heeft waarschijnlijk te makenmet het feit dat ik de waarde gebruik als zoekreferentie in een vlookup worksheetfunction.

Ik hoop dat jullie een oplossing kunnen bedenken.

Hieronder de code

Private Sub Worksheet_Change(ByVal Target As Range)
[B]Dim nArt As Long        'dit is de boosdoener[/B]
Dim nCount As Integer
Dim sLev As String
Dim sOms As String
Dim sFormaat As String
Dim sWork As String
Dim rArtTabel As Range ' the look up table
Dim rArtikel As Range ' The Make column on Lookup Table

If Target.Column = 2 Then
    If Not Target Is Nothing Then

    nArt = Target.Value
    Set rArtTabel = Sheets("Tabellen").Range("ArtTabel")
    Set rArtikel = Sheets("Tabellen").Range("Art")
    
    nCount = Application.WorksheetFunction.CountIf(rArtikel, nArt) 'Checks if the value entered in the Make entered is found on Clumn A of LUTable (Manfacturer)
    
        If nCount = 1 Then
        
        sLev = Application.WorksheetFunction.VLookup(nArt, rArtTabel, 2, False)
        sOms = Application.WorksheetFunction.VLookup(nArt, rArtTabel, 3, False)
        sFormaat = Application.WorksheetFunction.VLookup(nArt, rArtTabel, 4, False)
        
        
        Target.Offset(0, 1).Value = sLev
        Target.Offset(0, 2).Value = sOms
        Target.Offset(0, 3).Value = sFormaat
        
        Else
        
        Target.Offset(0, 1).Value = ""
        Target.Offset(0, 2).Value = ""
        Target.Offset(0, 3).Value = ""
        
        End If
    End If

End If
End Sub

 

[snb]

Verwijder 'Option Explicit' en alle declaraties.

 

[Driesumdre]

Hoi SNB,

Bedankt voor de snelle reactie.

• Option explicit gebruik ik niet hier.

• De "Zoektabel" bevind zich op een ander tabblad als de "target". Ik denk dat het verwijderen van de declaraties dan niet goed gaat?

Als ik het mis heb hoor ik het graag.

 

[AccessGuru]

Dit werkt uiteraard ook:

Dim nArt As String

Variabelen níet declareren kan, maar ik vind dat erg onhandig. Controle over de gegevens is onderdeel van netjes programmeren.

 

[alphamax]

Dim nArt As Variant

bron: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/variant-data-type en https://software-solutions-online.com/vba-variant/

En dat is een klein beetje wat snb bedoelt, als je niet declareert dan is alles variant.

 

[snb]

Over variabelen heb je geen controle.
Dat doet de compiler voor je.
In moderne talen (Python) neemt de compiler dat, net zoals in VBA voor zijn/haar rekening.

 

[Driesumdre]

Dank voor de antwoorden allemaal
Het probleem is nog niet opgelost.

Zowel op string als variant loopt hij stuk.
Dit heeft naar mijn idee te maken met dat in de zoektabel een getal als getal wordt opgenomen en een alfanummerieke&numerieke code als tekst.
Op het moment dat target een getal is stuit de code op variant en string.
Op het moment dat target een alfanumeriek&numerieke code is dan stuit de code op long.

waar ik naar op zoek ben is een oplossing waar ik beide in vang, of een workaround.

Niet declareren levert overal variant, maar zoals gezegd loopt die vast op de lookup table.

Alvast bedankt voor het meedenken.

 

[sylvester-ponte]

wat denk je van een voorbeeldbestandje waar deze problemen in voorkomen?

 

[snb]

Dat 'vastlopen' heeft niets met de soort variabele van doen.
Voldoe aan @sylvs verzoek.

 

[Driesumdre]

Hierbij op verzoek een "uitgekleed" document.

 

[snb]

In C5: =INDEX(Tabel4[Leverancier];MATCH([@[Artikel Leverancier]];Tabel4[Artikel];0))
In D5: =INDEX(Tabel4[Art omschrijving];MATCH([@[Artikel Leverancier]];Tabel4[Artikel];0))
in E5: =INDEX(Tabel4[Formaat];MATCH([@[Artikel Leverancier]];Tabel4[Artikel];0))

VBA overbodig: uitschakelen.

Voor de validatie van kolom B kun je beter als lijst Art=Tabel4[Artikel] gebruiken

 

[Ginger]

Een artikelnummer is nooit een getalwaarde maar een tekstwaarde. Denk hierbij ook aan bijvoorbeeld een telefoonnummer. Alle "nummers" waar je niet mee rekent, declareer je als string áls je wilt declareren natuurlijk...

 

[Driesumdre]

Wederom bedankt voor de antwoorden.

@SNB De index functie is mij bekend, dat werkt altijd.
(Voor de validatie van kolom B kun je beter als lijst Art=Tabel4[Artikel] gebruiken) zo was het in de eerste versie, maar ja, je gaat wat proberen hè. bedankt. daarnaast is die manier ook veel makkelijker invoeren

Maar, de reden om dit via VBA te doen is omdat ik niet degene ben die dit document vult en niet iedereen even handig is met Excel zoals wij dat zijn.
Op deze manier (via VBA) staan er geen formules in het bestand die per ongeluk verwijderd kunnen worden en het ziet er "clean" uit.
Als het niet anders kan dan beveilig ik de bewuste cellen, maar daar gaat mijn voorkeur niet naar uit, omdat het dan ook niet meer overschreven kan worden, wat met een vulling middels VBA wel kan.

@Ginger String was mijn eerste keuze. Maar dan wordt er niets gevonden in de tabel. (tabel is ingesteld als standaard, en ziet het artnr. mogelijk als getal)

Hebben jullie wellicht nog ideeën?
Ik hoor het graag. Het zou toch moeten kunnen?

 

[snb]

Zo dan ?

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, [Tabel2[Artikel Leverancier]]) Is Nothing Then
    Application.EnableEvents = False

    sn = [Tabel4]
    For j = 1 To UBound(sn)
      If Target = sn(j, 1) Then Target.Offset(, 1).Resize(, 3) = Array(sn(j, 2), sn(j, 3), sn(j, 4))
    Next

    Application.EnableEvents = True
  End If
End Sub

 

[Driesumdre]

Hallo SNB,

Perfect! Bedankt voor deze oplossing.
Dit type VBA code's (For, next) lijkt hogere wiskunde en ik heb me nog nooit echt in verdiept, maar dat gaat nu zeker gebeuren.
Wel slim om dit een XLSB bestand te maken, dat scheelt weer wat opslag.

Iedereen bedankt voor het meedenken en de aangedragen oplossingen, wordt gewaardeerd! :thumb: Keep up the good work.

 

[snb]

Of zó:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, [Tabel2[Artikel Leverancier]]) Is Nothing Then
    Application.EnableEvents = False
        
    Target.Offset(, 1).Resize(, 3) = [Tabel4[Artikel]].Find(Target).Offset(, 1).Resize(, 3).Value
        
    Application.EnableEvents = True
  End If
End Sub

NB. Het 'declareren' van variabelen smelt als sneeuw voor de zon.....