I am attempting to change in a range, the cell references on
a number of cells from relative to absolute.
The macro results are garbled with the wrong cell addresses.
I am submitting the workbook with the macro as well as several
Test sheets.
Any help would be greatly appreciated
Joaquin Alonso
Your code refers to drawing a calendar, no sign of that. Also, if you remove protection and switch off screen updating then both must be restored at the end of the code
Excel has the ConvertFormula method to do this.
Sub Change_Cell_Reference_Relative_To_Absolute()
Dim oWs As Worksheet, rCl As Range
''///not needed
' Prevent screen flashing while drawing calendar.
' Application.ScreenUpdating = False
''/// Change References
On Error Resume Next
For Each oWs In ThisWorkbook.Worksheets
''/// Unprotect sheet if had previous calendar to prevent error.
oWs.Unprotect
For Each rCl In oWs.Range("D10").CurrentRegion.SpecialCells(xlCellTypeFormulas)
rCl.Formula = Application.ConvertFormula(rCl.Formula, _
xlA1, xlA1, xlAbsolute)
Next rCl
oWs.Protect
On Error GoTo 0
Next oWs
End SubRoy;
As always you are right.
My reasoning was that the calendar code is completely clean and I do not want to clutter it with
my learning curve, I'd rather keep it away from that clean code to avoid cluttering it until the
new code is ready to be applied and tested on the calendar.
However; if you prefer for me to include it in the calendar code, then I will do so and upload the
workbook on the calendar thread.
As I as said before, when I am done with this process, I plan to share it completely, the code as well
as the documentation where it can be applied. This is my pledge to you and OzGrid for providing me
with a wealth of knowledge that I do not possess.
Please advise if you want the cell reference code applied to the calendar code.
Also, I am really weak on the looping and indexing topics, I've looked thru the internet to see what
is out there in these topics, I have found nothing that I could readily use due to my lack of
understanding of the VBA macro syntaxes.
Roy, so you can know me better, please bear with me as the following lines may be lengthy:
I come from the old days, my knowledge at the time was in systems analysis and programming with expertise in design and maintenance of online systems applications and the use of communications protocols in dedicated broadband telephone lines used for those online applications.
The hardware was IBM Mainframes and and Series1 minicomputers.
The languages Mainframe BAL and COBOL, Series1 language.
I started in microcomputers when I retired, so I never got the education or training I would have needed
to continue in the modern IT world.
As you can see I have the experience in systems design and programming to use the techniques that
I need to apply. I lack the in-depth knowledge of the new languages, although analyzing what you sent
me so far I understand a lot of the logic.
To help me in expediting my learning curve, maybe you could provide me with the name of
a VBA/Macros syntax instruction manual for beginners.
Thanks in advance for listening.
Joaquin Alonso
Thanks for your reply.
I don't need the rest of your code, I have supplied code to change the cell references for you.
I don't use books, I just learn by trying different code and seeing how others tackle problems.
Roy,
That is fantastic. That is the way I learned a lot of what I know. I only went to the specification books when
there was a question about instructions being applied to specific code to achieve the desired results.
In my particular case I sent you the workbook as a sample to demonstrate what I was doing and how the code behaved.
Right now I am working with the code you sent me.
I will keep you informed of my advance.
This is a really good learning site. See what answers are provided, try to understand the code and adapt it for your own use.
You will soon be answering questions yourself.
Don’t have an account yet? Register yourself now and be a part of our community!