Posts by Joaquin Alonso

Roy,

Yes this is a nasty condition, but I do not complain, although with certain difficulty some times, I still can see,

there are people that are less lucky than me.

I already found the code tag Icon, thanks for you kind offer. As always, I can wait for your analysis of this situation whenever you can, if you need the rest of the code, I will provided using the tag code.

Again, thanks for your help.

Jack

Code
For x = 0 To 10
Range("A1:C1").Offset(x * 1, 0).Select
If IsEmpty(Range("B1:C1").Value) = True then Range("A1").Offset(x * 1, 0).Selection.NumberFormat = "0"
Next

Roy, this code relates to the same problem. I am simply exploring other functions that could be applicable to what I am doing/learning.

I've created a list of cells extracted from the calendar and I am trying to establish the conditions for the sort function to delete non-significant rows automatically. I believe this could not be achieved if I do not set the date field to zeroes, being the only significant field in the rows.

From now on I will be using the code tags, I've read rules before about the code tags, my problem is that I have an ailment called retinopathy (bad eyesight) and could not find the Icon for it, sorry.

If you need the complete set of code I will provided for you.

Please, advice

Thank you, Jack

Hi Roy,

The intent of the code below is to eliminate empty rows using the sort function. I am trying to do this by setting to zeroes a date field which is the only field in the row with significant data.

I used various functions that should've done what I am trying to accomplish. The results of the execution of this code is that it does not set to zeroes the date field so the sort function does not delete the rows in question.

For x = 0 To 10
Range("A1:C1").Offset(x * 1, 0).Select
If IsEmpty(Range("B1:C1").Value) = True then Range("A1").Offset(x * 1, 0).Selection.NumberFormat = "0"
Next

Thanks again for providing your help and expertise.

Jack

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.

Roy;

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

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

Roy,

This is exactly what I need, I will study your change since it corrects

the problem I was having.

As you can readily see, I am weak in the new indexing technologies,

although I understand the logic, I lack the new expertise in the code

syntax as well as its application.

Needless to say I will keep informed of my progress and will share

my new findings and/or developments with you and whoever else

you deem that could benefit from my learning experience.

Again, grateful for your efforts,

Joaquin Alonso

Roy,

Take your time and thank you

Hi Roy

Haven't communicated with you for a while.

I do not know if I I am allowed to un-watch this thread, if I can what effect

would this action have on this problem.

Please advice

Thank you

Joaquin Alonso

Roy,

I read the OzGrid Forum Rules when I originally posted my problem. I obviously did not read the rules with enough detail to understand each of them, please forgive my hastiness, it will not happen again.

I have resigned from using the MrExcel Forum and inform them of my decision and that I posted the problem

as a way to shorten my learning curve and that I was happy with the lessons I received from OzGrid's interactions, without realizing that I was violating OzGrid's Forum Rules as well as theirs.

I am attaching the workbook again.

Thanks for your help and advice.

Roy:

Yes, I posted this problem in MrExcel . I was not aware I was violating one of the rules,

simply by my lack of knowledge of how this sites work internally. It seems I violated a

very important rule and I stand corrected. As a measure of correction I posted to MrExcel

that I apologize for the violation and that I resigned from using their site, since this was

an attempt to shorten my learning curve and that I was happy with OzGrid's performance.

I am also sorry if this situation caused problems to the OzGrid site.

Joaquin Alonso

Roy,

I believe I've isolated the code causing one of the problems.

I updated the macro with the following comment at the place the error is happening:

' ************************************************************

' ***

' *** One of the problems is in this Statement:

' *** For Each cell In .Range("A3: N15").Select

' ***

' *** For Each cell In .Range("A3: N15").Select

' ***

' *** Its propagating the number of days across the top of the

' *** group of cells for all weeks of the month. Instead of

' *** selecting the specific cell where to place the number for that day

' *** The following cells are the candidates to be propagated with the number of the day:

' ***

' *** Cells on group 1 A3 C3 E3 G3 I3 K3 M3

' *** Cells on group 2 A16 C16 E16 G16 I16 K16 M16

' *** Cells on group 3 A29 C29 E29 G29 I29 K29 M29

' *** Cells on group 4 A42 C42 E42 G42 I42 K42 M42

' *** Cells on group 5 A55 C55 E55 G55 I55 K55 M55

' *** Cells on group 6 A68 C68 E68 G68 I68 K68 M68

' ************************************************************

Roy;

I'm attaching the latest copy of the workbook.

Jack

PS: I'm sorry, yes I did change the workbook on my POST # 8

Roy;

As the saying goes, "There is more time than life".

What you have provided for me, is knowledge and to me that is priceless.

I'm still tinker-toying with the debug function. The minute I find what I'm

looking for I'll let you know. Don't forget, this exercise plus our interaction

is giving me a lot of excellent information and knowledge, it's shaking up

a lot of my rust.

In the mean, take your time.

Jack

Roy;

Just to keep you updated about my doings.

Since I wrote to you last, I've been trying to use the various features of the debug function.

I'm attempting to find the function the would display the values generated in a specific patch

of code, so far I've been unsuccessful. A lot has to do with my re-familiarization of the debug

function.

I will post you of my progress.

Thanks

Roy,

I took your code and incorporated it to my new changes.

The new macro is working fine.

However; the original problems still exits, can't:

Propagate the number of days in the proper cells.

Generate the correct number of days.

I'm working with the debug function to see if I can isolate

the problems.

I'm attaching the new workbook with the new macro in it.

Roy'

Thank for your efforts,

While you were working on this problem, I was too.

Well thanks to your finding of the code in error (See your prior post), I corrected the code

in question and now I am able to border all cells.

Corrected Code

The result of this code is to delete the last set of rows if no numbers were generated for these cells

I tested this change and it is deleting the unused rows of the calendar.

That is a positive steps.

I'm still not able to successfully:

Propagate the number of days in the proper cells.

Generate the correct number of days.

I'm still working in these two problems without the proper results.

I attached a copy of the workbook

Again, thanks for your help

Roy,

i will look into my error handling routine.

It may take me a while, but I will get back to you

as soon as I have some definite work done.

Thank you

Roy,

Thanks for your prompt reply.

I did not check the workbook before attaching it and added a test member

I use for testing several scenarios. Please forgive my eagerness and

assumption. I am attaching only the workbook with the worksheet empty and

the macro within the workbook.

When executing the macro, it will take you thru the following screens:

Screen 1: “Type in Month and Year for the Calendar”

Enter: “July 2020”

Click on: “OK”

------------------------------------------------

After you will get an error screen:

Screen 2: "You may not have entered your Month and Year correctly”

"Spell the Month correctly (or use 3 letter abbreviation)”

“and 4 digits for the Year”

Click on: “OK”

------------------------------------------------

After the last reply the macro will take you back to the original screen:

Screen 3: “Type in Month and Year for the Calendar”

Enter: >> Do not enter any information <<

Click on: “Cancel”

------------------------------------------------

After this last screen, the macro will execute the code to the end.

____________________________________________________

To re-start the macro process in the worksheet:

• Delete columns A thru O.
• Select cell A1.
• Re-start the macro process again.

By following the 3 steps above, the macro process can be re-started

as many times as necessary.

Thanks for your help,