We geven een zoekterm op en willen de kolomletter vinden waarin deze zoekterm zich bevindt. Laten we zoeken naar:
San Cristóbal
Vul die naam in onderstaande code in.
Sub Find_Column_Letter()
Set cell = Cells.Find("San Cristóbal", , xlValues, xlPart, , , False)
If Not cell Is Nothing Then
ColLetter = Split(cell.Address, "$")(1)
MsgBox ColLetter
Else
MsgBox "I cannot find that text on this sheet"
End If
End Sub
De uitkomst is kolom E
Je kunt natuurlijk ook gewoon zoeken met toetscombinatie Ctrl+F en in het zoekvak de naam invullen.
Option Explicit
'I presume you've got a lot of file names. 10, 100 or 1000?
'- Start up command prompt (cmd.exe) and run as administrator.
'- Go to directory where your files are. Let's presume C:\temp
'- Type dir /b
'- Look for the little black icon at the top left
'- Choose Edit | Mark. You can now select your files.
'- Choose Edit | Copy
'- Go to Excel and start up a new workbook and select A1
'- Hit Paste
'Now suppose your file name is something like: Fortitude.S01E01.WEB-DL.XviD-FUM.jpg
'and you want to replace the part "WEB-DL.XviD-FUM" with "IMG_" (without quotes.)
'- Go to B1 and enter formula =SUBSTITUTE(A1;"WEB-DL.XviD-FUM.jpg";"IMG_.jpg") Attention, I use semi colon and not comma because I have Dutch version.
'- Copy down
'- Now you have your correct file names in B1
'- Copy and Paste this code in a new module -> Alt+F11 | Insert | Module
'- Run the code with View | Macros |View macros | RenameFiles | Run
'- it will pause and you have to point to the directory where your files are.
Sub RenameFiles()
Dim xDir As String
Dim xFile As String
Dim xRow As Long
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show = -1 Then
xDir = .SelectedItems(1)
xFile = Dir(xDir & Application.PathSeparator & "*")
Do Until xFile = ""
xRow = 0
On Error Resume Next
xRow = Application.Match(xFile, Range("A:A"), 0)
If xRow > 0 Then Name xDir & Application.PathSeparator & xFile As _
xDir & Application.PathSeparator & Cells(xRow, "B").Value
xFile = Dir
Loop
End If
End With
End Sub
In kolom A staan dubbele waarden en in kolom B niet. Bijvoorbeeld in kolom A staan de bedrijfsnamen en in kolom B de adressen. Een bedrijf kan meerdere adressen hebben omdat ze ook nog in een ander land zitten. De bedrijfsnaam kan dus 2 of meerdere keren voorkomen maar het adres niet.
Als de bedrijfsnaam meerdere keren voorkomt in kolom A willen we de hele rij markeren. Zoiets:
Option Explicit
Sub Dubbele_Waarden_In_Kolom_A()
'Start rijnummer van je gegevens.
Const lngStartRij As Long = 2
Dim lngLaatsteRij As Long
Dim rngCell As Range
Dim strGegevens As String
lngLaatsteRij = Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
strGegevens = "A" & lngStartRij & ":A" & lngLaatsteRij
For Each rngCell In Range(strGegevens)
If Evaluate("COUNTIF(" & strGegevens & ",A" & rngCell.Row & ")") > 1 Then
'Dubbele waarden in kolom A rood markeren.
Range("A" & rngCell.Row & ":B" & rngCell.Row).Interior.Color = RGB(191, 255, 128)
End If
Next rngCell
End Sub
Bovenstaande code als volgt invoegen:
1. Kopieer de code 2. Open een nieuwe werkmap 3. Druk op de toetscombinatie ALT + F11 om de Visual Basic Editor te openen 4. Druk op de toetscombinatie ALT + N om het menu Invoegen te openen 5. Druk op M om een standaard module in te voegen 6. Daar waar de cursor knippert voeg je de code in middels Ctrl + V 7. Druk op de toetscombinatie ALT + Q om de Editor af te sluiten en terug te keren naar Excel 8. Zorg dat je een lijst met gegevens in je werkblad hebt zoals in de screenshot hierboven. 9. Macro uitvoeren via Beeld | Macro’s
Let op: In de formule worden twee matrices (Array) gebruikt namelijk: {“Verhoeven”\“Bakema”} én {“Hema”\“Aldi”}. Een matrix in een formule wordt ingesloten door accolades { } en de onderdelen worden gescheiden door een backslash.
Let op: In de formule wordt nu een verwijzing gebruikt. Zie het rode gedeelte. In dat gedeelte kun je namen invullen waardoor de formule flexibeler wordt.
In sommige formules kun je een matrix gebruiken. De onderdelen in die matrix moeten van elkaar gescheiden worden. Normaliter gelden in Nederland de backslash \ en de puntkomma ; als scheidingsteken.
{1;2} stelt een matrix voor met 1 kolom en 2 rijen (1 en 2 onderelkaar)
{1\2} stelt een matrix voor met 1 rij en 2 kolommen ( 1 en 2 naastelkaar)
Kortom de \ is de kolom separator en de ; is de rij separator.
Wil je kijken wat er op jouw computer is ingesteld draai dan onderstaande macro.
Met Excel kun je een tekstbestand lezen en het resultaat in het werkblad zetten. Dit doe je door VBA te gebruiken. Visual Basic for Applications (VBA) is een eenvoudige programmeertaal. Je hoeft geen verstand te hebben van die programmeertaal. Gewoon onderstaand verhaal opvolgen. Het zijn 3 uiterst simpele voorbeelden.
Code als volgt invoegen:
1. Kopieer de code 2. Open een nieuwe werkmap 3. Druk op de toetscombinatie ALT + F11 om de Visual Basic Editor te openen 4. Druk op de toetscombinatie ALT + N om het menu Invoegen te openen 5. Druk op M om een standaard module in te voegen 6. Daar waar de cursor knippert voeg je de code in middels Ctrl + V 7. Plaats nu de cursor in een van de subs en druk op F5. 8. Om het simpel te houden wordt het resultaat afgedrukt in het Venster Direct (Dus niet in het werkblad.) 9. Let op: daar waar staat “c:\temp\JOUWBESTAND.txt” moet je de naam van jouw bestand invullen. De extensie is .txt
Sub Test_1()
Dim FileNum As Integer
Dim DataLine As String
FileNum = FreeFile()
Open "c:\temp\JOUWBESTAND.txt" For Input As #FileNum
While Not EOF(FileNum)
Line Input #FileNum, DataLine ' read in data 1 line at a time
Debug.Print DataLine
' depending on what processing you need to do for each case
Wend
Close #FileNum
End Sub
Sub Test_2()
Dim hf As Integer: hf = FreeFile
Dim lines() As String, i As Long
Open "c:\temp\JOUWBESTAND.txt" For Input As #hf
lines = Split(Input$(LOF(hf), #hf), vbNewLine)
Close #hf
For i = 0 To UBound(lines)
Debug.Print "Line"; i; "="; lines(i)
Next
End Sub
Let op: Het laatste voorbeeld Test_3 vereist een verwijzing naar Microsoft Scripting Runtime. Dat doe je als volgt: Druk op Alt+F11 als je nog niet in de Visual Basic Editor zit. Klik vervolgens op: Extra | Verwijzingen Zoek in de lange lijst naar Microsoft Scripting Runtime en vink aan.
Sub Test_3()
'Let op: Vereist een verwijzing naar Microsoft Scripting Runtime.
'Extra | Verwijzingen -> Microsoft Scripting Runtime zoeken en aanvinken
Dim fso As FileSystemObject: Set fso = New FileSystemObject
Set txtStream = fso.OpenTextFile("c:\temp\JOUWBESTAND.txt", ForReading, False)
Do While Not txtStream.AtEndOfStream
strNextLine = txtStream.ReadLine
Debug.Print strNextLine
Loop
txtStream.Close
End Sub
Gegevens kun je het beste filteren met de ingebouwde filter- en sorteerfuncties van Excel. Te bereiken via: Data | Sort & Filter
Als je niet zoveel gegevens hebt kun je middels een paar formules filteren. Dat biedt soms extra mogelijkheden. Let op: Als je heel veel rijen met gegevens hebt, bijvoorbeeld 10000, kun je beter gebruik maken van de ingebouwde filter- en sorteerfuncties van Excel. Dat is veel sneller.
Om te beginnen selecteer je de hele tabel van B1:F831 en kies in de menubalk voor Format as Table.
Hiermee krijgen alle kolommen de naam die bovenaan staat. Die namen kunnen we gebruiken in de formule. In het bereik H1:H10 zet je de volgende namen.
In J1 en K1 zet je de volgende gegevens. Selecteer medewerker en Medewerker_Aantal. Maar Let op: Selecteer cel J2 en kies voor Gegevens | Gegevens validatie en vul in bij Toestaan: Lijst en bij Bron =$H$2:$H$10 In K2 zet je de formule: =COUNTIF(Medewerker;J2) Je ziet in de formule de naam Medewerker staan. Die naam hebben we eerder gemaakt. J2 is nu een uitklaplijst en je kunt medewerkers kiezen.
Zet nu in het bereik J4:N4 de juiste namen. Nu komt de belangrijkste formule. Selecteer cel J5 en voer de volgende formule in: =IF(ROWS(J$5:J5)<=$K$2;INDEX(INDIRECT(J$4);SMALL(IF(Medewerker=$J$2;ROW(Medewerker)-ROW($C$2)+1);ROWS(J$5:J5)));””)
Invoeren met de toetscombinatie Ctrl+Shift+Enter NIET alleen Enter. Vervolgens formule naar rechts kopieren tot N en dan naar beneden (zo’n 250 rijen, dat volstaat voor dit voorbeeld). Telkens als je nu een andere naam kiest in J2 krijg je andere gegevens te zien.
In bereik A2:A11 (oranje screenshot) staan wat getallen die gedeeltelijk overeenkomen met de gegevens in bereik B15:B24 (groene screenshot). Indien er een overeenkomst gevonden wordt willen we de bijpassende naam in het bereik D15:D24 (groene screenshot) laten verschijnen in het bereik B2:B11 (oranje screenshot) .
Formule in D15: =ALS.FOUT(INDEX($B$2:$B$11;KLEINSTE(ALS(ISGETAL(VIND.SPEC($A$2:$A$11;B15));RIJ($A$2:$A$11)-1);1));””)
Invoeren met Ctrl+Shift+Enter en naar beneden doorvoeren.
Voorbeeldje, tijdens het importeren van de gegevens is iets fout gegaan. Nu zit je met de gebakken peren. In kolom A allemaal abracadabra. Kortom: garbage data. Oftewel, verprutste gegevens.
Je wilt alleen de cijfers eruit halen.
Code als volgt invoegen:
1. Kopieer de code 2. Open een nieuwe werkmap 3. Druk op de toetscombinatie ALT + F11 om de Visual Basic Editor te openen 4. Druk op de toetscombinatie ALT + N om het menu Invoegen te openen 5. Druk op M om een standaard module in te voegen 6. Daar waar de cursor knippert voeg je de code in middels Ctrl + V 7. Ga terug naar het Excel werkblad en selecteer cel B2 en type =Cijfers(A2) 8. Formule doorvoeren naar beneden
Function Cijfers(ByVal strVuilnis As String, Optional Extra As Boolean) As String
Dim lngX As Long
For lngX = 1 To Len(strVuilnis)
If Mid(strVuilnis, lngX, 1) Like "[!0-9]" Then Mid(strVuilnis, lngX) = " "
Next
strVuilnis = Application.Trim(strVuilnis)
If Len(Replace(strVuilnis, " ", "")) < 12 Then
If Not Extra Then Cijfers = Replace(strVuilnis, " ", "")
Else
If Extra Then
Cijfers = Mid(strVuilnis, InStrRev(strVuilnis, " ") + 1)
Else
Cijfers = Replace(Left(strVuilnis, InStrRev(strVuilnis, " ")), " ", "")
End If
End If
End Function
Een andere doortrapte methode is:
Function Alleen_Cijfers(strTekst As String) As String
With CreateObject("vbscript.regexp")
.Pattern = "\D"
.Global = True
Alleen_Cijfers = .Replace(strTekst, "")
End With
End Function
