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!

 

[steve the fish]

Oh and your Dim statement needs to change to Range not Long

 

[JoanaMartins]

Error 91 still

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(What:=Sheets("Crono").Range("A" & i).Value, _
    LookIn:=xlFormulas, LookAt:=xlPart, MatchCase:=False)


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]

Whats this line??

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

 

[wideboydixon]

Still not 100% clear what you're trying to do since this line is very odd:

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

I tried to re-write your code (ignoring that line):

Sub Datas()

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

Dim cs As Worksheet
Dim ps As Worksheet

Set cs = Sheets("Crono")
Set ps = Sheets("Projetos NOVO")

t = cs.Cells(cs.Rows.Count, 1).End(xlUp).Row

For i = 8 To t
    Set w = ps.Range("C:C").Find(cs.Cells(i, 1).Value)
    If Not (w Is Nothing) Then
        cs.Cells(i, 6).Value = w.Offset(0, 15).Value
        cs.Cells(i, 7).Value = w.Offset(0, 16).Value
    End If
Next i

End Sub

WBD

 

[JoanaMartins]

IT WORKED!!!!

woow. I can't even believe it! thank you so much.
I think I have a lot to learn

 

[JoanaMartins]

It didn't work after all.. because I have 2 criteria. I want it to find the value of cells(i,1) and find out if, on that same row the cell from column J contains the text that is on another cell.

there is 2 kind of criteria..

 

[JoanaMartins]

That's why I had this line:

[COLOR=#333333]If Sheets("Projetos NOVO").Range("D" & j).Find(Sheets("Crono").Range("C" & i).Value).Row Then[/COLOR]

I really cannot think how I can solve this problem :\

 

[wideboydixon]

For each value in column A on the sheet "Crono", look in column C on sheet "Projetos NOVO" for that value. If you find that value in column C and, on that row, column D contains the same value then copr columns R and S from that row to columns F and G on sheet "Crono":

Sub Datas()

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

Dim cs As Worksheet
Dim ps As Worksheet

Set cs = Sheets("Crono")
Set ps = Sheets("Projetos NOVO")

t = cs.Cells(cs.Rows.Count, 1).End(xlUp).Row

For i = 8 To t
    Set w = ps.Range("C:C").Find(cs.Cells(i, 1).Value)
    If Not (w Is Nothing) Then
        If w.Offset(0, 1).Value = cs.Cells(i, 1).Value Then
            cs.Cells(i, 6).Value = w.Offset(0, 15).Value
            cs.Cells(i, 7).Value = w.Offset(0, 16).Value
        End If
    End If
Next i

End Sub

WBD

 

[JoanaMartins]

Almost there!

What if it is not exactly like the second cell but it contains the text from that cell.

If I was looking for a specific text I would put

... like "*apple*"

But in this case I do not know what the text is.

Do you know how I can write that?

Is

[COLOR=#333333]If w.Offset(0, 1).Value like "*cs.Cells(i, 1).Value*" Then[/COLOR]

correct?

 

[steve the fish]

Try instr:

If InStr(Ucase(w.Offset(0, 1).Value), Ucase(cs.Cells(i, 1).Value)) > 0 Then

I think those are the right way around