Sheetname 'problem' and another Index-match problem

Blackov

New Member
Joined
Jun 7, 2023
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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:
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!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
So, I've been playing with stuff and I found something:
VBA Code:
'This works:

Range("I7").Value = WorksheetFunction.Index(ws.ListObjects("TF_tabel").ListColumns(2).DataBodyRange, _
    Application.WorksheetFunction.Match(fiche, ws.ListObjects("TF_tabel").ListColumns(4).DataBodyRange, False))

'This also works, if I "cheat" and place the name and revision (eg: TF0301) in the first column:

Range("I7").Value = WorksheetFunction.Index(ws.ListObjects("TF_tabel").ListColumns(2).DataBodyRange, _
    Application.WorksheetFunction.Match(fiche & revisie, ws.ListObjects("TF_tabel").ListColumns(4).DataBodyRange , False))

However, this doesn't work. It gives me a type mismatch:
Range("I7").Value = WorksheetFunction.Index(ws.ListObjects("TF_tabel").ListColumns(2).DataBodyRange, _
    Application.WorksheetFunction.Match(fiche & revisie, ws.ListObjects("TF_tabel").ListColumns(4).DataBodyRange & ws.ListObjects("TF_tabel").ListColumns(4).DataBodyRange, False))

How can I fix this?

Another thing I have noticed, is something really weird...
This piece of code:
VBA Code:
fiche = Left(ActiveSheet.Name, 4)
revisie = Mid(ActiveSheet.Name, InStr(ActiveSheet.Name, "_") + 1, 2)

for some reason calls a Private_sub on the sheet "TF overzicht".
This is the sub:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range) 
    If Target.Address = "$M$4" Then
        Application.ScreenUpdating = False
        Call Aanpassen
        Range("A10").Select ' Select cell A10.
        Application.ScreenUpdating = True
    End If
End Sub

It's a sub for checking if cell M4 on "TF overzicht" is updated.
So for some reason that sub is called and on the sheet "TF overzicht", AH1 is filled with "TF o" and AH3 is filled with "TF"
I don't understand why... I can understand where "TF" comes from, but the "o" is a mystery to me.
Can anyone explain?

Thanks!
 
Upvote 0
Found the solution. Thanks goes to this forum, for these many replies and possible solutions. Never have I seen such a devoted community!
 
Upvote 0

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top