Help With A Lost Macro

%3CLINGO-SUB%20id%3D%22lingo-sub-962671%22%20slang%3D%22en-US%22%3EHelp%20With%20A%20Lost%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-962671%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Kind%20Community%3A%3C%2FP%3E%3CP%3EI%20am%20an%20octogenarian%20that%20needs%20help%20with%20a%20lost%20Macro%3B%20but%20first%20I%20have%20some%20questions%20that%20are%20bugging%20me%3A%20(1)%20Why%20do%20I%20have%20multiple%20PERSONAL.xlsb%20files%20in%20the%20folder%20C%3A%2FUsers%2FJerry%2FAppdata%2FRoaming%2FMicrosoft%2FExcel%2FXLSTART%3F%20I%20assume%20that%20because%20they%20also%20contain%20words%20like%20Autosaved%20and%20AutoRecovered%2C%20they%20arose%20from%20glitches.%20They%20contain%20Macros%20but%20not%20the%20one%20that%20I%20seem%20to%20have%20lost.%20Further%2C%20the%20one%20that%20opens%20when%20I%20open%20Excel%20is%20the%20one%20with%20the%20most%20recent%20date.%20This%20leads%20to%20my%20next%20question.%20(2)%20When%20I%20open%20Excel%2C%20that%20PERSONAL%20file%20remains%20open%20and%20visible.%20I%20thought%20that%20the%20PERSONAL%20file%20works%20in%20the%20background.%20(3)%20Note%20the%20content%20of%20the%20Macro%20captured%20image.%20There%20are%202%20macros%20FormatLinkedInExportFile%20and%20FormatNewLinkedInExportFile.%20These%20should%20remain.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20to%20the%20guts%20of%20my%20question.%20I%20had%20a%20Macro%20that%20no%20longer%20exists.%20It%20was%20CountCellsByColor.%20The%20range%2C%20which%20is%20dynamic%2C%20is%20currently%20M3%3AM7599.%20The%20formula%20%3DCountCellsByColor(M3%3AM7599%2CL7602)%2BM7601%20is%20in%20M7602.%20L7602%20contains%20the%20color%20(BLACK)%20to%20be%20evaluated.%20So%2C%20to%20further%20explain%2C%20the%20range%20contains%20both%20number%20cells%20and%20color%20cells.%20M7601%20is%20the%20sum%20of%20the%20number%20cells.%20The%20formula%20is%20supposed%20to%20count%20the%20number%20of%20color%20cells%20in%20the%20range%20and%20add%20it%20to%20the%20sum%20of%20the%20number%20cells.%20Right%20now%20the%20formula%20evaluates%20to%20%23NAME%3F.%20Can%20the%20macro%20that%20I%20described%20be%20easily%20recreated%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20wish%20to%20use%20the%20same%20macro%20for%20a%20new%20column%20that%20has%20been%20created%20(Column%20N).%20The%20same%20relative%20cells%20would%20be%20utilized%20as%20in%20Column%20M%20and%20the%20color%20to%20be%20evaluated%20(YELLOW)%20would%20be%20in%20O7602.%20Can%20this%20be%20easily%20done%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20all%20of%20your%20assistance.%20Please%20let%20me%20know%20if%20there%20are%20any%20questions.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-962671%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-967748%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20With%20A%20Lost%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-967748%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F435604%22%20target%3D%22_blank%22%3E%40JbScG_2%3C%2FA%3E%20Lets%20take%20this%20one%20step%20at%20the%20time.%3C%2FP%3E%0A%3CP%3E1.%20Clean%20out%20XLSTART%3C%2FP%3E%0A%3CP%3E-%20Close%20Excel%20and%20move%20all%20files%20from%20XLSTART%20to%20another%20folder%20where%20you%20can%20easily%20find%20them%3C%2FP%3E%0A%3CP%3E-%20Open%20these%20files%20in%20Excel%3C%2FP%3E%0A%3CP%3E-%20Compare%20the%20modules%20and%20macro's%20in%20all%20of%20them%20(easiest%20to%20do%20in%20the%20VBA%20editor)%3C%2FP%3E%0A%3CP%3E-%20If%20you%20find%20a%20macro%20in%20any%20of%20the%20ones%20not%20called%20personal.xlsb%20which%20you%20need%20to%20keep%2C%20copy%20that%20one%20over%20to%20personal.xlsb.%20Perhaps%20the%20%3CSPAN%20style%3D%22display%3A%20inline%20!important%3B%20float%3A%20none%3B%20background-color%3A%20%23ffffff%3B%20color%3A%20%23333333%3B%20font-family%3A%20'SegoeUI'%2C'Lato'%2C'Helvetica%20Neue'%2CHelvetica%2CArial%2Csans-serif%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%3ECountCellsByColor%3C%2FSPAN%3E%20function%20is%20there%20too.%3C%2FP%3E%0A%3CP%3E-%20Once%20you've%20salvaged%20the%20macro's%20you%20need%2C%20close%20all%20of%20them%20but%20only%20save%20personal.xlsb%3C%2FP%3E%0A%3CP%3E-%20Open%20the%20personal.xlsb%20and%20click%20the%20View%20menu.%20Click%20the%20Hide%20button.%3C%2FP%3E%0A%3CP%3E-%20The%20file%20seems%20to%20disappear%2C%20but%20don't%20worry%2C%20just%20close%20Excel.%20It%20should%20prompt%20whether%20you%20want%20to%20save%20the%20file.%20Click%20Yes.%3C%2FP%3E%0A%3CP%3E-%20Now%20copy%20the%20updated%20Personal.xlsb%20back%20to%20your%20XLSTART%20folder%3C%2FP%3E%0A%3CP%3E-%20Make%20sure%20to%20delete%20all%20other%20files%20in%20that%20folder%3C%2FP%3E%0A%3CP%3E2.%20Fix%20%23Name%20error%3C%2FP%3E%0A%3CP%3E-%20Open%20the%20file%20which%20uses%20this%20function%3C%2FP%3E%0A%3CP%3E-%20Choose%20Data%2C%20Edit%20links%3C%2FP%3E%0A%3CP%3E-%20Personal.xlsb%20is%20likely%20in%20there%2C%20click%20it%20and%20choose%20change%20source%20and%20navigate%20to%20personal.xlsb%20in%20your%20XLSTART%20folder.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello Kind Community:

