Runtime error '424' driving me nuts!!

JoanaMartins

New Member
Joined
Jul 14, 2016
Messages
29
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?

Code:
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

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
Code:
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
 

JoanaMartins

New Member
Joined
Jul 14, 2016
Messages
29
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?

Code:
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

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
Perhaps you're not dealing with the situation where the Find() doesn't actually find the text?

WBD
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,778
Office Version
365
Platform
Windows
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

New Member
Joined
Jul 14, 2016
Messages
29
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

New Member
Joined
Jul 14, 2016
Messages
29
Like this?

Code:
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

Well-known Member
Joined
Oct 20, 2009
Messages
7,778
Office Version
365
Platform
Windows
Heres an example of .Find with various variables

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

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,778
Office Version
365
Platform
Windows
Try like this:

Code:
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
 

Forum statistics

Threads
1,081,708
Messages
5,360,781
Members
400,595
Latest member
T_Dubs

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top