first, I'm sorry for another index-match question, I have noticed a lot of these topic here. But I can't find one that helps me. Possible that I just suck a searching.
But I've learned a lot from this site.
Working on the Dutch version of 365
Question one:
My excel file consists of a, overview-page "TF overzicht" on which there is a table (TF_tabel) which the user fills in.
Example:
Name - Subject - Revision
TF01 - Subj1 - 00
TF02 - Subj2 - 00
TF03 - Subj3 - 00
TF03 - Subj3b - 01
TF04 - Subj4 - 00
After pressing a button, a worksheets gets copied for each name and renamed with "name_revision".
Afterwards, on the new sheet, the name and revision gets taken out of the sheetname and filled in 2 separate cells (AH1 and AH3).
Next, I need excel to find the "subject" in the table (shown above) based on name and revision, and put it in "I7" on the new sheet.
My first solution was a formula in each cell: "INDEX(TF_tabel[#Alles];VERGELIJKEN(1;(TF_tabel[[#Alles];[Kolom1]]=AH1)*(TF_tabel[[#Alles];[Kolom4]]=AH3);0);2)" (here is the dutch part)
(with AH1 the cell with the name, and AH3 with revision, note: AH3 is formatted as text, because revision numbering goes: 00, 01, 02, ... and excel doesn't like that, column 'revision in the table is also as text)
This works fine... Untill you've got 50 pages, with 6 of those links working together... Then excel gets slow...
So I tried putting it in VBA, so it would only run once per sheet when a button was pressed.
It does not work completely: The code finds the correct name on row x, and finds a match for 'revision 00' on the first line. I tried some variations on the code.
In the code below where is written "HERE" I tried putting +, & and ',' which all give different, but wrong, results.
Also, as long as the revision is '00', it will give , kind of, the correct Subject.
So after long search on google I found out that this code just searches for the first match of each, not the "and" match.
The main part of the code I use for this is:
The "complete" code:
Question two.
As I said in previous question, I copy and rename a sheet. In that code is an "IF loop" which checks if the name has been used or not, so no double sheets.
The loop just checks 'how many sheets' and then "for sheet1 - check name, for sheet2 - check name,...)
This works, however, with each sheet created the loop gets longer. So the loop gets VERY slow with 30+ sheets.
Is there a way to do this differently?
I was thinking creating a list with all the sheetnames to which you check your new name and add the new name afterwards.
Is that possible or is there a better/faster way?
Thanks in advance!
But I've learned a lot from this site.
Working on the Dutch version of 365
Question one:
My excel file consists of a, overview-page "TF overzicht" on which there is a table (TF_tabel) which the user fills in.
Example:
Name - Subject - Revision
TF01 - Subj1 - 00
TF02 - Subj2 - 00
TF03 - Subj3 - 00
TF03 - Subj3b - 01
TF04 - Subj4 - 00
After pressing a button, a worksheets gets copied for each name and renamed with "name_revision".
Afterwards, on the new sheet, the name and revision gets taken out of the sheetname and filled in 2 separate cells (AH1 and AH3).
Next, I need excel to find the "subject" in the table (shown above) based on name and revision, and put it in "I7" on the new sheet.
My first solution was a formula in each cell: "INDEX(TF_tabel[#Alles];VERGELIJKEN(1;(TF_tabel[[#Alles];[Kolom1]]=AH1)*(TF_tabel[[#Alles];[Kolom4]]=AH3);0);2)" (here is the dutch part)
(with AH1 the cell with the name, and AH3 with revision, note: AH3 is formatted as text, because revision numbering goes: 00, 01, 02, ... and excel doesn't like that, column 'revision in the table is also as text)
This works fine... Untill you've got 50 pages, with 6 of those links working together... Then excel gets slow...
So I tried putting it in VBA, so it would only run once per sheet when a button was pressed.
It does not work completely: The code finds the correct name on row x, and finds a match for 'revision 00' on the first line. I tried some variations on the code.
In the code below where is written "HERE" I tried putting +, & and ',' which all give different, but wrong, results.
Also, as long as the revision is '00', it will give , kind of, the correct Subject.
So after long search on google I found out that this code just searches for the first match of each, not the "and" match.
The main part of the code I use for this is:
VBA Code:
Range("I7").Value = Application.Index(ws.ListObjects("TF_tabel").ListColumns(2).DataBodyRange, _
Application.Match(Range("AH1"), ws.ListObjects("TF_tabel").ListColumns(1).DataBodyRange, 0) "HERE", _
Application.Match(Range("AH3"), ws.ListObjects("TF_tabel").ListColumns(4).DataBodyRange, 0) - 1)
The "complete" code:
VBA Code:
Sub FillData()
Dim fiche As String
Dim revisie As String
Dim temp As String
Dim ws As Worksheet
Set ws = Worksheets("TF overzicht")
Dim ws2 As Worksheet
Set ws2 = Worksheets("Werkblad")
On Error GoTo Fout
'I know I can put the name directly in the correct cell, but I was planning on using "fiche" and "revisie" somewhere else in the code, but that didn't happen
fiche = Left(ActiveSheet.Name, 4)
revisie = Mid(ActiveSheet.Name, InStr(ActiveSheet.Name, "_") + 1, 2)
Range("AH1").Value = fiche
Range("AH3").Value = revisie
'onderwerp (dutch for subject)
Range("I7").Value = Application.Index(ws.ListObjects("TF_tabel").ListColumns(2).DataBodyRange, _
Application.Match(Range("AH1"), ws.ListObjects("TF_tabel").ListColumns(1).DataBodyRange, 0), _
Application.Match(Range("AH3"), ws.ListObjects("TF_tabel").ListColumns(4).DataBodyRange, 0) - 1)
'this repeats multiple times for different cells/columns.
End If
Fout:
'some awesome error-handling code will be put here. With fireworks.
End Sub
Question two.
As I said in previous question, I copy and rename a sheet. In that code is an "IF loop" which checks if the name has been used or not, so no double sheets.
The loop just checks 'how many sheets' and then "for sheet1 - check name, for sheet2 - check name,...)
This works, however, with each sheet created the loop gets longer. So the loop gets VERY slow with 30+ sheets.
Is there a way to do this differently?
I was thinking creating a list with all the sheetnames to which you check your new name and add the new name afterwards.
Is that possible or is there a better/faster way?
Thanks in advance!