*

Help!

PC-Problemen?
De vrijwilligers van Oplossing.be zoeken gratis met u mee!

Hulp bij posten

Recente topics

Pagina's: [1]   Omlaag

Auteur Topic: formule met externe verwijzing automatisch aanpassen  (gelezen 36929 keer)

0 leden en 1 gast bekijken dit topic.

Offline rdcoster

  • Ervaren lid
  • ***
  • Berichten: 274
  • Geslacht: Man
  • Oplossing.be
formule met externe verwijzing automatisch aanpassen
« Gepost op: 25 februari 2019, 07:05:28 »
Ik heb een 'gegevens' bestand op locatie C:\Users\Rene\Documents\Oplossing_be\2018\

In een ander bestand 'Rapport' publiceer ik de data uit bestand 'gegevens' zonder het te openen.

Dit met de formule ='C:\Users\Rene\Documents\Oplossing_be\2018\[gegevens.xlsx]Blad1'!A1

Omdat de gegevens dit jaar in map Documents\Oplossing_be\2019\ zitten
wil ik in die formule de vaste 2018 vervangen door een variabele van het tabblad 'Instellingen C10'

Zie 3 bijlagen

Gelogd
Windows 10
Excel 2013

Offline Ex-lid

  • Oplosser
  • ****
  • Berichten: 670
  • Geslacht: Man
  • dit lid is verbannen
Re: formule met externe verwijzing automatisch aanpassen
« Reactie #1 Gepost op: 25 februari 2019, 13:12:24 »
Beste,
Ik heb hier een VBA oplossing.
Zie bijlage.
Code is te vinden in module 1
Gelogd

Offline rdcoster

  • Ervaren lid
  • ***
  • Berichten: 274
  • Geslacht: Man
  • Oplossing.be
Re: formule met externe verwijzing automatisch aanpassen
« Reactie #2 Gepost op: 25 februari 2019, 13:39:12 »
Beste Dotchiejack,

Ik heb jouw oplossing uitgetest maar in mijn geval is dit niet bruikbaar.
In bijlage een deel screenshot waar de formules zichtbaar zijn.

Het is in de omkaderde cel dat ik de vaste 2018 wil vervangen door de waarde van cel C10 van blad 'Instellingen'.
Gelogd
Windows 10
Excel 2013

Offline Ex-lid

  • Oplosser
  • ****
  • Berichten: 670
  • Geslacht: Man
  • dit lid is verbannen
Re: formule met externe verwijzing automatisch aanpassen
« Reactie #3 Gepost op: 25 februari 2019, 15:03:13 »
Ik begrijp goed wat je wilt bereiken, maar doordat je met een gesloten bestand werkt ben je beperkt, vb INDIRECT werkt niet met een gesloten bestand.
Hopelijk heeft iemand een beter idee, ik weet ook niet alles,ik ben Google niet ;)
Gelogd

Offline SoftAid

  • Administrator
  • Ambassadeur
  • *****
  • Berichten: 20.172
  • Geslacht: Man
  • Nobody is perfect, not even me...
Re: formule met externe verwijzing automatisch aanpassen
« Reactie #4 Gepost op: 25 februari 2019, 15:08:03 »
Misschien te simpel, maar werkt een "Zoeken en Vervangen" ook niet?

:) SoftAid :)             
Gelogd
Maximum grootte bijlagen vergroot naar 4 MB
Dubbelposten, het posten op verschillende forums van dezelfde vraag, dient op
voorhand gemeld te worden, met een link naar het topic op de andere site.
Overtreding van deze regel kan bestraft worden met verbanning !

Offline Ex-lid

  • Oplosser
  • ****
  • Berichten: 670
  • Geslacht: Man
  • dit lid is verbannen
Re: formule met externe verwijzing automatisch aanpassen
« Reactie #5 Gepost op: 25 februari 2019, 15:16:39 »
Citaat
Misschien te simpel, maar werkt een "Zoeken en Vervangen" ook niet?
Dit is wat ik ook zou doen,Ctrl + H en gaan met die banaan ;D, maar TS wilt een dynamische gegeven en niet hard gecodeerd.
Gelogd

