converting Formula into absolute value

[leo73pk]

I'm enclosing a sample workbook. In my sheet Cells K4:L6 are linked with Cells F4:G6.

I want that if cell J2=2, then the values of K4:L6 becomes absolute values (not linked with F4:G6), but if Cell J2=1, then again K4:L6 are linked with F4:G6.



This post is also posted at OZGRID.com. The link to this post is as follows;

[URL="https://www.ozgrid.com/forum/index.php?thread/1227377-vba-code-for-converting-formula-to-value/&postID=1234499#post1234499"]

[AliGW]

It is not possible to do what you want. A cell can contain either a value or a formula, but not both, so you cannot make it switch between being linked to another cell and containing a value in the way you describe. It may be possible using VBA - shall I move the thread for you?

[leo73pk]

sure you can.

[AliGW]

What - move the thread? Or are you saying that I am wrong? Please be clear.

[leo73pk]

Move the thread for VBA solution.

[AliGW]

Please ...

OK - I'm moving it now.

[leo73pk]

Ali Thanks for moving the thread to the appropriate forum.

[Vraag en antwoord]

And what if J2 <> 1 or <> 2

[leo73pk]

J2 must be either 1 or 2

[Vraag en antwoord]

Please Login or Register  to view this content.

[leo73pk]

After saving the workbook as macro enabled workbook I copied this vba code and save the file once I change the value of J2 from 1 to 2, it gives an error message;

"compile error: Expected endsub"

Kindly advise

[Vraag en antwoord]

Then you will not have applied it properly.

You ask for a VBA solution and then I assume that you also have some knowledge of it.

[leo73pk]

My apologies, I missed End Sub.
What if I have to apply it for multiple ranges on the same sheet.

[leo73pk]

Vraag, thanks for your prompt reply. The proposed VBA code works for a single Cell Range K4:L6 but when I apply it for multiple ranges by adding following changes

With Range("K4:L6", "K10:L12")"

it works but when I change the value of J2 to 1, in Cells K7:L8 link established with F7:G8.

Kindly advise

[Vraag en antwoord]

You run that risk if you are not completely in the question. I have given you how to tackle it with code. So you may first try something yourself.

[leo73pk]

I have tried Sir, That's why i'm asking that once I made certain change to the code you proposed it serves the purpose for multiple ranges but as I mentioned above it gives outcome like this

"The proposed VBA code works for a single Cell Range K4:L6 but when I apply it for multiple ranges by adding following changes

With Range("K4:L6", "K10:L12")"

it works but when I change the value of J2 to 1, in Cells K7:L8 link established with F7:G8. I only want that this vba code applies to the multiple ranges I defined.

Kindly advise

[leo73pk]

I have used this VBA code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "J2" Then
With Range("K4:L6,K10:L12,K16:L18")
If Target.Value = 2 Then .Value = .Value Else .Formula2R1C1 = "=RC[-5]"
End With
End If
End Sub

Its working for K4:L6 and K16:L18 cell ranges. but when I turn the value of cell J2 to 2 it gives undesired results for K10:L12 (sample workbook is enclosed)

Please advise how to get this resolved.

[AliGW]

Administrative Note:

We would very much like to continue to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[leo73pk]

I have used this VBA code

Please Login or Register  to view this content.

Its working for K4:L6 and K16:L18 cell ranges. but when I turn the value of cell J2 to 2 it gives undesired results for K10:L12 (sample workbook is enclosed)

Please advise how to get this resolved.

[leo73pk]

In the enclosed workbook, the below VBA code works as per the requirement i.e. when Cell J2=2, Cell K4:L6 formula becomes absolute values and when Cell J2=1 again the Cell K4:L6 formula restored (its link with Cells F4:G6).

Please Login or Register  to view this content.

Now i want to modify the above VBA code in such manner that it works the same ways for other cell ranges K10:L12 and K16:L18 or any other cell ranges which i opt to definer later. Sample workbook is enclosed for reference.

[bakerman2]

How about this.

Formula2 uses A1-style.

Please Login or Register  to view this content.

[leo73pk]

How about this.

Formula2 uses A1-style.

Please Login or Register  to view this content.

The change you proposed is giving the same result as the existing vba code.

I want to amend this vba code that it works in a similar manner as it is currently working for cell range K6:L6 based on Cell J2 value. Therefore, I'm looking forward to have an amended VBA code so that it work for K10:L12 and K16:L18 or any other cell ranges which I opt to define later. Sample workbook is enclosed for reference.

[bakerman2]

Temporary solution.

Please Login or Register  to view this content.

[leo73pk]

Dear Bakerman2,

You have mentioned a Temporary Solution before the VBA code. what does that mean?. However, your VBA code serves the purpose as per the structure of my enclosed sample workbook. I'll appreciate if you suggest the changes in VBA code, if I the same procedure to be performed on cells which are not at RC(-5) position. i.e. F21:G21 are linked with B4:C4 and N23 linked with B6. how to include that in VBA Code.

[Pepe Le Mokko]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

[leo73pk]

This post is also posted at OZGRID.com. The link to this post is as follows;


https://www.ozgrid.com/forum/index.p...99#post1234499

[leo73pk]

Thanks Bakerman2,

Your proposed VBA Code gives the desired result. Thanks a lot for your time to help me.