Quick Excel VBA Code - 18/01/2020 16:40 EST

Állapot: Closed
Díj: $20
Beérkezett pályamű: 9
Nyertes: Farrukh Saeed

Winner

A verseny összegzése

I would like to get some VBA code written that does the following:
(1) In the active workbook, copy and paste as values all cells that link to the Active Worksheet
(2) Have a message box that says "Are you sure you would like to hardcode all references to this sheet?" With a Yes or No choice for the user.

The goal of the code is to be able to delete the sheet in the workbook without causing "#Ref" errors to come up in the workbook.

Please submit a .txt file.

Ajánlott készségek

Winner

Tegyen közzé egy hasonló versenyt

Munkaadói értékelés

“I have worked with many freelancers on VBA projects and Furrukh is among the best”

briandoherty22, United States.

A verseny legjobb pályaművei

Farrukh Saeed Pakistan

2
babusamrat Bangladesh

0
pogodin Belarus

1
Muhammadadil1 Pakistan

0
gato63 Venezuela

1

További pályaművek

Nyilvános pontosítófelület

matthew34362
- 4 év telt el
That's a sloppy solution chosen as the final "winner":

1) It uses the clipboard to do a copy and paste, when all that was necessary was to assign the cell .Value field to the .Formula field without interfering with the Windows clipboard at all.

2) It assumes any instance of the worksheet name in a cell is a reference to the worksheet. Text in a cell could mean anything. That's not parsing syntax. That's just ridiculously bad software design, making large assumptions. It could easily break in unpredictable ways in the future.
- 4 év telt el
Farrukh Saeed
- 4 év telt el
do you need file? inbox me
- 4 év telt el
matthew34362
- 4 év telt el
This is not as quick and simple as you think.

The nested for loops over all sheets, all rows, all columns is the easy part, sure.

But determining all references to a particular cell across from other worksheets (in a *reliable* way) is a parsing mess. Search the Web to find discussion about this by some pretty smart people if you don't believe me.
- 4 év telt el
pogodin
- 4 év telt el
Hello !

My experience with vba is more than 10 years.
Can You upload example of xls file ?

Thank You. Regards, Alexei.
- 4 év telt el
Farrukh Saeed
- 4 év telt el
hi please check #9 if you need it for testing inbox me
- 4 év telt el
gato63
- 4 év telt el
Hello, Please see #2 . Than you
- 4 év telt el
mvchetty
- 4 év telt el
Hi There, Please see the entry # 6 and contact me is there any questions. Cheers! - Murali
- 4 év telt el
blui88
- 4 év telt el
Hi please share your views about my entry # 5
Regards
Santosh
- 4 év telt el