[Excel] getal en tekst tegelijk vervangen

[Beamer]

In excel heb je de funtie vervangen. Ik wil deze functie nu direct toepassen op bijvoorbeeld zowel het cijfer 6 als hoofddorp, dit wil ik dan veranderen in 7 Rotterdam (bijvoorbeeld).
Ik kan niet eerst alleen het cijfer vervangen want als ik dat doe zegt excel van 7 is niet bekend in Hoofddorp en stopt hij. Ik moet dus Hoofddorp ook direct kunnen wijzigen in Rotterdam want alleen in Rotterdam is 7 bekend.

Ik hoop dat ik het zo duidelijk genoeg heb gemaakt, met dit simpele voorbeeld.

Maar het komt er dus op neer dat ik 2 dingen tegelijk wil vervangen in 1 cel, ik heb bij vervangen al geprobeerd worden te scheiden door ; "" maar dit allen werkte niet. Hoop dat jullie me verder kunnen helpen.

[Reptile209]

Het is me volledig niet duidelijk wat je nou bedoelt. Heb je een cel met daarin "6 Hoofddorp" en moet dat "7 Rotterdam worden"? Dan is het gewoon Zoeken en Vervangen: Zoeken naar 6 Hoofddorp, Vervangen door 7 Rotterdam.
Of bedoel je dat er een cel is met "6 [bladiebla] Hoofddorp" en zit je dus met die bladiebla in je maag? Doe es een voorbeeldje

[Beamer]

Reptile209 schreef op 01 October 2003 @ 11:52:
Het is me volledig niet duidelijk wat je nou bedoelt. Heb je een cel met daarin "6 Hoofddorp" en moet dat "7 Rotterdam worden"? Dan is het gewoon Zoeken en Vervangen: Zoeken naar 6 Hoofddorp, Vervangen door 7 Rotterdam.
Of bedoel je dat er een cel is met "6 [bladiebla] Hoofddorp" en zit je dus met die bladiebla in je maag? Doe es een voorbeeldje

Ja mis niet duidelijk genoeg excuses.

Ja er zit inderdaad een hoop bla bla bla tussen. Het gaat namelijk om verwijzingen naar andere excel bestanden, als ik dus zeg bagger bla bla blab bla sheet 6 moet shit bla bla bla sheet 6 worden, zegt hij van in sheet 6 is geen tabblad bagger aanwezig en stopt hij.

In die andere bladen staan per station totalen en de bijbehorende opbouw, de financial controller is nu een blad aan het maken met alleen maar totalen van alle stations en als je dan iets veranderd zegt hij ja maar in die file ken ik dat station niet.
Hij heeft nu als trucje, dat hij in een excel file ff een tabblad aanmakt met hoofddorp dan herkent hij hoofddorp geet hij wel geen uitkomst van de formule (omdat dat tabblad leeg is) maar dat doet dan ff niet terzake.
Daarna kan hij doordat hij dat extra tabblad ff heeft aangemaakt, die 6 in de 7 veranderen en dan komt de formule opeens te voor schijn omdat in excel file nummer 7 ook weer tabblad hoofddorp maar wel met getallen.
Maar hij moet dus nu iedere keer in diverse files een extra tabbladje aanmaken, op zich wel slim gevonden alleen niet tijdsefficiënt als je bedenkt dat het om 80 stations gaat. Hoop dat het nu duidelijker is.

[F_J_K]

Ik denk dat ik het goed snap maar het zou vast helpen als je een compleet voorbeeld geeft

Het makkelijkst (als ik het nu goed begrijp ) lijkt me automatisch bijwerken even uit te zetten: opties -> berekenen -> handmatig of edit -> koppelingen -> handmatig bijwerken

Dan heb je namelijk geen last van controleren tijdens het wijzigen.

[Beamer]

F_J_K schreef op 01 October 2003 @ 12:16:
Ik denk dat ik het goed snap maar het zou vast helpen als je een compleet voorbeeld geeft

Het makkelijkst (als ik het nu goed begrijp ) lijkt me automatisch bijwerken even uit te zetten: opties -> berekenen -> handmatig of edit -> koppelingen -> handmatig bijwerken

Dan heb je namelijk geen last van controleren tijdens het wijzigen.

Hier hadden we zelf ook al aan ged8 (niet bijdehand bedoeld), maar dat vind ik een zelfde oplossing als dat extra tabblad (ad-hok?). Ik wil gewoon 2 dingetjes tegelijk vervangen .

Voorbeeld:
='H:\wegv\username\BUDGET 2003\[test budget 2004 A-E.xls]Bolsward'!$AB$31
moet worden veranderd in
='H:\wegv\username\BUDGET 2003\[test budget 2004 F-M.xls]Heeze'!$AB$31

