Solved

excel vba write value to the referenced cell formula location

Posted on 2016-11-16
7
37 Views
Last Modified: 2016-11-16
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
0
Comment
Question by:supertramp4
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 41889396
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
 

Author Comment

by:supertramp4
ID: 41889404
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
 
LVL 1

Expert Comment

by:cErasmus
ID: 41889432
Hi
Can you perhaps give me a bit more information and an example of what you need the code to do?
Elmo
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 51

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 250 total points
ID: 41889434
then try
Private Sub CommandButton1_Click()
Range(Replace(Sheets("Sheet1").Range("A1").Formula, "=", "")) = 3
End Sub

Open in new window

0
 

Author Comment

by:supertramp4
ID: 41889443
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
 
LVL 27

Accepted Solution

by:
MacroShadow earned 250 total points
ID: 41889444
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
 

Author Closing Comment

by:supertramp4
ID: 41889456
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question