Solved

excel vba write value to the referenced cell formula location

Posted on 2016-11-16
7
40 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 52

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 52

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
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 demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

624 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