My Macro is too long!?!

[dukeofscouts]

Well the good news is that I got the macro written correctly. Bad news is that it is too long. So here goes my question: How can I get this shorter?

Background is that I'm working on an excel workbook to solve Soduko puzzels. I wanted to push my excel skills and see how I could use excel to mimic some tools other programs use to help beginners solve puzzels. So far things are going well.
-I have the Several 27*27 puzzel ranges set up,
--First starts in B2 and is a merged and copied down to a 9*9 grid. This is used to maintain the original puzzel so I can go back and start over,
--the second starts in AD2, this also is a 9*9 merge and copy that simply copies the the range from B2, I then can overwrite the formulas with my values here.
--The third in BF2 is a 27*27, this range creates the "pencil marks" showing what a each cell could be in the range AD2 based on the values in AD2.
--Finally (well not really but the others wont have any effect on this) there is CH2 a 27*27 that checks to see based off of the values in BF2 to see if there are any cells in AD2 that are not filled yet, but that there is a set value for it to be.

I've changed all TRUE and FALSE statements to 1 or 0 so that I could use custome formatting to edit what each cell displayes or hides without using the IF function.

My macro works to see if there is cell that the computer knows the answer to that I have not entered yet, copies the answer from the pencil marks, and puts it in the correct slot in the puzzle range.

I'm still a rookie at this so I typed it all out, not knowing how to create loops in macros yet.

Any suggetions as to what I can cut out, or how to change this to loop?

 

[derekpegg]

Probably not the best solution, but for a quick fix, you could cut some of the code into another sub then call that sub where your code was previously. Have done this several times with large subs.

Probably best to go through line by line and work on any loops you could use or redundant code, but my suggestion should work and would be very quick.

 

[dukeofscouts]

<TABLE class=MsoNormalTable style="MARGIN: auto auto auto 4.65pt; WIDTH: 121.5pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 5.4pt 0in 5.4pt" cellSpacing=0 cellPadding=0 width=162 border=0><TBODY><TR style="HEIGHT: 15pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH2).select¶if selection.value = 1 then¶Range(BF2).select¶selection.copy¶range(B2).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH3).select¶if selection.value = 1 then¶Range(BF3).select¶selection.copy¶range(b2).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH4).select¶if selection.value = 1 then¶Range(BF4).select¶selection.copy¶range(b2).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 3"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH5).select¶if selection.value = 1 then¶Range(BF5).select¶selection.copy¶range(B3).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 4"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH6).select¶if selection.value = 1 then¶Range(BF6).select¶selection.copy¶range(b3).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 5"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH7).select¶if selection.value = 1 then¶Range(BF7).select¶selection.copy¶range(b3).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 6"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH8).select¶if selection.value = 1 then¶Range(BF8).select¶selection.copy¶range(B4).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 7"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH9).select¶if selection.value = 1 then¶Range(BF9).select¶selection.copy¶range(b4).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 8"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH10).select¶if selection.value = 1 then¶Range(BF10).select¶selection.copy¶range(b4).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 9"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH11).select¶if selection.value = 1 then¶Range(BF11).select¶selection.copy¶range(B5).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 10"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH12).select¶if selection.value = 1 then¶Range(BF12).select¶selection.copy¶range(b5).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 11"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH13).select¶if selection.value = 1 then¶Range(BF13).select¶selection.copy¶range(b5).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 12"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH14).select¶if selection.value = 1 then¶Range(BF14).select¶selection.copy¶range(B6).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 13"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH15).select¶if selection.value = 1 then¶Range(BF15).select¶selection.copy¶range(b6).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 14"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH16).select¶if selection.value = 1 then¶Range(BF16).select¶selection.copy¶range(b6).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 15"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH17).select¶if selection.value = 1 then¶Range(BF17).select¶selection.copy¶range(B7).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 16"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH18).select¶if selection.value = 1 then¶Range(BF18).select¶selection.copy¶range(b7).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 17"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH19).select¶if selection.value = 1 then¶Range(BF19).select¶selection.copy¶range(b7).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 18"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH20).select¶if selection.value = 1 then¶Range(BF20).select¶selection.copy¶range(B8).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 19"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH21).select¶if selection.value = 1 then¶Range(BF21).select¶selection.copy¶range(b8).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 20"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH22).select¶if selection.value = 1 then¶Range(BF22).select¶selection.copy¶range(b8).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 21"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH23).select¶if selection.value = 1 then¶Range(BF23).select¶selection.copy¶range(B9).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 22"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH24).select¶if selection.value = 1 then¶Range(BF24).select¶selection.copy¶range(b9).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 23"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH25).select¶if selection.value = 1 then¶Range(BF25).select¶selection.copy¶range(b9).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 24"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH26).select¶if selection.value = 1 then¶Range(BF26).select¶selection.copy¶range(B10).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 25"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH27).select¶if selection.value = 1 then¶Range(BF27).select¶selection.copy¶range(b10).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 26"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CH28).select¶if selection.value = 1 then¶Range(BF28).select¶selection.copy¶range(b10).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 27"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CI2).select¶if selection.value = 1 then¶Range(BG2).select¶selection.copy¶range(b2).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 28"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CI3).select¶if selection.value = 1 then¶Range(BG3).select¶selection.copy¶range(b2).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 29"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CI4).select¶if selection.value = 1 then¶Range(BG4).select¶selection.copy¶range(b2).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 30"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CI5).select¶if selection.value = 1 then¶Range(BG5).select¶selection.copy¶range(b3).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 31"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CI6).select¶if selection.value = 1 then¶Range(BG6).select¶selection.copy¶range(b3).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 32"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CI7).select¶if selection.value = 1 then¶Range(BG7).select¶selection.copy¶range(b3).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 33"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CI8).select¶if selection.value = 1 then¶Range(BG8).select¶selection.copy¶range(b4).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 34"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CI9).select¶if selection.value = 1 then¶Range(BG9).select¶selection.copy¶range(b4).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 35"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CI10).select¶if selection.value = 1 then¶Range(BG10).select¶selection.copy¶range(b4).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 36"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CI11).select¶if selection.value = 1 then¶Range(BG11).select¶selection.copy¶range(b5).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 37"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CI12).select¶if selection.value = 1 then¶Range(BG12).select¶selection.copy¶range(b5).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 38"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CI13).select¶if selection.value = 1 then¶Range(BG13).select¶selection.copy¶range(b5).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 39"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CI14).select¶if selection.value = 1 then¶Range(BG14).select¶selection.copy¶range(b6).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 40"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CI15).select¶if selection.value = 1 then¶Range(BG15).select¶selection.copy¶range(b6).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 41"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CI16).select¶if selection.value = 1 then¶Range(BG16).select¶selection.copy¶range(b6).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 42"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CI17).select¶if selection.value = 1 then¶Range(BG17).select¶selection.copy¶range(b7).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 43"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CI18).select¶if selection.value = 1 then¶Range(BG18).select¶selection.copy¶range(b7).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 44"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CI19).select¶if selection.value = 1 then¶Range(BG19).select¶selection.copy¶range(b7).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 45"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CI20).select¶if selection.value = 1 then¶Range(BG20).select¶selection.copy¶range(b8).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 46; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 121.5pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=162>Range(CI21).select¶if selection.value = 1 then¶Range(BG21).select¶selection.copy¶range(b8).select¶Activesheet.Paste¶Cutcopypastemode = False¶end if...<o></o>
</TD></TR></TBODY></TABLE>

 

