Run Time Error 13 - Type Mismatch Frustration

[Poundland]

Hi Guys,

I have written some code and part of it is generating a Runtime 13 error - Type Mismatch, I am not sure if it is because I am using invalid object variables or just trying to combine some variables that are not meant to be combined in the chosen method.

The portion of Code where the error is generated is below and is generated on the first .Find statement


' finds and sets a range object for each measure
With DataDept.Range("G:G")
Set Rws(1) = .Find(SA, Rw.Address, LookIn:=xlValues)
Set Rws(2) = .Find(SAM, Rw.Address, LookIn:=xlValues)
Set Rws(3) = .Find(DA, Rw.Address, LookIn:=xlValues)
Set Rws(4) = .Find(Re, Rw.Address, LookIn:=xlValues)
Set Rws(5) = .Find(O, Rw.Address, LookIn:=xlValues)
Set Rws(6) = .Find(Om, Rw.Address, LookIn:=xlValues)
End With

The full code is below, any help in solving this with me would be most appreciated as always.


Sub Retrieve()
' Variable building
Dim LostSales As Range, LostDespatch As Range, SalesAdjust As Range, SalesAdjustMarg As Range
Dim LSDestn As Range, LDDestn As Range, SADestn As Range, SAMDestn As Range
Dim DespatchAdjust As Range, Resched As Range, OTB As Range, OTBMarg As Range
Dim DADestn As Range, ReDestn As Range, OTBDestn As Range, OTBMDestn As Range
Dim Database As Workbook, DataDept As Worksheet, DataDeptLost As Worksheet, Lineflow As Worksheet, Monthflow As Worksheet
Dim Msku As Range, LS As String, LD As String, SA As String, SAM As String, DA As String, Re As String, O As String
Dim Om As String, Dte As Range, Department As String, Rw As Range, Col As Range
Dim LSStart As Range, LSEnd As Range, LDStart As Range, LDEnd As Range
' Assigning known object Variables
Set Database = Workbooks("Developments Database.xlsm")
Set Lineflow = ThisWorkbook.Sheets("Lineflow")
Set Monthflow = ThisWorkbook.Sheets("Monthflow")
Set Msku = Lineflow.Cells(5, 6)
Set LSDestn = Monthflow.Range("B26:CG26")
Set LDDestn = Monthflow.Range("B33:CG33")
Set SADestn = Lineflow.Range("G14:BB14")
Set SAMDestn = Lineflow.Range("G15:BB15")
Set DADestn = Lineflow.Range("G31:BB31")
Set ReDestn = Lineflow.Range("G34:BB34")
Set OTBDestn = Lineflow.Range("G38:BB38")
Set OTBMDestn = Lineflow.Range("G39:BB39")
Set Dte = Lineflow.Cells(7, 7)
' Assigning string variables
Department = Lineflow.Cells(5, 9).Value
LS = Monthflow.Cells(26, 1).Value
LD = Monthflow.Cells(33, 1).Value
SA = Lineflow.Cells(14, 1).Value
SAM = Lineflow.Cells(15, 1).Value
DA = Lineflow.Cells(31, 1).Value
Re = Lineflow.Cells(34, 1).Value
O = Lineflow.Cells(38, 1).Value
Om = Lineflow.Cells(39, 1).Value
' Captures colour of cells for formatting post retrieval
ColMonth = LSDestn.Interior.Color
colLine = SADestn.Interior.Color
' Assigns Database worksheet object variables
Select Case Department ' Select Case statement for Events only
Case Department = "Seasonal/Events (480)"
Set DataDept = Database.Sheets("Seasonal (480)")
Set DataDeptLost = Database.Sheets("Seasonal (480) Lost Sales")
Case Else
Set DataDept = Database.Sheets(Department)
Set DataDeptLost = Database.Sheets(Department & " Lost Sales")
End Select
' checks for MSKU in lost sales database
With DataDeptLost.Range("A:A")
Set Rw = .Find(Msku, LookIn:=xlValues)
If Not Rw Is Nothing Then
rowaddress = Rw.Row
coladdress = Rw.Column
Else
MsgBox ("No User Data can be retrieved as the Master Sku does not exist in the Archive")
Exit Sub
End If
End With
' assigns lost sales/despatch range to object variables
Set LSStart = DataDeptLost.Cells(rowaddress, coladdress + 3)
Set LSEnd = DataDeptLost.Cells(rowaddress, coladdress + 87)
Set LDStart = DataDeptLost.Cells(rowaddress + 1, coladdress + 3)
Set LDEnd = DataDeptLost.Cells(rowaddress + 1, coladdress + 87)
Set LostSales = DataDeptLost.Range(LSStart.Address, LSEnd.Address)
Set LostDespatch = DataDeptLost.Range(LDStart.Address, LDEnd.Address)
' copies object variables to Lineflow and reassigns colour
LostSales.Copy LSDestn
LostDespatch.Copy LDDestn
'Re -formats
With LSDestn
.Interior.Color = ColMonth
.Borders.LineStyle = xlContinuous
End With
With LDDestn
.Interior.Color = ColMonth
.Borders.LineStyle = xlContinuous
End With
' finds MSKU in database
With DataDept.Range("E:E")
Set Rw = .Find(Msku, LookIn:=xlValues)
End With
' find each measure and retrieve data
Dim SAStart As Range, SAEnd As Range, SAMStart As Range, SAMEnd As Range, DAStart As Range, DAEnd As Range
Dim ReStart As Range, ReEnd As Range, OtbStart As Range, OtbEnd As Range, OtbMStart As Range, OtbMEnd As Range
Dim Rws(1 To 6) As Range ' Rw1 = Sales Adjust, Rw2 = Sales Adjust Margin, Rw3 = Despatch Adjust, RW4 = Reschedule
' Rw5 = OTB, Rw6 = OTB Margin
Dim DteCol As Range
' finds and sets a range object for each measure
With DataDept.Range("G:G")
Set Rws(1) = .Find(SA, Rw.Address, LookIn:=xlValues)
Set Rws(2) = .Find(SAM, Rw.Address, LookIn:=xlValues)
Set Rws(3) = .Find(DA, Rw.Address, LookIn:=xlValues)
Set Rws(4) = .Find(Re, Rw.Address, LookIn:=xlValues)
Set Rws(5) = .Find(O, Rw.Address, LookIn:=xlValues)
Set Rws(6) = .Find(Om, Rw.Address, LookIn:=xlValues)
End With
' finds date and assigns column number to a string variable
With DataDept.Range("A:A")
Set DteCol = .Find(Dte.Value, LookIn:=xlValues)
DateColumn = DteCol.Column
End With
' sets up the Starting positions in the database
Set SAStart = Cells(Rws(1).Row, DateColumn)
Set SAMStart = Cells(Rws(2).Row, DateColumn)
Set DAStart = Cells(Rws(3).Row, DateColumn)
Set ReStart = Cells(Rws(4).Row, DateColumn)
Set OtbStart = Cells(Rws(5).Row, DateColumn)
Set OtbMStart = Cells(Rws(6).Row, DateColumn)
' sets up the ending position in the database
Set SAEnd = Cells(Rws(1).Row, DateColumn + 43)
Set SAMEnd = Cells(Rws(2).Row, DateColumn + 43)
Set DAEnd = Cells(Rws(3).Row, DateColumn + 43)
Set ReEnd = Cells(Rws(4).Row, DateColumn + 43)
Set OtbEnd = Cells(Rws(5).Row, DateColumn + 43)
Set OtbMEnd = Cells(Rws(6).Row, DateColumn + 43)
' captures data range in database for each measure
Set SalesAdjust = Range(SAStart.Address, SAEnd.Address)
Set SalesAdjustMarg = Range(SAMStart.Address, SAMEnd.Address)
Set DespatchAdjust = Range(DAStart.Address, DAEnd.Address)
Set Resched = Range(ReStart.Address, ReEnd.Address)
Set OTB = Range(OtbStart.Address, OtbEnd.Address)
Set OTBMarg = Range(OtbMStart.Address, OtbMEnd.Address)
' copies retrieved data from database to lineflow
SalesAdjust.Copy SADestn
SalesAdjustMarg.Copy SAMDestn
DespatchAdjust.Copy DADestn
Resched.Copy ReDestn
OTB.Copy OTBDestn
OTBMarg.Copy OTBMDestn
' re-formats colour for each measure in lineflow
With SADestn
.Interior.Color = colLine
End With
With SAMDestn
.Interior.Color = colLine
End With
With DADestn
.Interior.Color = colLine
End With
With ReDestn
.Interior.Color = colLine
End With
With OTBDestn
.Interior.Color = colLine
End With
With OTBMDestn
.Interior.Color = colLine
End With

End Sub

[Aflatoon]

The second argument to Find is a Range object, not an address string.

[Poundland]

The second argument to Find is a Range object, not an address string.

Thank you for your response.

I am trying to define from after which cell address the find function should start looking, I thought by using the .Address that I would be referencing the object address rather than the object itself.

What should I be using?

[Aflatoon]

Remove the .Address

[Poundland]

That did not work, It still generated the same error.

I have structured the Find statement so that the first string is the value to look for and the Rw.address if the cell address of the object to start looking after to find the string.

I need to reference the Rw.Address as there will be multiple instances of the same string in the data range but I am looking for a specific entry that will be the first search result after the Rw.Address.

[Aflatoon]

You cannot use an address string there - it has to be a range OBJECT. You will get an error if rw is not a cell within the range you are searching.

[Poundland]

I removed the .Address from the code lines for each .Find and extended the search range to include the Object and it now works.

Thank you for your help.