VBA equivalent of input() for active cell(s)

i_excel

Board Regular
Joined
Jun 4, 2015
Messages
113
Hi

I'm interested in what seems should be a simple macro - It should put an equals sign before the value of each cell in the active array. But I have had little luck as a novice.

Recording the macro does not work. Use of the recorded macro in another cell returns the value of the cell that is similarly offset from the originally referenced cell. If the relative reference in the VBA code is replaced with ActiveCell, the recorded macro places the value of the active cell in single quotation marks and does not fetch the referenced cell value.

To clarify the result I have in mind, take the following for example. Cell C3 holds the value So Far, So Good. A1 has the value C3. After implementing the desired macro, A1 should hold the value So Far, So Good and the function =C3. A similar function would be performed in each cell of an array if multiple cells were selected at initiation.

I would be most interested to see if (and how) this can be coded. Let me know if you would like me to further clarify my question.

Kind regards
i_excel
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Provided A1 is not formatted as text then this should do it:

Code:
Range("A1") = "=" & Range("A1")
 
Upvote 0
Hi steve the fish

Thank you for your reply. Your code is new to me. I was using

ActiveCell = "=" & ActiveCell

<tbody>
</tbody>

This works for one cell, but not any more. The range code you use is new to me. Can it be adapted so that multiple cells (active rather than specified) are modified to the same effect and at once?

Kind regards
i_excel
 
Upvote 0
A thousand times thank you. You have simultaneously answered my question and introduced me to the "For Each" 'loop'. That is neat.

Kind regards
i_excel
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top