Excel Macro indexing problem

    [xpost][/xpost]

    Hi, my name is Joaquin Alonso.


    I retired 18 years ago and I’m starting to use Excel macros again.

    As a test I’m changing a calendar maker code that I copied from an Excel help site.

    The original calendar provides for a single entry on a specific day. As a first attempt, I was able to successfully add 12 rows to each day of the calendar.


    Now that I added the 12 daily rows, I am attempting to expand the 7 original columns to 14.

    Well, I’m having trouble when indexing this new array, I:


    Successfully:

    • Successfully expand the columns from 7 to 14.
    • Place the days of the week in the right columns of the calendar.

    Problem:

    • It seems like the indexing that places number of days in the right columns is off,

    As well as the generation of the number of days is incorrect.


    I’m attaching the VBA code as well as the page with the problem.


    Book3.xlsxCalendar Money Field.xlsx

    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,

    Your error handler for the date entry inputbox is not a good idea, any error subsequently throws up the same message, for instance I get an error on this line.

    Code
    If Range("A68:A78").Value = "" Then Range("A68:A78").Offset(0, 0)

    It is also not very clear how you want the inputbox filling

    Try this, I've made some amendments to the code to sort the error messages.I'll see if I can sort the formatting of the sheet

    I

    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

    If Range("A68").Value = "" Then Range("A68:A78").Offset(0, 0) _
    .Resize(13, 14).EntireRow.Delete


    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 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;


    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;


    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,

    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:


    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 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.

    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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login