Update gegevens in datablad

[Welt]

Beste,

Ik kom er niet uit... Ik heb bijgevoegd een excel bestand waarin 2 tabbladen zijn, formulier en database.

Ik wil eigenlijk bij de statussen waardes (tekst) toevoegen danwel aanpassen in het formulier. als dat gedaan is dan wil ik dat die het bijwerkt op de juiste plaats in de database.

vbl.

Bedrijf 15
status 1 : belcontact gehad
status 2 : leeg

Dus als ik in formulier intoets bij status 2
"weer belcontact gehad"

Dan moet die het bijwerken in de database bij de rij van bedrijf 15 en de kolom van status 2... maar hoe.Bekijk bijlage update msgbox hoe.xlsm

Kan iemand mij hiermee helpen?

Alvast hartstikke bedankt.

 

[arnoo7]

Wat jij wil kan denk ik niet. De kaart wordt nu gevuld door opzoekformules. Als je in die cellen wat intikt ben je de formule kwijt.

Je kan wel vanuit andere cellen de database aanvullen. Hierbij een voorbeeld hoe ik het aan zou pakken. Wellicht hebben anderen nog andere oplossingen.
Ik heb een aantal cellen een naam gegeven, dat werkt handiger als je later gaat schuiven.

Je kunt er voor kiezen om de kaart te vullen middels een macro. Dat werkt in feite op de zelfde manier. Waarde ophalen uit een cel en wegschrijven in een andere cel.

Is dit wat je bedoelt?

P.S. Ik heb 2 tabbladen weggegooid, anders werd het bestand net te groot om te uploaden.

 

[Welt]

Reactie bericht

Beste Arnoo,

Alvast hartstikke bedankt hiervoor.

Echter, wanneer ik de code kopieer in het daadwerkelijke bestand krijg ik een objectvariabele of blokvariabele with is niet ingesteld.

(is hetzelfde bestand alleen dan groter).

hoe kan dit en of kan ik dit oplossen??

 

[arnoo7]

Hallo Welt,

Moeilijk te zeggen van een afstandje. Ik weet niet wat je aangepast hebt.

Ik heb een aantal cellen een naam gegeven in de excel. Die komen terug in de macro. Heb je deze aangepast in jouw grotere spreadsheet?
Daarnaast: heb je de ranges e.d. in de macro aangepast naar de goede ranges in jouw spreadsheet. Kloppen de tabbladnamen nog?

Anders wellicht even de code delen wat je er van gemaakt hebt.

 

[Welt]

Beste Arnoo,

Sorry voor de overlast . hierbij de code zoals die nu is...

Option Explicit

Sub StatusToevoegen()

Dim Klant As String, Toevoegstatus As String, Toevoegdatum As String, Toevoegen As String
Dim Statusdatum As Date
Dim Zoekrange As Range, Zoekcel As Range, Zoekrange2 As Range, Zoekcel2 As Range
Dim Kolom As Integer, Rij As Integer


Klant = Range("Klant").Value 'Klant = D4
Toevoegstatus = Range("Toevoegstatus").Value '=C31
Toevoegen = Range("Toevoegen").Value '=D31
Statusdatum = Range("Statusdatum").Value '=E31

Worksheets("Database").Activate
Set Zoekrange = Range("R1:AG1")
Set Zoekcel = Zoekrange.Find(What:=Toevoegstatus, MatchCase:=True, Lookat:=xlWhole)

Set Zoekrange2 = Range("C4:C963")
Set Zoekcel2 = Zoekrange2.Find(What:=Klant, MatchCase:=True, Lookat:=xlWhole)

Kolom = Zoekcel.Column
Rij = Zoekcel2.Row

Cells(Rij, Kolom).Value = Toevoegen
Cells(Rij, Kolom + 1).Value = Statusdatum

Range("Toevoegen").ClearContents

Worksheets("Formulier").Activate


End Sub

Ik heb tevens de cellen die jij gemaakt hebt ook gewijzigd van naam...

 

[arnoo7]

De macro loopt vast op het stukje waar het Kolomnummer of Rijnummer bepaald wordt.

Kolom = Zoekcel.Column
Rij = Zoekcel2.Row

Als hij de waarden in de velden Toevoegstatus en Klant niet kan vinden in de ranges R1 tot AG1 en C4 tot C961 in het tabblad database. Check dus even goed of deze celen gevuld zijn en of deze waardes cf een waarde in de genoemde ranges heeft.

