supertramp4
asked on
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
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
ASKER
Hi
Either solution returns an error "Method 'Range' of object '_worksheet' failed "
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
Hi
Can you perhaps give me a bit more information and an example of what you need the code to do?
Elmo
Can you perhaps give me a bit more information and an example of what you need the code to do?
Elmo
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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").Ran
Sheets(Replace(arr(0), "=", "")).Range(arr(1)).Value = outvalue
End Function
Have Split points between you
Thanks
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").Ran
Sheets(Replace(arr(0), "=", "")).Range(arr(1)).Value = outvalue
End Function
Have Split points between you
Thanks
pls try
Open in new window
shorterOpen in new window
Regards