excel formule vertalen naar VBA

[Cyrax]

probleem:

ik heb een facturatieprogramma, dat zijn data haalt (artikels, klanten, ...) uit een file DATADUMP.xls

in diezelfde DATADUMP is een sheet 'factuurnummers'.

ik heb een formule in excel gemaakt die het eerste ongebruike factuurnummer zoekt, en dit invult op de juiste plaats.

=INDEX('[datadump.xls]facturen BCF'!$A$1:$B$999;VERGELIJKEN("unused";'[datadump.xls]facturen BCF'!$B$1:$B$999;ONWAAR);1)

hoe kan ik deze code nu 'vertalen' naar VBA, zodat excel bij het klikken op een knop niet enkel de file exporteert naar PDF, een hard copy maakt (is allemaal al gebeurd), maar ook het factuurnummer dat hij nu gebruikt aanvinkt als zijnde 'gebruikt' (dit gebeurt doormiddel van er een andere tekst dan "unused" achter te zetten.)

ik moet dus een soort van VLOOKUP vertalen naar VBA ...


zelfde vraag voor het stockbeheer:

bij het drukken op diezelfde knop had ik graag dat ook de gefactureerde aantallen van de stocklijst gaan.

ik moet dus weeral die VLOOKUP gebruiken om in de juiste rij te geraken in mijn stocklijst, maar hoe zoek ik dan naar de eerste lege cel in die rij, zodat ik er mijn waarde in kan kopieren ?.

groetjes

 

[xlcoach]

Met de volgende code bepaal je alle blanco cellen in de range r:

    On Error Resume Next
    Set rBlanks = r.SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0

rBlanks kan nu Nothing zijn, zoniet dan zet je zo de huidige datum in de eerste blanco cel:
rBlanks.cells(1) = Date

Zie evt ook Find en/of WorksheetFunction.Lookup in de vba-help.

 

[Warme bakkertje]

Voor je faktuurnummer

Sub Zoeken()
c = [A1].Value 'Wijzig A1 in cel met faktuurnummer
    With Workbooks("datadump.xls").Worksheets("facturen BCF").[A1:A999]
        Set n = .Find(c, LookIn:=xlValues)
            n.Offset(, 1).Value = "Used"
    End With
End Sub

Voor je stocklijst

Sub Stocklijst()
For Each c In [A1:A20] 'wijzig in bereik met artikels
aantal = c.Offset(, 2).Value 'wijzig in kolom met aantallen
    With Sheets("Stocklijst").[A1:A999]
        Set n = .Find(c, LookIn:=xlValues)
           n.End(xlToRight).Offset(, 1).Value = aantal
    End With
Next
End Sub

Mvg

Rudi

 

[Cyrax]

Voor je faktuurnummer
Voor je stocklijst

Sub Stocklijst()
For Each c In [A1:A20] 'wijzig in bereik met artikels
aantal = c.Offset(, 2).Value 'wijzig in kolom met aantallen
    With Sheets("Stocklijst").[A1:A999]
        Set n = .Find(c, LookIn:=xlValues)
           n.End(xlToRight).Offset(, 1).Value = aantal
    End With
Next
End Sub

hi rudi, factuurnr werkt super, thanks.

bij stocklijst heb ik een probleempje waar ik me al vrij suf heb opgestaard.

deze heb ik nu:

' boek goederen in
    For Each y In [AO24:AO32] 'wijzig in bereik met artikels
        aantal = y.Offset(, 2).Value 'wijzig in kolom met aantallen
    With Workbooks("datadump.xls").Worksheets("Stock MIN").[A1:IV999]
        Set n = .Find(y, LookIn:=xlValues)
           n.End(xlToRight).Offset(, 1).Value = aantal
    End With
    Next

mijn goederen en aantallen staan in bereik AO24:AP32 (AO = artikelnrs, AP = aantallen)
ik heb de 'c' vervangen door een 'y', omdat ik simpelweg al een c had gebruikt

nu krijg ik een foutmelding op 'n.End(xlToRight).Offset(, 1).Value = aantal'.
de waarden die ik te zien krijg zijn '-4161' bij xlToRight en 'leeg' bij aantal ...

de goederen moeten weggeboekt in bereik A1:IV999. in kolom A staan de artikelnummers, in bereik B1:IV999 staan de respectievelijke aantallen die in mindering moeten gebracht. (deze worden dan in IW opgeteld ...)

