A macro to change the values on a specified cellClosed

Question

Hello, 

I am searching a macro code on excel to set a value on a cell that is found by using such an excel function 

=ADDRESS(MATCH(N7,'Sheet1'!G:G,0),7) 

In this example when the user writes a value in cell N7 of the current sheet and presses the run button, the same value is written to the address returned by this function (for example 'Sheet1'$G$5)

I want to let the user control when to change the value, therefore I want this code to be run only when clicked to a button as I mentioned above.

Thanks in advance!

Kaan

TrowaD · Answer

Hi Kaan,

How about this code:
Sub RunMe()
Dim MyValue As String
Dim cFind As Range

MyValue = Sheets("Master").Range("N7").Value

Sheets("Sheet1").Select
Set cFind = Columns("A:A").Find(MyValue)

If cFind Is Nothing Then
    MsgBox "Value not found."
    Exit Sub
End If

cFind.Offset(0, 6).Value = MyValue

End Sub

Note 1: Since your result is 'Sheet1'$G$5, I assumed your formula should have been A:A instead of G:G.

Note 2: Your destination sheet is called "Sheet1", but you didn't mention the source sheet name. I took the liberty to call it "Master".

Best regards,
Trowa

A macro to change the values on a specified cell

1 reply

Similar discussions

Subscribe To Our Newsletter!