Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

excel vba write value to the referenced cell formula location

Posted on 2016-11-16
7
Medium Priority
?
51 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
7 Comments
 
LVL 53

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 53

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 1000 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 28

Accepted Solution

by:
MacroShadow earned 1000 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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

916 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