Previous Topic Next Topic

A macro to change the values on a specified cell

Closed
prov - Aug 25, 2015 at 07:19 AM
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
 - Aug 27, 2015 at 11:32 AM
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 reply

Answer 1 / 1
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
 515
Aug 27, 2015 at 11:32 AM
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
0