[dukeofscouts]

That Sounds like an Idea I'll get started on that

 

[derekpegg]

Definitely looks like you could do with some sort of loop procedure(s) for that lot. Probably could be done in 3 or 4 lines of code. Hopefully someone with better VBA abilities than me can help you out.

 

[dukeofscouts]

Well the great part is now the macro runs all the way. The bad part is that I've got errors in it somewhere. It is copy cells it souldn't copy. I'm going to give this another go. That said, I'd love to learn how to write a loop for this. I started this to push myself to learn new tricks, I guess looping macros is the next one. Is there a podcast or youtube, or any webpage for that matter, that explains how to do this.

 

[derekpegg]

Try:

http://www.exceltip.com/st/Using_Loops_in_VBA_in_Microsoft_Excel/628.html

http://www.ozgrid.com/VBA/VBALoops.htm

or just Google "Excel VBA Loops"

 

[njimack]

Based on your quasi-code, the following should suffice, although I'm not sure it does what you've explained in your opening post...

Sub Test()

Dim n As Long, i As Long

For n = 2 To 28
    i = WorksheetFunction.Round((n / 3) + 1, 0)
    If Cells(n, 86) = 1 Then Cells(n, 58).Copy Destination:=Cells(n, i)
Next n

For n = 2 To 28
    i = WorksheetFunction.Round((n / 3) + 1, 0)
    If Cells(n, 87) = 1 Then Cells(n, 59).Copy Destination:=Cells(n, i)
Next n

End Sub

 

[al_b_cnu]

Hi,

did you want a Sudoku Solver, something like this which reads the data from the named range 'Original', analyses each row, column & box & outputs the results into the named range 'Calculated':

Option Explicit

Sub SudokuSolver()
Dim bChanged As Boolean, bChangedIteration As Boolean
Dim iColPtr As Integer, iRowPtr As Integer, iBoxPtr As Integer
Dim iStartRow As Integer, iStartCol As Integer
Dim iCurPtr As Integer
Dim sCur As String
Dim vaGrid() As Variant
Dim vaCurrent() As Variant