De code impliceert dat de klant in de database staat en dat de Status (bv Status 3) ook in de kolommen voorkomt.

 

[Welt]

beste Arnoo,

Er staan dubbele namen in het bestand. het kan zijn dat bedrijf 7 twee keer voorkomt. maar dan wel op een andere datum...

De bovenste drie rijen zijn leeg...

Kan je hier wat mee?

 

[arnoo7]

Nee, dat maakt niet uit voor de werking van de macro. In de zin dat hij de waarde in een cel gaat opzoeken in een range. Dus als die waarde erin zit kan hij bepalen in welke kolom/welke rij. Dus check nog even goed of dat het geval is.

Wat wel een dilemma is: als een waarde meerdere keren in een range voorkomt, start hij in het begin en stopt hij bij de eerste de beste. En het is dus niet gezegd of dat de goede is, want wellicht wil je de 2 of 10e verschijning van die klant hebben.

Maar dat heb je ook bij het aanmaken van het formulier. Als ik in jouw voorbeeldbestand Bedrijf 1 verander in Bedrijf 2 vult hij het formulier altijd met de data van de BMW 3. Wat doe je dan als ik de Renault Kangoo kaart wil zien? Dan zul je nog een kenmerk moeten toevoegen. Klant 1 Aanvraag 2 (of aanvraagdatum, maar dan moet je zeker weten dat de klant niet 2 aanvragen op 1 dag kan doen). In elk geval moet je uniek maken over welk record je het hebt. Hoe heb je dat bij het formulier opgelost?

 

[Welt]

he arnoo,

alle waardes moeten dus uniek zijn. duidelijk, dat is / was nu nog niet zo omdat daar geen noodzaak toe was.

Ik zal eerst is proberen om alles uniek te maken en te kijken of het dan wel lukt.

is er dan een manier om wel (in het formulier) een melding te geven van dat het bedrijf al eens eerder ingevoerd is ? Database heeft ongeveer 1200 rijen dus anders wordt t zoekwerk.

 

[arnoo7]

Hoi Welt,

Ik denk dat die noodzaak er al wel was. Want voor het aanmaken van het juiste formulier kun je ook niet alleen afvragen op klant. Als de klant 10 auto's heeft. Hoe weet excel dan van welke auto je het formulier je wilt zien? Maar ik ken niet helemaal de opzet/doelstelling van je originele bestand natuurlijk.

Ik zou in de database een uniek kenmerk toevoegen. Voor een auto lijkt het kenteken mij het meest logisch. Maar een andere mag ook natuurlijk

Hoe ga je de database vullen? Is dat rechtstreeks in de database een regel tikken als iemand een auto neemt? Dan zou ik een aparte lijst met klanten aanleggen (op alfabet?). En dan kolom C van de database vullen uit de lijst met gegevensvalidatie. Want anders krijg je verschillen Jansen B.V. is volgens excel wat anders dan Jansen BV. En dan heb je al snel heel veel klanten. (ik heb het nu in het tabblad Formulier gezet om cel C4 te vullen. Ik heb een oude versie van excel, die kan de lijst niet uit een ander tabblad halen. Versies na 2007 kunnen dit wel) Als je dergelijke lijsten "verstopt" in een apart tabblad houdt dit je spreadsheet een beetje overzichtelijk. Geldt ook voor hulpveldjes zoals veld M1 in tabblaf formulier. Als je dan de klantdata in een apart tabblad zet kun je er uit dit tabblad ook de kvk, telefoon e.d. bijzoeken voor je uiteindelijke database. Voorkom je daar ook tikfouten in. Persoonlijk zou ik van D4 (kenteken) ook een gevalideerd veld maken met een lijst die hij uit kolom AO haalt in de database. De combobox voegt niet zoveel extra toe.

Om niet te teveel te hoeven zoeken zou je wellicht een filter toe kunnen voegen. Ik heb een voorbeeldje hiervan opgenomen in bijgaand bestandje. Dan selecteer je in C4 de klant. In de combobox verschijnen dan de auto's die deze klant heeft. Selecteer de juiste auto en voila, formulier weet welke auto je zoekt. Daarna alle indexformules laten zoek op het kenteken ipv op de klantnaam. Het macrootje om te filteren vind je bij Ontwikkelaars in het tabbladje Formulier. Gaat draaien als je cel C4 wijzigt. Wellicht kun je het nog wat beter laten werken door te werken met dynamische ranges e.d. maar om het niet te complex te maken verder niet naar gekeken.