I am an octogenarian that needs help with a lost Macro; but first I have some questions that are bugging me: (1) Why do I have multiple PERSONAL.xlsb files in the folder C:/Users/Jerry/Appdata/Roaming/Microsoft/Excel/XLSTART? I assume that because they also contain words like Autosaved and AutoRecovered, they arose from glitches. They contain Macros but not the one that I seem to have lost. Further, the one that opens when I open Excel is the one with the most recent date. This leads to my next question. (2) When I open Excel, that PERSONAL file remains open and visible. I thought that the PERSONAL file works in the background. (3) Note the content of the Macro captured image. There are 2 macros FormatLinkedInExportFile and FormatNewLinkedInExportFile. These should remain.

 

Now to the guts of my question. I had a Macro that no longer exists. It was CountCellsByColor. The range, which is dynamic, is currently M3:M7599. The formula =CountCellsByColor(M3:M7599,L7602)+M7601 is in M7602. L7602 contains the color (BLACK) to be evaluated. So, to further explain, the range contains both number cells and color cells. M7601 is the sum of the number cells. The formula is supposed to count the number of color cells in the range and add it to the sum of the number cells. Right now the formula evaluates to #NAME?. Can the macro that I described be easily recreated?

 

I also wish to use the same macro for a new column that has been created (Column N). The same relative cells would be utilized as in Column M and the color to be evaluated (YELLOW) would be in O7602. Can this be easily done?

 

Thanks in advance for all of your assistance. Please let me know if there are any questions.

1 Reply
Highlighted

@JbScG_2 Lets take this one step at the time.

1. Clean out XLSTART

- Close Excel and move all files from XLSTART to another folder where you can easily find them

- Open these files in Excel

- Compare the modules and macro's in all of them (easiest to do in the VBA editor)

- If you find a macro in any of the ones not called personal.xlsb which you need to keep, copy that one over to personal.xlsb. Perhaps the CountCellsByColor function is there too.

- Once you've salvaged the macro's you need, close all of them but only save personal.xlsb

- Open the personal.xlsb and click the View menu. Click the Hide button.

- The file seems to disappear, but don't worry, just close Excel. It should prompt whether you want to save the file. Click Yes.

- Now copy the updated Personal.xlsb back to your XLSTART folder

- Make sure to delete all other files in that folder

2. Fix #Name error

- Open the file which uses this function

- Choose Data, Edit links

- Personal.xlsb is likely in there, click it and choose change source and navigate to personal.xlsb in your XLSTART folder.