Find value from active sheet and copy to another sheet

[simke]

Dear sirs,

How can I use VBA to find first value in activesheet in column R. Value to search is written in sheet "Parameters" cell J5. Once it find first searched value, it gives value to left in column "O" and write this value to sheet "Parameters" to cell J6.

In this example(shown picture) it search value PN23-6 and it gives value 2000 to sheet "Parameters" in cell J6

Thank you for your help

 

[Micron]

If everything is to be dynamic, you have to explain where the value to put in O comes from or how it is determined to be 2000. Otherwise the solution you get would only put 2000 in column O which might be what you want?

 

[simke]

If everything is to be dynamic, you have to explain where the value to put in O comes from or how it is determined to be 2000. Otherwise the solution you get would only put 2000 in column O which might be what you want?

Sory if I wasn't clear.

Tha value in column O is static and it is already in the table. Idea is that makro search in active sheet first finded value PN23-6 and return value in the column O which is at the same row where is PN23-6.

 

[Micron]

Maybe

Sub simke()
Dim rng As range
Dim varValue As Variant

varValue = Sheets("Parameters").range("J5")
Set rng = ActiveSheet.rng.Find(What:=varValue, LookIn:=xlFormulas, MatchCase:=False, SearchFormat:=False)
If Not rng Is Nothing Then
   rng.Offset(0, -3) = 2000
End If

Set rng = Nothing
End Sub

EDIT - untested and probably not quite right. Search needs to be restricted to column R?

 

[Micron]

For looking in R of active sheet

Sub simke()
Dim rng As range
Dim varValue As Variant

varValue = Sheets("Parameters").range("J5")
Set rng = ActiveSheet.range("R:R").Find(What:=varValue, LookIn:=xlFormulas, MatchCase:=False, SearchFormat:=False)
If Not rng Is Nothing Then
   rng.Offset(0, -3) = 2000
End If

End Sub

 

[simke]

For looking in R of active sheet

Sub simke()
Dim rng As range
Dim varValue As Variant

varValue = Sheets("Parameters").range("J5")
Set rng = ActiveSheet.range("R:R").Find(What:=varValue, LookIn:=xlFormulas, MatchCase:=False, SearchFormat:=False)
If Not rng Is Nothing Then
   rng.Offset(0, -3) = 2000
End If

End Sub

Hi Micron,

This is it what I need.

Thanks for your help