Offline rdcoster

  • Ervaren lid
  • ***
  • Berichten: 274
  • Geslacht: Man
  • Oplossing.be
Re: formule met externe verwijzing automatisch aanpassen
« Reactie #6 Gepost op: 25 februari 2019, 16:32:18 »
Inderdaad, met zoeken en vervangen gaat dit zeker !
Maar ik moest een versie maken waar Excel-leken dit getal maar in te vullen hebben.

OK, voorlopig dus met deze oplossing.

Dank voor het meedenken,
Mvg
René
Gelogd
Windows 10
Excel 2013

Offline pitufo

  • Ambassadeur
  • *****
  • Berichten: 1.343
  • Geslacht: Man
Re: formule met externe verwijzing automatisch aanpassen
« Reactie #7 Gepost op: 25 februari 2019, 18:44:12 »
Hallo,

Ik heb ook even een poging gedaan om méé te denken.
Mijn oplossing is wel gebonden aan de voorwaarde (maar het is niet ondenkbaar dat dit een realistisch scenario is) dat er telkens wordt geswitcht naar een volgend jaar.
In dat geval (en anders verzinnen we wel iets anders...) volstaat deze code op tabblad Instellingen :
Code: [Selecteer]
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect([C10], Target) Is Nothing Then
    Sheets("Rapport").Range("C8:C17").Replace What:=Trim(Str(Target.Value - 1)), Replacement:=Target, LookAt:=xlPart
End If

End SubC10 en C8:C17 heb ik genomen uit je eerste voorbeeld. Die zal je mogelijk dus moeten aanpassen.

Groeten,
pitufo

Gelogd
"De computer doet wel degelijk wat je hem vraagt,
 maar NIET wat je DENKT dat je hem vraagt"

Offline rdcoster

  • Ervaren lid
  • ***
  • Berichten: 274
  • Geslacht: Man
  • Oplossing.be
Re: formule met externe verwijzing automatisch aanpassen
« Reactie #8 Gepost op: 25 februari 2019, 20:09:57 »
@pitufo,
Uw oplossing werkt !

Zou dit nog kunnen ge-finetuned worden zodat ALLEEN de 2018 vervangen wordt ALS die tussen 2 backshlashes staat (dus alleen in een formule-verwijzing). Zodat een waarde =2018 in het gegeven bereik niet vervangen wordt.

PS: waarom werkt dit niet voor een vorig jaar ?
Gelogd
Windows 10
Excel 2013

Offline RedHead

  • Excel-Expert
  • Ambassadeur
  • *****
  • Berichten: 2.279
  • Geslacht: Man
  • Met Excel lukt 't wel.... (toch???)