vaGrid = Range("Original").Resize(9, 9).Value
ReDim vaCurrent(1 To UBound(vaGrid, 1))

'-- Initially populate working array --
For iRowPtr = 1 To UBound(vaGrid, 1)
    For iColPtr = 1 To UBound(vaGrid, 2)
        sCur = vaGrid(iRowPtr, iColPtr)
        If IsNumeric(sCur) = False Then sCur = "123456789"
        vaGrid(iRowPtr, iColPtr) = sCur
    Next iColPtr
Next iRowPtr

Do
    bChangedIteration = False
    
    '-------------------
    '-- Check Columns --
    '-------------------
    bChanged = False
    For iColPtr = 1 To UBound(vaGrid, 2)
        For iRowPtr = 1 To UBound(vaGrid, 1)
            vaCurrent(iRowPtr) = vaGrid(iRowPtr, iColPtr)
        Next iRowPtr
        For iRowPtr = 1 To UBound(vaGrid, 1)
            bChanged = bChanged Or CheckData(Data:=vaCurrent)
        Next iRowPtr
        If bChanged Then
            For iRowPtr = 1 To UBound(vaGrid, 1)
                vaGrid(iRowPtr, iColPtr) = vaCurrent(iRowPtr)
            Next iRowPtr
        End If
    Next iColPtr
    bChangedIteration = bChangedIteration Or bChanged
    
    '----------------
    '-- Check Rows --
    '----------------
    bChanged = False
    For iRowPtr = 1 To UBound(vaGrid, 1)
        For iColPtr = 1 To UBound(vaGrid, 2)
            vaCurrent(iColPtr) = vaGrid(iRowPtr, iColPtr)
        Next iColPtr
        For iColPtr = 1 To UBound(vaGrid, 2)
            bChanged = bChanged Or CheckData(Data:=vaCurrent)
        Next iColPtr
        If bChanged Then
            For iColPtr = 1 To UBound(vaGrid, 2)
                vaGrid(iRowPtr, iColPtr) = vaCurrent(iColPtr)
            Next iColPtr
        End If
    Next iRowPtr
    bChangedIteration = bChangedIteration Or bChanged
    
    '-----------------
    '-- Check Boxes --
    '-----------------
    bChanged = False

    For iBoxPtr = 0 To UBound(vaCurrent) - 1
        iStartRow = (Int(iBoxPtr / 3) * 3) + 1      '=(INT(A1/3)*3)+1
        iStartCol = ((iBoxPtr Mod 3) * 3) + 1       '=(MOD(A1,3)*3)+1
        iCurPtr = 0
        
        For iRowPtr = iStartRow To iStartRow + 2
            For iColPtr = iStartCol To iStartCol + 2
                iCurPtr = iCurPtr + 1
                vaCurrent(iCurPtr) = vaGrid(iRowPtr, iColPtr)
            Next iColPtr
        Next iRowPtr
        bChanged = bChanged Or CheckData(Data:=vaCurrent)
    
        If bChanged Then
            iCurPtr = 0
            For iRowPtr = iStartRow To iStartRow + 2
                For iColPtr = iStartCol To iStartCol + 2
                    iCurPtr = iCurPtr + 1
                    vaGrid(iRowPtr, iColPtr) = vaCurrent(iCurPtr)
                Next iColPtr
            Next iRowPtr
        End If
        bChangedIteration = bChangedIteration Or bChanged
    Next iBoxPtr
    
Loop While bChangedIteration

Range("Calculated").Resize(9, 9).Value = vaGrid
End Sub

Private Function CheckData(ByRef Data() As Variant) As Boolean
Dim iPtr1 As Integer, iPtr2 As Integer
Dim sCur As String

CheckData = False
For iPtr1 = 1 To UBound(Data)
    sCur = CStr(Data(iPtr1))
    If Len(CStr(Data(iPtr1))) = 1 Then
        For iPtr2 = 1 To UBound(Data)
            If iPtr1 <> iPtr2 Then
                If InStr(CStr(Data(iPtr2)), sCur) <> 0 Then
                    Data(iPtr2) = Replace(CStr(Data(iPtr2)), sCur, "")
                    CheckData = True
                End If
            End If
        Next iPtr2
    End If
Next iPtr1

End Function

 

[dukeofscouts]

Hi,

did you want a Sudoku Solver, something like this which reads the data from the named range 'Original', analyses each row, column & box & outputs the results into the named range 'Calculated':

That's really cool. Not going to lie, I don't understand over half the code, but very cool. That said, if I follow correctly what I do understand this is way better than what I've got now. That will be my next challange then. Thanks for Sharing.