Public Range

[Eziooh]

Hello, I think it is a noob question but...

How can I range a cell with a variable to a module?
My code right now is pretty simple, this is on the actual sheet:

Private Sub Worksheet_Change(ByVal Target As Range)    If Target.Address = "$D$7" Then
        Call Procurar
    End If
End Sub

And in the module code I want to use a variable which ranges the target address...
Is it possible? If it is, how?
Thanks for the time!

 

[Fluff]

In that case

valor = rng.Value ' This gives the value of the cell, assuming that only one cell was changed
valor = rng.Address ' This gives the address

 

[Eziooh]

Thank you, now I am trying to get this to work, when I am done or with any question, I will post here, seems okay to you?

 

[Fluff]

You're welcome & thanks for the feedback

 

[Eziooh]

It's me again, I think I have something wrong, the macro doesn't run...


(Module)

Sub Procurar(rng As Range)

Dim lastrow As Long
Dim Cell, cRange, a As Range
Dim valor, resultado As String
valor = rng.Value
lastrow = Sheets("Finalsheet").Cells(Rows.Count, 4).End(xlUp).Offset(1).Row
a = rng.Address
' Range to search
    Set cRange = Worksheets("Sheet1").Range("A1:B1000")
' for each cell in range
        For Each Cell In cRange
' if cell has the value
            If Cell.Value = valor Then
' then the valor appears
              resultado = Cell.Offset(0, -1).Value
            
            Select Case a
            Case a = Worksheets("sheet2").Range("D7") : Sheets("Finalsheet").Cells(lastrow, 4) = resultado
            End Select
End If
' Check next cell in range
        Next Cell


End Sub

(In sheet2, I have a drop-down which I need...)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "D7" Then
        Call Procurar(Target)
    End If
End Sub

 

[Fluff]

What are you trying to do on this line?

Case a = Worksheets("sheet2").Range("D7") = Sheets("Finalsheet").Cells(lastrow, 4) = resultado

 

[Eziooh]

What are you trying to do on this line?

Case a = Worksheets("sheet2").Range("D7") = Sheets("Finalsheet").Cells(lastrow, 4) = resultado

Trying to paste the "resultado"
I don't know if it works as it is, I am new to vba...

 

[Eziooh]

I could get myself up and done it, thank you for help me!

 

[Fluff]

Glad you sorted it & thanks for the feedback