Re: formule met externe verwijzing automatisch aanpassen
« Reactie #9 Gepost op: 25 februari 2019, 22:36:09 »
Probeer deze maar 'ns uit in een kopie van je workbook...
Code: [Selecteer]
Sub VervangJaartal()
Dim AlleFormules As Range

    On Error Resume Next
    Set AlleFormules = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
    If AlleFormules Is Nothing Then
        MsgBox "geen formules in de sheet gevonden...", vbOKOnly, "Heu???"
        Exit Sub
    End If
    Application.Calculation = xlCalculationManual
    For Each c In AlleFormules
        c.Formula = Replace(c.Formula, "\2018\", "\2019\")
    Next c
    Application.Calculation = xlCalculationAutomatic

End Sub
Gelogd
______________________________

Groet, Leo

Offline Ex-lid

  • Oplosser
  • ****
  • Berichten: 670
  • Geslacht: Man
  • dit lid is verbannen
Re: formule met externe verwijzing automatisch aanpassen
« Reactie #10 Gepost op: 25 februari 2019, 22:38:44 »
@ redhead,
Deze vind ik onbetaalbaar  :D :D :D :thumbsup: :thumbsup:
Citaat
Heu???
Gelogd

Offline rdcoster

  • Ervaren lid
  • ***
  • Berichten: 274
  • Geslacht: Man
  • Oplossing.be
Re: formule met externe verwijzing automatisch aanpassen
« Reactie #11 Gepost op: 26 februari 2019, 06:59:07 »
@ RedHead,

De code werkt perfect !
Ik heb alleen de vaste 2018 en 2019 vervangen door een variabele.
Code: [Selecteer]
For Each c In AlleFormules
    c.Formula = Replace(c.Formula, "\" & oudjaar & "\", "\" & nieuwjaar & "\")
    Next c
Bedankt ! Ik markeer topic als opgelost

Groeten, Heu???

René

Gelogd
Windows 10
Excel 2013

Offline RedHead

  • Excel-Expert
  • Ambassadeur
  • *****
  • Berichten: 2.279
  • Geslacht: Man
  • Met Excel lukt 't wel.... (toch???)
Re: formule met externe verwijzing automatisch aanpassen
« Reactie #12 Gepost op: 26 februari 2019, 07:00:24 »
@Dotchie, LOL.  ;D
Ik moet je zeggen dat ik vaker dit soort teksten in mijn msgboxen gebruik. Of het moet een serieus programma zijn natuurlijk. Maar anders vind ik dat een heel stuk gezelliger. Grappig dat je dat opviel.
Gelogd
______________________________

Groet, Leo

Offline RedHead

  • Excel-Expert
  • Ambassadeur
  • *****
  • Berichten: 2.279
  • Geslacht: Man
  • Met Excel lukt 't wel.... (toch???)
Re: formule met externe verwijzing automatisch aanpassen
« Reactie #13 Gepost op: 26 februari 2019, 07:07:41 »
Citaat van: rdcoster op 26 februari 2019, 06:59:07
De code werkt perfect !
Da's heel mooi.
Citaat
Ik heb alleen de vaste 2018 en 2019 vervangen door een variabele.
Had ik ook nog over nagedacht, maar meestal doe ik dat niet in een procedure die slechts 1x per jaar nodig is en verder dus niet zoveel toevoegt aan het geheel. Of je nou 2 extra variabelen inzet óf het rechtstreeks in de regel zelf aanpast is om het even. Maarrrr... jij moet er mee werken en gelukkig van worden. Dus prima gedaan!  ;)
Gelogd
______________________________

Groet, Leo

Offline pitufo

  • Ambassadeur
  • *****
  • Berichten: 1.343
  • Geslacht: Man
Re: formule met externe verwijzing automatisch aanpassen
« Reactie #14 Gepost op: 26 februari 2019, 13:04:52 »
Hoi,

Half onder de noemer "vijgen na pasen" :

Citaat
Zodat een waarde =2018 in het gegeven bereik niet vervangen wordt.
Dat zou niet gebeurd zijn als je mijn advies helemaal had opgevolgd :
Citaat
C8:C17 heb ik genomen uit je eerste voorbeeld. Die zal je mogelijk dus moeten aanpassen.
Die range is degene waarin formules staan. Die kan je of zelf eenmalig aanpassen, of het stukje code van RedHead invoegen waarmee alle formules worden gezocht.

Citaat
waarom werkt dit niet voor een vorig jaar ?
Omdat mijn code niet door de gebruiker in gang moet worden geduwd. Om het in alle omstandigheden te laten werken zou je ergens één extra cel moeten voorzien op Instellingen. Maarre... wanneer ga je dat eens moeten gebruiken ?

@ RedHead,

Citaat
Ik moet je zeggen dat ik vaker dit soort teksten in mijn msgboxen gebruik.
Ik doe dat soms ook in de projecten die ik voor mijn collegaatjes ontwikkel, en verwerk dan ook graag Application.UserName in mijn msgbox.
Hilariteit gegarandeerd !
Gelogd
"De computer doet wel degelijk wat je hem vraagt,
 maar NIET wat je DENKT dat je hem vraagt"
Pagina's: [1]   Omhoog
 


www.combell.com