Dus de filename veranderd hier van a-e in f-m (dit staat voor alle stations beginnend met die letter) en de naam van het betreffende station dit maal dus Bolsward in Heeze

Ik wil dus in 1 handeling a-e veranderen in f-m en Bolsward veranderen in Heeze!!!

Dit moet het denk zeker duidelijk maken wat ik wil.

[ Voor 37% gewijzigd door Beamer op 01-10-2003 12:43 ]

[Beamer]

schopje, had eigenlijk wel nieuwe reacties verwacht met dit duidelijke voorbeeld

[Coffeemonster]

Volgens mij moet het gewoon kunnen met zoek&vervang, waarbij je eventueel jokertekens gebruikt:

Zoeken: A-E.xls]Bolsward
Vervangen: F-M.xls]Heeze

Staat er nog andere tekst tussen die wisselt, dan kan je het vraagteken (?) gebruiken als een willekeurig karakter en de asterisk (*) als een of meer willekeurige karakters. Dit kan je ook weer gebruiken in de vervangtekst.

[Beamer]

Coffeemonster schreef op 02 October 2003 @ 13:46:
Volgens mij moet het gewoon kunnen met zoek&vervang, waarbij je eventueel jokertekens gebruikt:

Zoeken: A-E.xls]Bolsward
Vervangen: F-M.xls]Heeze

Staat er nog andere tekst tussen die wisselt, dan kan je het vraagteken (?) gebruiken als een willekeurig karakter en de asterisk (*) als een of meer willekeurige karakters. Dit kan je ook weer gebruiken in de vervangtekst.

Ziet er nuttig uit. Kan inderdaad zijn dat er soms andere tekst tussen zit, kan je hier een voorbeeld van geven wat je dan precies moet inslaan?
Thankx alvast

[onkl]

