Macro problem for absolute reference

[ronlau123]

Dear big brother and sister,

I have a tricky task. I hope you can give me reply. appreciate your help.

I have the following macro. However, sometimes, it doesn't work, it may be because it doesn't applied on specific sheet. May I ask how can I modify the following macro to let it run on specific sheet only.

Secondly, as you go to sheet "before macro", after I use macro for Sub Absolute() (all cells selected are absolute reference", it returns to VALUE. May I know why?

Thanks

Please Login or Register  to view this content.

[realniceguy5000]

Hi,

You could use

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[ronlau123]

Thanks for your reply.

After i put the following code, it still doesn't work.
On the sheet "before macro", it still turn to value# (cell A 1to B7)
By the way, should i change sheets(1) of the macro to sheet(before macro)?

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Moreover, the 2nd macro you write may be incomplete. what is the purpose of macro?

Please Login or Register  to view this content.

[realniceguy5000]

Hi,

I was only just giving you examples to try not complete scripts you only need to try one of the ways to see if it works. After the Dim statement try one of these methods:

Please Login or Register  to view this content.

.

Also

Please Login or Register  to view this content.

.
Should be like this:

Please Login or Register  to view this content.

The number I was talking about is the count of the worksheets you want to run your code on. For example you open a new workbook you have 3 sheet tabs with the names sheet1 ,sheet2 and sheet3 Say you want to run code on sheet2 you could use sheets("sheet2") by the name of the worksheet or sheets(2) by the index number placement of the worksheet.

You dont really need to activate or select a worksheet to make a script work but it may help you to see what your script is doing when you step through it.

Hope that helps some.

[ronlau123]

I tried to use the following macro, its work.
However, A1 to B7 still return to value# after I use macro (on sheet "before macro")

I believe I may need to modify the following macro....

Please Login or Register  to view this content.

[ronlau123]

Dear big brother and sister,

Can anyone help me? It seems the formula is right, but I don't know why the result will return to VALUE#. Before using macro, the answer is right. I would like to have macro for absolute reference

I search in google for two hours.

[ronlau123]

Dear all,

After I search the macro for absolute reference in search engine for several days, I still can't get the answer why the macro for the following doesn't work

Please Login or Register  to view this content.

I tried several times, the macro still doesn't work. I appreciate your reply.

[watersev]

hi, ronlau, please check attachment, select cell or cells with formula and run code "Absolute"

[ronlau123]

Dear Watersev,

Thanks thanks for your reply. It is so awesome. I don't think I can get better answer from forum.

Please Login or Register  to view this content.

May I know which vba knowledge used in this macro (looping)? I tried to search in the web site, still can't get the solution. It seems that you develop the macro by yourself.

[watersev]

hi, I'm glad it helped though the code was slightly changed from the your's one.

The code uses:

- For Each...Next Statement
- ConvertFormula method

See VB help file for details.

If you are happy with the offered help, please mark the thread as solved