Solved

excel vba write value to the referenced cell formula location

Posted on 2016-11-16
7
29 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 50

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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 50

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

830 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