I am trying to create a userform where I can edit a project-entry in a large database.
The idea is that when you click on the button "Edit Project" in excel, it opens a userform. This userform opens by presenting a combo_box, where you can choose the project-entry that you want to look at/ edit. When you choose the project in question and click CONTINUE in the userform, excel should locate where this project's number is in the database (I have given this button a macro).
To create the code to do this, I have used the following code:
Private Sub CommandButton_Cont_1_Click()
Dim TargetRow As Integer
TargetRow = Application.WorksheetFunction.Match(Combo_Project, Sheets("DATA").Range("Dyn_Project_Number"), 0)
MsgBox TargetRow
End Sub(OBS: Combo_Project refers to the combo_box in the userform, Sheets refers to the sheet where the data is and Range is a range i created in Name Manager (translated applied code: offset(DATA!$A$8,0,0,Engine!$B$3,1)) - OBS Engine refers to a dynamic code that counts how many projects I have, the code is: CountA(DATA!A7:A10000)-1
No matter what I do, I get the following error message when I try to run the code: Error runtime 1004 - Unable to get Match property of the WorksheetFunction class
I have tried to use
TargetRow = Application.Match(Combo_Project, Sheets("DATA").Range("Dyn_Project_Number"), 0)But this did not work either.
I have also tried to use VLookup instead of Match - but also here I received the same error message!
Finally, I thought that the error may be due to changes in language. The VBA coding is in English, but the settings for my excel is not. However, coding in English thus far in the VBA has worked just fine and if I switch the coding language to the standard language setting for my excel - the coding does not work anymore... so in conclusion, coding in English in the VBA works....
As mentioned above, the range Dyn_Project_Number is not coded in English, as this was done under Name Manager under the excel toolbar. However, this coding still worked in the userform, as I referred to this range as the RowSource for the Combo_Box, Combo_Project - and this worked perfectly... so I don't understand why this would not work in the VBA code?
So, I feel that I have tried everything and I would really appreciate any help that I can get so that the first piece of code above works.
7 Reset to default
