WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 525 reviews for our classroom and online training

Details for Phil K

Phil K has participated in the following threads:

Added by Phil K on 13 Jun 2020 at 15:10

Hello Andrew

Fistly, thank you for making these videos free to view. As many have said previously you really do make things clear and asy to follow. However I'm having some difficulty with a sub I'm trying to write.

The issue is with the if statement, please see below

Sub search()
Dim CustSearch As Range
Dim SearchRange As Range
Dim CustName As Variant
Dim wb As Workbook
Dim wsRpt As Worksheet
Set wb = ThisWorkbook
Set wsRpt = ThisWorkbook.Worksheets("Rpt")
CustName = InputBox("Type customer name")
Set SearchRange = Range("B6", Range("B5").End(xlDown))
Set CustSearch = SearchRange.Find(What:=CustName, MatchCase:=False, LookAt:=xlPart)
If CustName Is Nothing Then
    MsgBox "No Customer found"
Else
    wsRpt.Cells(1, "CustName").Value
End If
End Sub

When I step through I get an 424 object error message at the IF statement, I tried changing 'Dim CustName As Variant' to a string, but had another error message 'Type mis-match' and the only way I can find to stop this is to change it back to a Variant.

Once the Search is completed, and if found, then to populate the value to the Rpt sheet

Could I ask if you could point me in the right dirrection as to where I've gone wrong with this coding?

Thank you for reading this.

Added by Phil K on 02 Aug 2020 at 21:17

Thank you and to Wiseowl for these fantastic videos, they truly help with getting good quality instruction on how to write good, rounded routines. I highly recommend these.  I have a question on the FSO. I've taken your tutorial and can get everything to work exactly as described. Brilliant!.

I would like to expand my knowledge about this topic but have hit a stumbling block. I've moved this on to start moving files with fso.filemove from one folder to another and while it is fine if I hard write the file name into the sub but if I use a variable to name the saved file I get an error.

My question is, can this be accomplished using a variable to name the new workbook?