Solved: File name compiling via Userform data help!

[Phelony]

Hi guys :hi:

I'm trying to create a userform which will allow me to pull up archived files without having to sift through the data. I know what I need to do, but it's not working.

This is my first real attempt at a userform so I'm probably just doing something stupid, but a little help would be appreciated.

Private Sub btnreportfetch_Click()
'set archive folder location variables
Dim archfolder As String
archfolder = "Y:\Planning and Performance\Reporting & Management Information\Archive\Regular MI\"

'variables from list boxes and text boxes from userform
'originally set as variables but not needed to be?
'Dim lstreporttype As String - list box
'Dim lstYearSelect As String - list box
'Dim LstMonthSelect As String - list box
'Dim textweek As String - input box
'lookup variables
Dim lookFor As String
Dim rng As Range
Dim col As Integer
Dim found As Variant
'open archiveweek
'triggers from button on userform
If optionweekreport = True Then
Set lookFor = lstreporttype
'range for look up to find proper filename prefix from predetermined lists
'set as C:E to allow copying of successful (if ever) code
Set rng = Sheets("Drop downs").Columns("C:E")
col = 2
On Error Resume Next
filename1 = Application.VLookup(lookFor.Value, rng, col, 0)
If IsError(found) Then
MsgBox lookFor & " not found"
Else: MsgBox ("The address of " & lookFor & " is " & archfolder & lstYearSelect & "\Weekly\" & filename1 & textweek & ".xls")

End If
End If
End Sub

Apologies for the formatting, it doesn't appear to have carried through correctly.

All of the listboxes and the text box have been named correctly, but I can't get the msgbox to appear at all. :help Once I can get the msgbox up so that the code is working upto that point, I can amend so it performs the retrieval, but I'm really really stuck as I've tampered with everything now to get i functional.

I hijacked the Vlookup code from somewhere else and it did originally work until I started to make the output more complex. The full code is much larger as it contains IF's for all of the Userform buttons, but they aren't conflicting. This one however, just doesn't function despite it being directed correctly and as far as I can see, correctly coded.

Where am I going wrong? :banghead:

Any guidance would be appreciated.

Phel

Edit - the problem I'm experiencing (thought that might help!) is that it's just stalling, it's not returning an error message and it's not returning the msgbox.

[Kenneth Hobs]

If you use Option Explicit as the first line, it will help. Found is not defined so I am not sure what you mean.

When troubleshooting code like that, comment out your On Error lines.

I would use Err.Number to find that kind of problem. In this example, run it to show that it found it as it is not case sensitive. Then change A1 to "Hobs" and it should show not found.
Sub t()
Dim filename1 As String
Range("A1").Value2 = "ken"

On Error Resume Next
filename1 = Application.VLookup("Ken", Range("A1:B10"), 1, 0)
If Err.Number = 13 Then
MsgBox "Ken not found."
Else: MsgBox "Ken found."
End If
On Error GoTo 0
End Sub

Of course you could use the Find method contained in the Range object.

[Phelony]

I don't think I followed your advice explicitly (no pun intended) but it did put me on the right track and I've got it to work! Thank you so much.
:beerchug:

Phel

If optionmonth = True Then

Else
End If
'lookup variables
'Dim lookFor As String
Dim rng As Range
Dim col As Integer
Dim found As Variant
Dim filename1 As String
'open archive month
If optionmonthreport = True Then
Else

End If

'open archiveweek
If optionweekreport = True Then
'lookFor = lstreporttype
Set rng = Sheets("Drop downs").Columns("C:E")
col = 2
On Error Resume Next
filename1 = Application.VLookup(lstreporttype.Value, rng, col, 0)
If IsError(lstreporttype) Then
MsgBox lstreporttype & " not found"
Else: MsgBox ("The address of " & lstreporttype & " is " & archfolder & lstYearSelect & "\Weekly\" & filename1 & textweek & ".xls")

End If
End If