kan je me nog ff een duwtje geven ? :love:

 

[snb]

Er is toch geen kolom IW ? (t/m Excel 2003)
Is het echt nodig 256 kolommen te gebruiken (vanwege de traagheid en omvang)

Code-suggestie

Sub Stocklijst()
  For Each c In [A1:A20]
    [Stocklijst!.A1:A999].Find(c, LookIn:=xlValues).End(xlToRight).Offset(, 1).Value =  c.Offset(, 2).Value 
  Next
End Sub

 

[Cyrax]

Er is toch geen kolom IW ? (t/m Excel 2003)
Is het echt nodig 256 kolommen te gebruiken (vanwege de traagheid en omvang)

klopt, foutje van de firma... optelling gebeurt dus in kolom IV.

ik heb op zich wel redelijk kolommen veel nodig. in mijn "datadump" staan alle data die de administratie van ons bedrijf nodig heeft. productoverview, klantenbestand, kortingspercentages per klant, ...
en ook Stoc PLUS (als we artikelen binnenboeken)
Stock MIN (als er buitengaan, zoals op de factuur)
en Stock TOTAAL, wat simpelweg PLUS - MIN is. ik heb dus zoveel kolommen nodig omdat er elke dag bijna wel een of meerdere verrichtingen gebeuren met die stocklijst

die info wordt allemaal opgeroepen via kleinere, doelgerichte sheets. (facturatieprogramma, informatiefiche product, ...)

 

[Warme bakkertje]

Mijn excuses,ik had geen rekening gehouden met lege regels i/h bereik

Sub Stocklijst()
    For Each y In [AO24:AO32] 
        If Not IsEmpty(y) Then
            With Workbooks("datadump.xls").Worksheets("Stock MIN").[A1:A999]
                .Find(y, LookIn:=xlValues).End(xlToRight).Offset(, 1).Value = y.Offset(, 1).Value
            End With
        End If
    Next
End Sub

Mvg

Rudi

 

[Cyrax]

sorry rudi

nog steeds geeft hij een foutmelding


.Find(y, LookIn:=xlValues).End(xlToRight).Offset(, 1).Value = y.Offset(, 1).Value

fout 1004 zegt 'ie dan ...

en eerlijk waar, deze code is chinees voor mij

 

[Warme bakkertje]

Geeft hij nog een andere melding buiten fout 1004 ?
Uileg coderegel: vind waarde y in Bereik A1:A999 >> bij gevonden waarde ga rechts naar de laatsgevulde cel in de gevonden rij >> in de lege cel rechts daarvan plaats je het aantal v/d verkochte goederen

Mvg

Rudi

 

[Cyrax]

Geeft hij nog een andere melding buiten fout 1004 ?
Uileg coderegel: vind waarde y in Bereik A1:A999 >> bij gevonden waarde ga rechts naar de laatsgevulde cel in de gevonden rij >> in de lege cel rechts daarvan plaats je het aantal v/d verkochte goederen

Mvg

Rudi

thx voor de uitleg ... wordt het n beetje duidelijker ..

buiten de 'omschrijving' van die error is dat het enige wat er gebeurd.
de files staan op mn pc op het werk, dus kan er atm niet bij ... pas weer maandagochtend ...


ik zal kijken of ik dan mss een voorbeeldje kan posten ??

 

[Warme bakkertje]

En wat was die omschrijving ?
Heb het eens gereconstrueerd en loopt hier als een trein, dus moeten we het werkend krijgen.

Mvg

Rudi

 

[Cyrax]

En wat was die omschrijving ?
Heb het eens gereconstrueerd en loopt hier als een trein, dus moeten we het werkend krijgen.

Mvg

Rudi

zo, ben weer op kantoor: de melding die ik krijg is de volgende.

FOUT 1004 tijdens uitvoering:
Door de toepassing of door object gedefinieerde fout.

kan je hier iets mee, of heb je graag een voorbeeldbestandje ?

 

[Cyrax]

hhmm,

nu ben ik bezig een voorbeeldbestandje te maken ... en nu werkt dat als een trein ???

het feit dat de macro in mn algemene macro-file staat maakt toch niets uit he ??
ik heb nu ff de macro van factuur en die van mn voorbeeld uitgeprint .. identiek hetzelfde ding (buiten wat bereiken aangepast uiteraard).

