A macro to change the values on a specified cell

Closed
prov - 25 Aug 2015 à 07:19
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Moderator Last seen 27 December 2022 - 27 Aug 2015 à 11:32
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

1 response

TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Moderator Last seen 27 December 2022 555
27 Aug 2015 à 11:32
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