excel help

Reply #29

lottaloot — Fri, 10 Jun 2005 17:54:27 GMT

Please ignore the above. The problem is not with the formula, it is with my technigue. On my all states spreadsheet it works fine--it is on the MN sheet that has the problem.I will retrack with I'm doing wrong. Thanks SirMetro

Reply #28

lottaloot — Fri, 10 Jun 2005 17:18:26 GMT

Got that part; however, it is not recognizing doubles

Reply #27

SirMetro — Fri, 10 Jun 2005 17:14:25 GMT

oh....I forgot to mention one itsybitsy little item.if the number is 023 or 005, you MUST enter into your spreadsheet cell as '023 or '005The reason for this is because Excel automatically trims the leading 0 out, something I am sure you have noticed. The apostraphe works just fine and you will get the result you are interested in.Sir Metro

Reply #26

lottaloot — Fri, 10 Jun 2005 16:55:21 GMT

ThanksThat's works just fine.

Reply #25

SirMetro — Fri, 10 Jun 2005 16:53:23 GMT

Lottaloot,If I understand your question correctly, I think you are trying to determine root sum.369 = sum 18 = root 9in Excel, the formula would look like this (assuming the 369 is in cell B11)=IF((SUM((LEFT(B11,1)*1)+(MID(B11,2,1)*1)+(RIGHT(B11,1)*1)))>9,(SUM((LEFT((SUM((LEFT(B11,1)*1)+(MID(B11,2,1)*1)+(RIGHT(B11,1)*1))),1)*1)+(RIGHT((SUM((LEFT(B11,1)*1)+(MID(B11,2,1)*1)+(RIGHT(B11,1)*1))),1)*1))),(SUM((LEFT(B11,1)*1)+(MID(B11,2,1)*1)+(RIGHT(B11,1)*1))))If the above formula looks a bit long, th... [ More ]

Reply #24

lottaloot — Fri, 10 Jun 2005 05:12:04 GMT

Simple question but I can't make it work for me. Trying to get excel to calculate the sum of a number into its root.

Reply #23

SirMetro — Wed, 08 Jun 2005 03:13:04 GMT

PS...after you record your macro...you will learn how to clean it up. Also, highlight the formulas and hit F1 to see what it really means. Thats sometimes the best way to learn the system.

Reply #22

SirMetro — Wed, 08 Jun 2005 03:10:58 GMT

LOL...around 15 or so years ago...the same ding ding ding happened to me with Lotus 123. I had gotten so frustrated not being able to figure out Lotus, I actually wrote a program to do what the spreadsheet was suppose to do. Today, I do everything in Excel...from breif notes to complex quotes and billing documents. With time comes experience. Now that the bells have sounded, your limits is your imagination...I look forward to your great results.Sir Metro

Reply #21

lottaloot — Wed, 08 Jun 2005 02:21:18 GMT

Ding, Ding, Ding!!!After checking out this book on VBA 4 times, I get what a macro is.It's just an easy way to do the same repetitive, tedious actions over over over again.And now I can step in out of macro like it aint nothin I thought that when I recorded a macro, I had to go fast so I would'nt run out of tape , I guess but now I realize, it's just like dictation. It copies down what you want to do to your spreadsheet plays it over over at the click of a button. Thanks Sir Metro... [ More ]

Reply #20

Sandy K — Mon, 06 Jun 2005 14:43:43 GMT

they are the best, aren't they, Lottaloot?And again, thank you guys for dummies like me for helping me out in the past and in the present.

Reply #19

lottaloot — Mon, 06 Jun 2005 14:35:24 GMT

Todd, I have 2003 excel also. SirMetro, Thanks again. And to everyone else who assisted in this thread-- You guys are the best

Reply #18

SirMetro — Mon, 06 Jun 2005 13:23:12 GMT

Lottaloot, you can actually cut and paste everything below. Use function key F8 to step thru to watch what it does. The comments will turn to green after you finish the paste process. Good Luck.Sir MetroSub TestForZero()i = 0 'Initialize i to 0'Create a Loop, set it to be whatever range you want to test forFor i = 1 To 100 'Create a point of reference (Trim statement pulls the unwanted spaces from the number) xCellLocation = N + Trim(Str(i)) 'Select the intended point of reference Rang... [ More ]

Reply #17

Todd — Mon, 06 Jun 2005 12:25:51 GMT

lottaloot,If all you're trying to do is to color a cell when it is a particular value (or in a range of values), then using a macro is not necessary.Let me preface this by saying that I use Excel 2003, so I cannot be sure this feature is in your version (if you use an earlier version), but it should be there.There is something called conditional formatting . It is rather easy to use.I will give you a step-by-step how to use it, and then I'm sure you can play around and exploit some of the real... [ More ]

Reply #16

lottaloot — Mon, 06 Jun 2005 05:16:21 GMT

OK. Scratch everything above! This is kind of what I want to do except it changes every grey cells color I only want it to change those cells that have 0 in them.How can I do this? Selection.Interior.ColorIndex = 40 Range( B:B,D:D,F:F,H:H,J:J,L:L,N:N ).Select Range( N1 ).Activate Cells.Find(What:= 0 , After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=True).Activate Selection.... [ More ]

Reply #15

lottaloot — Mon, 06 Jun 2005 04:57:21 GMT

If Range( A3 ).Select = 0 Then Selection.Font.ColorIndex = 3 Else Selection.Font.ColorIndex = 5I then tried to step into it andadd the above. Now sheet 2 (A2) turnes to grey. I guess I am getting closer to something

Reply #14

lottaloot — Mon, 06 Jun 2005 04:43:08 GMT