het enige wat "anders" is, is dat het eigenlijke facturatieprogramma data moet uitvoeren naar een andere file (datadump.xls), terwijl in het voorbeeldbestandje de 'datafile' gewoon een ander werkblad is ... ???

 

[Warme bakkertje]

Post de macro eens terug zoals jij hem aangepast hebt. Dus met de bereiken zoals ze moeten opgezocht en weggeschreven worden

Mvg

Rudi

 

[Cyrax]

dit is m zoals hij helemaal is. het gedeelte voor factuurnr weg te boeken staat in 'commentaar', moet je je verder niets van aantrekken.

Sub boeken()

    'boek factuurnummer in
    
    'If [AA3].Value = "1" Then
        'c = [J15].Value 'is gebruikt factuurnummer
        'd = [AA20].Value 'is factnr + klant
        'With Workbooks("datadump.xls").Worksheets("facturen ITS").[A1:A999]
        '    Set n = .Find(c, LookIn:=xlValues)
        '        n.Offset(, 1).Value = d
        'End With
    'Else
        'c = [J15].Value 'is gebruikt factuurnummer
        'd = [AA20].Value 'is factnr + klant
        'With Workbooks("datadump.xls").Worksheets("facturen BCF").[A1:A999]
        '    Set n = .Find(c, LookIn:=xlValues)
        '        n.Offset(, 1).Value = d
        'End With
    'End if
    
    ' boek data
    For Each y In [AO24:AO32]
        If Not IsEmpty(y) Then
            With Workbooks("datadump.xls").Worksheets("stock MIN").[A1:A999]
                .Find(y, LookIn:=xlValues).End(xlToRight).Offset(, 1).Value = y.Offset(, 1).Value
            End With
        End If
    Next

in AO24:AO32 staan de artikelnrs in een kolommetje (in de vorm xxx-xxxx-xxx), daarachter staan de aantallen.

 

[Warme bakkertje]

Plaats het wegschrijfstuk eens in een aparte sub in een aparte module en draai hem dan eens want ik heb hier van alles gesimuleerd en dat ding blijft het doen

Mvg

Rudi

 

[Cyrax]

ik heb het stuk voor de voorraad te boeken weggezet.

de hele macro staat in publieke file, het voorraad_boeken subje heb ik een aparte module gezet.

eerst geprobeerd die aparte sub op te roepen vanuit de andere macro, zelfde fout.
daarna enkel die aparte sub opgeroepen, zelfde fout ...

*zucht* ik staar me der helemaal blind op ...

* even een omwegje proberen dan ... *
--EDIT-- omwegje werkt ook niet ...

 

[snb]

Specificeer op welk werkblad de gegevens staan

For Each y In [[B][COLOR="Red"]Blad1![/COLOR][/B]AO24:AO32].specialcells(xlcelltypeConstants)
   With Workbooks("datadump.xls").['stock Min'!A1:A999]
      .Find(y, ,xlValues).End(xlToRight).Offset(, 1).Value = y.Offset(, 1).Value
    End With
Next

 

[Cyrax]

Specificeer op welk werkblad de gegevens staan

For Each y In [[B][COLOR="Red"]Blad1![/COLOR][/B]AO24:AO32].specialcells(xlcelltypeConstants)
   With Workbooks("datadump.xls").['stock Min'!A1:A999]
      .Find(y, ,xlValues).End(xlToRight).Offset(, 1).Value = y.Offset(, 1).Value
    End With
Next

deze krijg ik dan:

Sub voorraad_factuur_boeken2()
    ' boek data
For Each y In ['invoice'!AO24:AO32].SpecialCells(xlCellTypeConstants)
   With Workbooks("datadump.xls").['stock MIN'!A1:A999]
      .Find(y, , xlValues).End(xlToRight).Offset(, 1).Value = y.Offset(, 1).Value
    End With
Next
End Sub

error 1004: er zijn geen cellen gevonden (focus komt op de 1e regel)

bedankt voor de hulp btw ... elke keer weer n stapje dichter ...

 

[Warme bakkertje]

snb,daar had ik ook al aan gedacht maar waarom werkt hij dan bij mij zonder bladspecificatie ?

Mvg

Rudi