Is zoiets toepasbaar voor je? Het is vaak een beetje puzzelen wat handig is.

Ik ben geen groot VBA kenner dus wellicht zijn er betere oplossingen. Hebben anderen nog praktische oplossingen voor Welt?

 

[arnoo7]

PS, als je in de database zelf wil zien of een bedrijf vaker voorkomt kun je toch get gemakkelijkste een filter op de bovenste regel plaatsen?

 

[VenA]

@arnoo7,
Veel terechte vragen. Om de status weg te schrijven kan je met wat minder code af.:d

Sub VenA()
Dim r
With Sheets("Database")
    r = .Columns(8).SpecialCells(2).Find([D4]).Row
    .Cells(r, 19).Offset(, Application.CountA(.Cells(r, 19).Resize(, 16))).Resize(, 2).Value = Array([D31], [E31])
End With
End Sub

 

[arnoo7]

Bedankt, die resize stukken heb ik nog niet helemaal. Ga ik nog even rustig op studeren. Het is sowieso een snelle manier om de rijnummers en evt kolomnummers te vinden.

 

[HSV]

Ik zou het iets robuuster maken.

Sub hsv()
Dim c As Range
 With Sheets("Database")
     Set c = .Columns(8).Find([D4])
     If Not c Is Nothing Then
       c.Offset(, Application.CountA(c.Offset(, 11).Resize(, 16)) + 11).Resize(, 2).Value = Array([D31], Format([E31], "short date"))
     End If
 End With
End Sub

 

[Warme bakkertje]

Aangezien er dubbele waarden kunnen voorkomen in kolom Bedrijfsnaam schreeuwt dit toch om een Userform.
1 Combobox met unieke bedrijfsnamen, 1 Listbox met het gefilterde resultaat per gekozen bedrijfsnaam, 8 (of 16) Textboxen (afhankelijk of je de datum ook wil kunnen aanpassen per status)om de status in te vullen en 1 Opdrachtknop om de gegevens naar de correcte rij weg te schrijven.

 

[Welt]

Beste Allen (ondertussen),

Vriendelijk dank voor de reacties en hulp.

Veel hulp voor een leek zoals ik, de listbox snap ik niet helemaal, wat wel een unieke code kan zijn / worden is bijvoorbeeld audi a1 & audi a1 2 etc etc. hoe maak ik dan een listbox op het formulier zelf zodat deze wel doet wat het formulier moet doen, namelijk de statussen ophalen van een bepaald bedrijf bij een bepaalde regel....

 

[Warme bakkertje]

Heb snel even iets in elkaar gestoken.
Klik op Userform tonen, selecteer een bedrijf in de bovenste Combobox. In de Listbox krijg je nu een lijst met alle voertuigen verbonden aan dat bedrijf.
Klik nu op een van de regels in de Listbox en alle textboxen worden gevuld met de status van die wagen. Pas het nodige aan en klik dan op de Commandbutton zodat de aangepaste gegevens terug naar de database geschreven worden.
Dit Userform kan je ook uitbreiden zodat alle gegevens getoond kunnen worden die betrekking hebben op een geselecteerde wagen zodat je uiteindelijk je werkblad Formulier eigenlijk niet meer nodig hebt.

 

[Welt]

Beste,

Ziet er goed uit, alleen het volgende...

Ik zou graag juist wel het tabblad formulier gebruiken. Deze kan ik dan bijvoorbeeld veel netter printen e.d..

Is het misschien mogelijk, en hoe is het mogelijk, om op het tablad formulier te zetten dat als het die klant is, dan ga je zoeken in Database in kolom G:G naar de auto's die bij die klant hoort?.

 

[Warme bakkertje]

Zelfde principe maar nu op je werkblad Formulier.

 

[Welt]

beste,

Bij jou werkt het zo mooi, bij mij echter niet.

Het database bestand heeft ongeveer 1200 records, hoe zorg ik er voor dat de user form die op formulier staat niet selecteert op kenteken maar op auto en daarnaast, hoe voeg ik dan statussen toe want ik heb nog de melding dat de dimension niet klopt...