excel vba write value to the referenced cell formula location

Hi,
In sheet1, cell A1, I have a formula =Sheet2!C3

In VBA Code I need to be read cell Sheet1!A!, return the referenced location of Sheet2!C3, and write a value to the Sheet2!C3 cell.

it will only ever be a single cell location rather then a range that is referenced
Could someone share some code to achieve this please
supertramp4Asked:
Who is Participating?
 
MacroShadowConnect With a Mentor Commented:
Try this:
Sub Demo()
    Dim arr() As String
    arr = Split(Replace(Sheets("Sheet1").Range("A1").Formula, "=", ""), "!")
    Sheets(arr(0)).Range(arr(1)).Value = "whatever"
End Sub

Open in new window

0
 
Rgonzo1971Commented:
Hi,

pls try

Range(Right(Range("A1").Formula, Len(Range("A1").Formula) - 1)) = 3

Open in new window

shorter
Range(Replace(Range("A1").Formula, "=", "")) = 3

Open in new window

Regards
0
 
supertramp4Author Commented:
Hi
Either solution returns an error  "Method 'Range' of object '_worksheet' failed "

Private Sub CommandButton1_Click()
Range(Right(Range("A1").Formula, Len(Range("A1").Formula) - 1)) = 3
Range(Replace(Range("A1").Formula, "=", "")) = 3
End Sub

Open in new window

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
cErasmusCommented:
Hi
Can you perhaps give me a bit more information and an example of what you need the code to do?
Elmo
0
 
Rgonzo1971Connect With a Mentor Commented:
then try
Private Sub CommandButton1_Click()
Range(Replace(Sheets("Sheet1").Range("A1").Formula, "=", "")) = 3
End Sub

Open in new window

0
 
supertramp4Author Commented:
Hi Rgonzo19711m

Your code is not handling the fact that the referenced cell is on a different sheet
if Sheet1!A1 is =C3, then your code works
if Sheet1!A1 is =Sheet2!C3, then your code does not work, and generates the Method 'Range' of object '_worksheet' error . This is what I need to be able to do.
0
 
supertramp4Author Commented:
Hi Macroshadow / Rgonzo1971

Nearly right. needed to remove the preceding "=" from the returned sheet name as suggested by RGonzo1971

Public Function writereferenced(incell, outvalue)
Dim arr() As String
arr = Split(Sheets("Sheet1").Range(incell).Formula, "!")
Sheets(Replace(arr(0), "=", "")).Range(arr(1)).Value = outvalue
End Function

Have Split points between you
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.