Die jokertekens kom je waarschijnlijk wel uit (werkt ongeveer als jokers in windows explorer zeg maar.
Maar, in het algemeen:
Dit wordt een chaos lijkt me. Iedere keer als je een tabblad, bestand etc hernoemd zal je totalenblad 'stuk' zijn.
Ik zou het persoonlijk als volgt aanpakken:
Zet alle bestanden met gegevens per station in een aparte map.
Schrijf een (kort) vba macrootje met ongeveer de volgende doorloop in je totalenblad:
-verwijder huidige gegevens
-maak mbv. de findfile methode een collectie van alle excel bestanden in die aparte map
-loop door die collectie en open het bestand
-loop door alle tabbladen in dat bestand
-neem de naam van het tabblad en zet die in je totalenblad neer
-pak alle gegevens die je wilt hebben(celreferenties op dat werkblad of gebruik
names) en plak die op dezelfde regel in het totalenblad
-volgende tabblad
-volgende bestand.
-evt. kan je je totalenblad nog sorteren, er een totaal onder laten zetten etc.
Voordeel is dat het niet meer uitmaakt hoe je stations heten etc., je macro pakt nu gewoon alle tabbladen die hij kan vinden en vult dat in.
Als VBA voor jou echt een raadsel is moet je even een gil geven, dan wil ik wel even een voorzetje brabbelen.

[Beamer]

onkl schreef op 02 October 2003 @ 14:19:
Die jokertekens kom je waarschijnlijk wel uit (werkt ongeveer als jokers in windows explorer zeg maar.
Maar, in het algemeen:
Dit wordt een chaos lijkt me. Iedere keer als je een tabblad, bestand etc hernoemd zal je totalenblad 'stuk' zijn.
Ik zou het persoonlijk als volgt aanpakken:
Zet alle bestanden met gegevens per station in een aparte map.
Schrijf een (kort) vba macrootje met ongeveer de volgende doorloop in je totalenblad:
-verwijder huidige gegevens
-maak mbv. de findfile methode een collectie van alle excel bestanden in die aparte map
-loop door die collectie en open het bestand
-loop door alle tabbladen in dat bestand
-neem de naam van het tabblad en zet die in je totalenblad neer
-pak alle gegevens die je wilt hebben(celreferenties op dat werkblad of gebruik
names) en plak die op dezelfde regel in het totalenblad
-volgende tabblad
-volgende bestand.
-evt. kan je je totalenblad nog sorteren, er een totaal onder laten zetten etc.
Voordeel is dat het niet meer uitmaakt hoe je stations heten etc., je macro pakt nu gewoon alle tabbladen die hij kan vinden en vult dat in.
Als VBA voor jou echt een raadsel is moet je even een gil geven, dan wil ik wel even een voorzetje brabbelen.

GIL!!!!

Dit is echt toevallig ik ben pas begonnen met VBA hier op het werk cursusje gehad maar het wil nog niet echt vlotten. Ik sla gewoon helemaal dicht zodra ik VB open, krijg echt niets uit mezelf, denk dat dat mijn gebied misschien nooit zal worden . Daar zullen ze denk hier alleen geen booschap aan hebben

p.s. zoek:1(?)testen
vervang: 2(?)test
zo ongeveer?

[ Voor 4% gewijzigd door Beamer op 02-10-2003 14:27 ]

[Rataplan]

Beamer schreef op 02 October 2003 @ 14:00:
[...]


Ziet er nuttig uit. Kan inderdaad zijn dat er soms andere tekst tussen zit, kan je hier een voorbeeld van geven wat je dan precies moet inslaan?
Thankx alvast

Ziet er heel nuttig uit, maar werkt volgens mij niet Het zoek-en-vervang-mechanisme in Excel ondersteund het ongewijzigd laten van wildcarded tekst afaik *niet*. Het makkelijkst is nog denk ik zelf je zoek/vervang-resultaat in een formule te zetten, en het resultaat daarvan over het origineel heen te kopieren. Dan krijg je iets als

code:

=IF(
  AND( 
    NOT(ISERROR(FIND("eerstezoekterm";a1)));
    NOT(ISERROR(FIND("tweedezoekterm";a1)))
  );

  LEFT(a1;FIND("eerstezoekterm";a1)) & 
  "eerstevervangterm" &
  MID(a1;FIND("eerstezoekterm";a1)+LEN("eerstezoekterm") & 
  "tweedevervangterm" &
  RIGHT(a1;LEN(a1)-FIND("tweedezoekterm";a1)-LEN("tweedezoekterm"))
  ;

  a1)

...maar dan op één regel Deze code moet nog getwiekt worden met wat +1 en -1'tjes voor de correcte lengteberekeningen, maar dat mag je zelf doen

Omdat ik het een interessant probleem vond ben ik ook even op zoek gegaan, maar de beste oplossingen die ik kon vinden gebruiken VB-scriptjes met regular expressions. Dit is ongetwijfeld werkend te krijgen, maar - tenzij je deze functie *vaak* nodig hebt - is het volgens mij makkelijker om een workaround te verzinnen, zoals het voorbeeld hierboven, of de desbetreffende lijst even in ascii te exporteren en dan met een programma zoek/vervangen dat de gewenste truc wel beheerst, zoals UltraEdit.

In dat programma kan je je wildcard bracketen ( * wordt dan ^(*^) ) en in het vervang-door-veld met ^1, ^2, ... maximaal negen van die bracketed expressions in je vervang-tekst zetten (wel even 'regular expressions' aanvinken).

edit:
Als ik jou was zou ik op het aanbod van onkl ingaan, is voor jou relevanter Maar de vraag uit de topicstart heb ik iig beantwoord *zucht*

[ Voor 34% gewijzigd door Rataplan op 02-10-2003 15:01 . Reden: find-parametrisatie gefixed ]

[onkl]

Beamer schreef op 02 October 2003 @ 14:25:
GIL!!!!

Dit is echt toevallig ik ben pas begonnen met VBA hier op het werk cursusje gehad maar het wil nog niet echt vlotten. Ik sla gewoon helemaal dicht zodra ik VB open, krijg echt niets uit mezelf, denk dat dat mijn gebied misschien nooit zal worden . Daar zullen ze denk hier alleen geen booschap aan hebben

p.s. zoek:1(?)testen
vervang: 2(?)test
zo ongeveer?

Ontspan, bij ieder begin hoort een beetje chaos. Iets equivalents was mijn eerste VBA projectje. Zo onderhand ben ik alweer aardig wegwijs in VB, dus voor een hobbyprogrammeur niet verkeerd. Was voor mij veel prutsen en vragen, maar iedere minuut waard, ezels stoten hun kop geen twee keer omdat ze niet snappen dat pas na drie keer je kop stoten alles makkelijker wordt
heb een klein voorbeeldje,
is wel leuk aan te passen. Als je wilt mail ik wel even wat door.
BTW, 't hoeft niet, waarschijnlijk kom je er met wat gepruts via de zoek vervang methode ook wel uit, maar 't is misschien leuk om het een via VBA te proberen.

[ Voor 6% gewijzigd door onkl op 02-10-2003 15:59 ]

[Beamer]

onkl schreef op 02 October 2003 @ 15:39:
[...]


Ontspan, bij ......leuk om het een via VBA te proberen.

Sta altijd open voor een interessante mailing . Je kan me een e-mail sturen op [e-mail] alvast bedankt. Maar zal idd morgen als die controller er is nog ff wat proberen, ik hou jullie op de hoogte.

[onkl]

Beamer schreef op 02 October 2003 @ 16:09:
[...]


Sta altijd open voor een interessante mailing ..

Ik heb dit net doorgemaild, maar voor andere geinteresseerden, dit is de code waarmee je een leuk beginnetje hebt(maar niet zoveel meer dan dat).
Let erop dat'ie op allerlei plekken naar werkbladen refereerd.

code:

Sub haaldata()
Dim totaalwb As Workbook
Set totaalwb = ActiveWorkbook 'dat is dus fileopening.xls
Dim telezenwb As Workbook 'hebben we zo nodig
Dim dataws As Worksheet
Set dataws = totaalwb.Worksheets("data")
 'het tabblad waarin staat welke cel nodig is.
Dim totaalws As Worksheet
Set totaalws = totaalwb.Worksheets("Totalen")
 'het tabblad waarin de totalen terecht komen
Dim folderlocatie As String
 'de plek waar de bestanden die bekeken moeten worden staan
folderlocatie = totaalwb.Path & "\" & dataws.Range("B1").Value & "\" 
'dit is waarschijnlijk de folder waarin je bestanden staan.
'er wordt een tekst gebruikt uit data!B1
folderlocatie = InputBox("In welke folder moet ik zoeken?", "Folder", folderlocatie)
 'hier kan je de locatie nog aanpassen
totaalws.Columns("A:B").ClearContents 
'ruim de vorige totalen op. (moet erin, als er nu 
'minder totalen worden gevonden laat 'ie de oudere er onder staan
Dim rijindex As Integer
 'deze gebruiken we zo om alles op de goede regel te zetten
rijindex = 1 
'de eerste rij waarop zo geschreven gaat worden.
Set fs = Application.FileSearch
Application.ScreenUpdating = False 
'voorkomt een boel raar geknipper
'filesearch is een object waarmee excel bestanden kan zoeken. 
'loop even door de help, leuke voorbeelden.
With fs
    .LookIn = folderlocatie 'in welke folder
    .FileName = "*" 
    'alle bestandsnamen, eventueel kan je er hier een filter op proberen te zetten
    .FileType = msoFileTypeExcelWorkbooks 'alleen excelbestanden
    
    If .Execute(SortBy:=msoSortByFileName, _
            SortOrder:=msoSortOrderAscending) > 0 Then 
            'execute laat hem zoeken en geeft het aantal bestanden als antwoord.
            'vanaf hier bestaat de .foundfiles collectie
        MsgBox "There were " & .FoundFiles.Count & _
            " file(s) found." 'deze kan je eruit slopen als je wilt.
        For i = 1 To .FoundFiles.Count 'loop door alle gevonden bestanden.
            Set telezenwb = Workbooks.Open(.FoundFiles(i))
            'het bestand wordt geopend en telezenwb genoemd.
            For j = 1 To telezenwb.Worksheets.Count 
            'loop door alle tabbladen van telezenwb
                totaalws.Range("A" & rijindex).Value = telezenwb.Worksheets(j).Name 
                 'bij range kan je een string ingeven ("A1" oid.) dus dit kan ook.
                'telezenwb.worksheets is een collectie tabbladen, 
                'met (j) pluk je er eentje uit. Name is de naam van het ding
                totaalws.Range("B" & rijindex).Value = _
                telezenwb.Worksheets(j).Range(dataws.Range("B2").Value) 
                'Hier kijkt'ie op het datatabblad welke cel hij moet ophalen.
                'hard in de code zou bijvoorbeeld zijn:
                'totaalws.Range("B" & rijindex).Value = telezenwb.Worksheets(j).Range("B12").Value
                'en zo kan je er nog een heleboel bijzetten.
                rijindex = rijindex + 1 
                'het volgende resultaat moet op de volgende regel komen
            Next j 'tabblad
            telezenwb.Saved = True 
            ''anders vraagt excel of wijzigingen bewaard moeten worden
            telezenwb.Close 'sluit bestand
        Next i 'bestand
    Else '.execute>0
        MsgBox "There were no files found."
    End If '.execute>0
End With
Set fs = Nothing
Set dataws = Nothing
Set totaalws = Nothing
Set telezenwb = Nothing
Set totaalwb = Nothing
'even alle gecreerde dingen verdwijnen laten.
'dit moet niet maar is in het kader van geheugen weer vrijgeven altijd wel netjes.
Application.ScreenUpdating = True
End Sub

BTW, ja, 't is een beetje prutscode ja, dit is mijn allereersteVBA projectje ooit met wat op het eerste gezicht aanpassinkjes

[ Voor 9% gewijzigd door onkl op 02-10-2003 17:51 . Reden: wist da'k iets vergat ]