Hello!
I hope someone can help me with this problem I'm having with some code I'm writing. I have a report that shows customer and various details about them, e.g. address, date they became a customer, payment details etc.
The task I have is to extract information contained in B2 and then to move the complete row to a new sheet called Rpt.
I did look at using a normal vlookup to pull this data however because the data in B2 can slightly change on a month by month basis (there is a formatting issues with customer input, for example an extra space or transposed digits) I think the best way would be to use the Find/FindNext.
So, the problem...
1) When the declared variable CustName is set as a String returns an error 'mismatch', however, when changed to a Variant, there is no error reported, but in the Watch window is does show as a 'String' has been applied to it
2) Once the above has been set as a Variant, I then get an error with the If statement 'runtime error 424, Object required. 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
I hope someone can help me with this problem I'm having with some code I'm writing. I have a report that shows customer and various details about them, e.g. address, date they became a customer, payment details etc.
The task I have is to extract information contained in B2 and then to move the complete row to a new sheet called Rpt.
I did look at using a normal vlookup to pull this data however because the data in B2 can slightly change on a month by month basis (there is a formatting issues with customer input, for example an extra space or transposed digits) I think the best way would be to use the Find/FindNext.
So, the problem...
1) When the declared variable CustName is set as a String returns an error 'mismatch', however, when changed to a Variant, there is no error reported, but in the Watch window is does show as a 'String' has been applied to it
2) Once the above has been set as a Variant, I then get an error with the If statement 'runtime error 424, Object required. 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