Getting closer. But when I try and run the macro, a pop-up of Run-time error 9 subscript out of range occurs. When I hit debug, this is what I seeSub pairbook2()Sheets( doubles ).SelectRange( a1 ).SelectSelection.CopySheets( sheet2 ).SelectRange( a2 ).SelectSelection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=FalseEnd SubDid it again and it did thisSub pairbook2()Sheets( doubles ).SelectRange( a1 ).SelectSelection.CopySheets( sheet2 ).SelectRange( a2 ).Sele... [ More ]

Reply #13

SirMetro — Sun, 05 Jun 2005 23:34:11 GMT

the simpliest approach to the entire process is topress CNTRL + F8which will bring up the Macro menu.Click CREATE Macro, it will ask for a name then paste in the previous formulas.Sir Metro

Reply #12

lottaloot — Sun, 05 Jun 2005 19:01:28 GMT

submacro()Sheets( sheet1 ).SelectRange( a1 ).SelectSelection.CopySheets( sheet2 ).SelectRenage( a2 ).SelectSelection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=FalseEnd SubOK. I managed to play around with this one and it let me place the macro into VBA and was told by micro excel that no RETURN() or HALT()function found on the macro. What does that mean? Am I getting closer to do it correctly? Also, do you mean that I can name the macro SubanynameIChoose

Reply #11

lottaloot — Sun, 05 Jun 2005 16:38:29 GMT

Thanks. I did that and so far I only see user form, module, class module as choices here. Maybe once I get home, I will see add new module on a newer version of excel. But this does help. Any other tips are appreciated.

Reply #10

SHTh — Sun, 05 Jun 2005 14:25:55 GMT

LottalootWith reference to the code you posted, it has missing code at the beginning that tells excel that it is a macro.All macros must start with the code: Sub MacroName() example Sub colmatch()All macros must end with: End SubHence copy the code and paste the code betweenSub colmatch() Paste your macro code hereEnd SubLottaloot you may need to edit the code, so that it specifies what sheets you want to format.Sorry I haven't got time to test the code for you, but goodluck.SHTH... [ More ]

Reply #9

SHTh — Sun, 05 Jun 2005 14:12:42 GMT

1) Press Alt+F11 to open VBA2) From Insert menu, select Add New Module3) Copy and paste the code4) close and return to Excel5) To run macro: Press F5 or go via tools menu select macro name in macro list and Run.SHTH

Reply #8

lottaloot — Sun, 05 Jun 2005 13:50:21 GMT

Trying to figure it out. Went into the visual basic editor and tried to insert If Range( A3 ).Select = 0 Then Selection.Font.ColorIndex = 3 Else Selection.Font.ColorIndex = 5 into the module but I do not think that this is correct. HELP

Reply #7

lottaloot — Sun, 05 Jun 2005 13:27:14 GMT

OK excel experts, I need help as well understanding the macro above. SirMetro was kind enough to give me this marco formula as well but I don't know where the heck to place it. Do I go to macro section of excel and add a new macro?Do I type the formula into the marco area?Do I go under record put in the above as the macro?Basically speaking, I do not know what to do with a macro but I really want to color code certain areas. HELP

Reply #6

SirMetro — Sun, 05 Jun 2005 03:20:36 GMT

Below are 2 more options that can also be used to set colors of either text font or cell color. Hope they are usable. Also, the simple intent of the routines below is to establish a test for a cell. With a bit of massaging and a little creative thought process, you can about do anything. As for color codes, you will have to check the help list to determine the color with the number.The First example is to change the font colorIf Range( A3 ).Select = 0 Then Selection.Font.ColorIndex = 3 Else Sele... [ More ]

Reply #5

SirMetro — Sat, 04 Jun 2005 12:24:51 GMT

Glad to be of assistance.Sometimes, the simpliest solution is the most difficult to understand.Sir Metro

Reply #4

Sandy K — Thu, 02 Jun 2005 22:56:01 GMT

Sir Metro,I just talked to my husband......the macro worked.Thank you.I just told him, why does he have people work for him who can't figure that stuff out....and he told me the excel specialist was on vacation....came back today...and told him what you said was the way to go......You are the best......again, thank you.

Reply #3

Sandy K — Thu, 02 Jun 2005 21:30:00 GMT

thank you, Johnph and Sir Metro.............haven't had a chance to ask him if it worked or not. I know his employees said it was impossible to do what he wanted to do.........but he wanted me to ask you guys anyway just in case....

Reply #2

SirMetro — Wed, 01 Jun 2005 21:33:17 GMT

Below is the basic code for what he is asking it the program to do.Take it, apply a bit of programming thought to it, link it with a button and enjoy....be sure to adjust the BOLD items as needed to copy the data back and forth.Sub Macro1() Sheets( Sheet1 ).Select Range( A1 ).Select Selection.Copy Sheets( Sheet2 ).Select Range( A2 ).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=FalseEnd SubThere are other methods, th... [ More ]

Reply #1

johnph77 — Wed, 01 Jun 2005 20:51:14 GMT

Format them both at the same time - select (highlight) them both and format. If the cells are adjacent you can select them as a range and format and reformat using the range options. If they are not adjacent use the CTRL key to select both cells. They can be reformatted with the same process.There is no way I know of to reformat the second non-adjacent cell automatically upon the reformatting of the first cell.glj

excel help

Sandy K — Wed, 01 Jun 2005 18:47:54 GMT

anyone out there know the answer to this question...my husband emailed me this a few minutes ago.......hey .. ask one of your excel wizards this question ..I have a workbook with 2 worksheetsin cell AI of sheet 1 format the cell to have a pattern of redhow to you link cell a2 in sheet 2 so that the format is the same as cell a1 in sheet 1what I am trying to do .. if the color of cell a1 in sheet one is changed to purple... I want cell a2 in sheet 2 to automatically change to purple.anyone have... [ More ]