Runtime error '424' driving me nuts!!

[JoanaMartins]

Please, I so need your help!

The error appears when i do the set w thing!

I am kind of a beginner so can you kindly help me?

Sub Datas()

Dim w As Range
Dim t As Long
Dim i As Long




t = Sheets("Crono").Cells(Rows.Count, "A").End(xlUp).Row




For i = 8 To t


Set w = Sheets("Projetos NOVO").Range("C:C").Find(Sheets("Crono").Range("A" & i).Value).Row


If Sheets("Projetos NOVO").Range("D" & w).Find(Sheets("Crono").Range("C" & i).Value).Row Then


Sheets("Crono").Range("F" & i).Value = Sheets("Projetos NOVO").Range("R" & w).Value
Sheets("Crono").Range("G" & i).Value = Sheets("Projetos NOVO").Range("S" & w).Value


Else


End If


Next i


End Sub

Thank you in advance!

 

[wideboydixon]

Set w = Sheets("Projetos NOVO").Range("C:C").Find(Sheets("Crono").Range("A" & i).Value).Row

w is a Range
You're trying to assign a numeric value to it (.Row)

WBD

 

[steve the fish]

Remove the .Row

 

[JoanaMartins]

But I need the number of the row because I use it below

If I change to this it happens error 91!

Can you please help?

Sub Datas()

Dim w As Long
Dim t As Long
Dim i As Long




t = Sheets("Crono").Cells(Rows.Count, "A").End(xlUp).Row




For i = 8 To t


w = Sheets("Projetos NOVO").Range("C:C").Find(Sheets("Crono").Range("A" & i).Value).Row


If Sheets("Projetos NOVO").Range("D" & w).Find(Sheets("Crono").Range("C" & i).Value).Row Then


Sheets("Crono").Range("F" & i).Value = Sheets("Projetos NOVO").Range("R" & w).Value
Sheets("Crono").Range("G" & i).Value = Sheets("Projetos NOVO").Range("S" & w).Value


Else


End If


Next i


End Sub

 

[wideboydixon]

Perhaps you're not dealing with the situation where the Find() doesn't actually find the text?

WBD

 

[steve the fish]

You need to use the Set with .Find because it produces a range object. Objects require the use of Set. Once your range has been found then use w.Row

 

[JoanaMartins]

I never thought of that but it might actually be true. because i don't want it to find the exact data that is on the cell but that it contains that text.

I thought that the .find() did that. Maybe I am wrong :s

 

[JoanaMartins]

Like this?

Sub Datas()

Dim w As Range
Dim j As Long
Dim t As Long
Dim i As Long




t = Sheets("Crono").Cells(Rows.Count, "A").End(xlUp).Row




For i = 8 To t


Set w = Sheets("Projetos NOVO").Range("C:C").Find(Sheets("Crono").Range("A" & i).Value)


j = w.Row


If Sheets("Projetos NOVO").Range("D" & j).Find(Sheets("Crono").Range("C" & i).Value).Row Then


Sheets("Crono").Range("F" & i).Value = Sheets("Projetos NOVO").Range("R" & j).Value
Sheets("Crono").Range("G" & i).Value = Sheets("Projetos NOVO").Range("S" & j).Value


Else


End If


Next i


End Sub

 

[steve the fish]

Heres an example of .Find with various variables

Set myFind = Sheets(counter).Cells.Find(What:=datatoFind, LookIn:=xlFormulas, LookAt:=xlPart, MatchCase:=False)

 

[steve the fish]

Try like this:

Set w = Sheets("Projetos NOVO").Range("C:C").Find(What:=Sheets("Crono").Range("A" & i).Value, _
    LookIn:=xlFormulas, LookAt:=xlPart, MatchCase:=False)
If Not w Is Nothing Then
    j = w.Row
    MsgBox j
End If