Zoeken en vervangen waarden verschillende opmaak

[Figaro75]

Via mijn collega de vraag gekregen of ik iets in excel kan maken om het zoeken en vervangen van waarden te automatiseren...

Case:
1 csv-bestand (origineel, scheidingsteken ; en decimaal ,) en 1 txt-bestand (gemeten, scheidingsteken , en decimaal .)
CSV importeren in excel (tabblad 1), kolommen A, B en C selecteren en aanpassen naar getal met drie decimalen
TXT importeren in excel (tabblad 2) met alle kolommen als tekst, . vervangen door , en kolommen B, C en D omzetten naar getal met drie decimalen

Bij het bovenstaande denk ik dat ik met de recorder een heel eind moet komen....
De indeling van tabblad 1 is geheel niet overeenkomstig met tabblad 2.
Dus kolomindeling is anders en de rijen komen niet met elkaar overeen.

Nu de vraag of de volgende actie met VBA kan worden uitgevoerd met 1 druk op een knop:

Elke waarde in kolom 6 van tabblad 2 zoeken in kolom 4 van tabblad 1
Als waarde gevonden is (bv blad 2: F9 = "n1" gevonden in blad 1: D24 = "N1" (verschil hoofdletters/kleine letters moeten genegeerd worden)
Dan moet blad1: D24 gekleurd worden en moeten de waarden in blad 1: A24, B24 en C24 worden overschreven met de waarden van blad 2: B9, C9 en D9 en hier moet F9 gekleurd worden.
Dit moet de macro voor elke rij in blad 2 doen --> dus zoeken of de waarde in F gevonden wordt en dan de bijbehorende cellen overnemen.

Nu heb ik begrepen dat je met index en vergelijken vrijer bent in het zoeken dan vert.zoeken omdat die alleen in de 1e kolom kan zoeken.
Maar geldt die beperking ook voor VBA of kan je daar veel makkelijker zoeken?

Mijn vraag is of iemand met kennis van VBA kan aangeven of dit kan, zo niet dan geef ik dat aan mijn collega door en zal er met sorteren en handmatig zoeken en vervangen gewerkt moeten gaan worden.

We werken met Excel 2007 en 2010.

Ik hoor graag of ik hier wel of geen tijd in moet steken.
Als dit wel kan wordt ik graag een beetje op weg geholpen want ik zou niet weten waar ik moet beginnen....

Voorbeeld bestand
Bekijk bijlage Import1.xlsx

 

[namliam]

Zoeken met vba is een "ramp", ik zou daar zeker de standaard excel functie voor gebruiken

Maar klinkt zeker allemaal doenbaar....

 

[Figaro75]

Maar ik wil dus eigenlijk helemaal niets met sommen doen.
Dus enkel waarden vervangen en dan weer exporteren naar csv.
Zonder VBA lijkt mij dit dan weer onmogelijk of heb ik dat verkeerd?

 

[namliam]

Elke waarde in kolom 6 van tabblad 2 zoeken in kolom 4 van tabblad 1
Als waarde gevonden is (bv blad 2: F9 = "n1" gevonden in blad 1: D24 = "N1" (verschil hoofdletters/kleine letters moeten genegeerd worden)
Dan moet blad1: D24 gekleurd worden en moeten de waarden in blad 1: A24, B24 en C24 worden overschreven met de waarden van blad 2: B9, C9 en D9 en hier moet F9 gekleurd worden.
Dit moet de macro voor elke rij in blad 2 doen --> dus zoeken of de waarde in F gevonden wordt en dan de bijbehorende cellen overnemen.

waarom de waarden overschrijven, het is in excel "veel" simpeler om in dit geval een derde tab blad te maken en afhankelijk van de al dan niet gevonden waarden de verschillende informatie over te nemen op het derde blad.

Alleen het kleuren kan een uitdaging worden, maar daar kan je misschien wel wat mee met Conditional formatting, TOTAAL ZONDER VBA kan je dit doen met formules en een derde blad (en wat conditional formatting)
Standaard neem je Kolom D over op blad 3 van blad 1 met =Blad1!D1 etc...
In kolom E van Blad3 doe je een "Vergelijken" functie om te kijken of de waarde ook in blad twee te vinden is
In kolom A, B, C doe je dan IF(Iserror(E1), Blad1 informatie, Blad2 informatie)
Dan wat conditional formatting om de cellen te kleuren en je bent klaar zonder ook maar VBA aan te raken...

Het enige dat je hoeft te doen is het importeren van de bestanden.

Afgezien van de kleuren valt dit ook goed te doen met Access en een simpele query als je je daar goed bij voelt

 

[VenA]

Zoeken met vba is een "ramp"

Verklaar u eens nader.

@Figaro75,
Helemaal begrijpen doe ik het niet. Maar wat ik er van maak is dat de x,y en z waarden in blad2 iets nauwkeuriger zijn dan deze waarden in blad1. Dmv INDEX() en VERGELIJKEN() heb ik de waarden in blad2 opgezocht. Komt dit een beetje in de richting?

 

[cow18]

nou, nou, er zijn moeilijker dingen

Sub Kopieren()
  Dim data2, i As Integer, j As Integer, data1
  data2 = Sheets("blad2").Range("A1").CurrentRegion.Resize(, 1).Offset(, 5)  'array met de gegevens van kolom F van blad2
  data1 = Sheets("blad1").Range("A1").CurrentRegion.Resize(, 1).Offset(, 3)  'array met de gegevens van kolom D  van blad1
  For i = 2 To UBound(data2)                               'vanaf de 2e rij in een loopje
    On Error Resume Next
    j = 0: j = WorksheetFunction.Match(data2(i, 1), data1, 0)  'zoek waarde in die array van blad1
    On Error GoTo 0
    If j <> 0 Then                                         'gevonden
      Sheets("blad2").Range("F" & i).Interior.ColorIndex = 4  'kleur F-cel in blad2
      Sheets("blad1").Range("A" & j).Resize(, 3).Value = Sheets("blad2").Range("A" & i).Resize(, 3).Value  'overnemen coordinaten in blad1
      Sheets("blad1").Range("D" & j).Interior.ColorIndex = 4  'D-cel kleuren
    End If
  Next
End Sub

 

[Figaro75]

@VenA

De waarden in het eerste blad komen uit een csv bestand welke weer uit een database programma komt.
De gegevens uit het txt bestand komen uit een daadwerkelijke meting.
De coördinaat gegevens in de database moeten worden geupdate maar daar ligt de kolomdefinitie dus vast.

Dus moet op basis van de meting in het csv-bstand de juiste waarde worden gevonden waarna de coördinaten worden overschreden.
Daarna wordt het weer als csv-bestand weggeschreven en dan mogen er geen nieuwe kolommen in staan want dan wordt het bestand door de database niet geaccepteerd.
De kleurtjes is voor een visuele check om te kijken wat er is aangepast.

Dus de index formule kan i.d.d. vinden wat ik wil maar het gaat mij dus juist om het overschrijven van de waarde in de juiste kolom. Mijn dank hiervoor.

@cow18
excel is voor mij geen dagelijkse praktijk en zelfs dan vaak alleen de normale sommen.... Ik probeer op verzoek van collega's wel dingen te automatiseren maar dan vaak op basis van voorbeelden die ik bv hier vind. Dus met veel uitproberen en vallen en opstaan. Maar met vba mis ik zelfs de basiskennis al wordt het wel langzaam beter.
Maar zo'n 'simpel ding' kon ik niet vinden hier. Ik ga ermee aan de gang, alvast bedankt.

 

[Figaro75]

@cow18

Ik heb de code op 2 puntjes moeten veranderen maar hij werkt dan perfect :thumb:
Bij i = 2 sloeg hij in blad 2 de eerste regel over en bij Range("A" & i) plakte hij meteen vanaf kolom A blad 2 terwijl hij vanaf B moest overnemen.

Sub Kopieren()
  Dim data2, i As Integer, j As Integer, data1
  data2 = Sheets("blad2").Range("A1").CurrentRegion.Resize(, 1).Offset(, 5)  'array met de gegevens van kolom F van blad2
  data1 = Sheets("blad1").Range("A1").CurrentRegion.Resize(, 1).Offset(, 3)  'array met de gegevens van kolom D  van blad1
  For i = [COLOR="#FF0000"]1[/COLOR] To UBound(data2)                               'vanaf de 1e rij in een loopje
    On Error Resume Next
    j = 0: j = WorksheetFunction.Match(data2(i, 1), data1, 0)  'zoek waarde in die array van blad1
    On Error GoTo 0
    If j <> 0 Then                                         'gevonden
      Sheets("blad2").Range("F" & i).Interior.ColorIndex = 4  'kleur F-cel in blad2
      Sheets("blad1").Range("A" & j).Resize(, 3).Value = Sheets("blad2").Range("[COLOR="#FF0000"]B[/COLOR]" & i).Resize(, 3).Value  'overnemen coordinaten in blad1
      Sheets("blad1").Range("D" & j).Interior.ColorIndex = 4  'D-cel kleuren
    End If
  Next
End Sub

Wat voor jou heel makkelijk was heeft mij ontzettend veel zoek werk bespaard.

Nu even een standaard document maken welke de bestanden importeert, vergelijkt en vervangt en weer exporteert.

Iedereen bedankt voor het meedenken en helpen. Ook de oplossing van VenA komt nog wel eens van toepassing.

 

[namliam]

Verklaar u eens nader.

Ik doel hiermee met name op de performance, Vergelijk en Vert.zoeken is zeker met grotere data sets aanzienlijk sneller.

Je kan natuurlijk die functies ook "misbruiken" in VBA met worksheetfunction...

 

[cow18]

@namliam,
iedere vogel zingt zoals die gebekt is, dus de ene is vlotter met VBA dan de andere, maar of VBA gelijk staat met "misbruiken" is toch wel een bocht te ver !
Kwa performance, in 1 keer alles veranderen en alleen met vaste waarden overblijven zodat je daarna niets meer hoeft te herrekenen of een werkblad vol formules, ik wil het nog even zien.

@Figaro, had die foutjes uit het oog verloren.