When I run the code I get a VBA 400 error

J

Joe O

New Member
Joined
Sep 5, 2009
Messages
25
The code performs the match and selects the data on sheet1. The msgbox I inserted shows the correct column on sheet2, then I get the 400 error. Is the problem in the Cells(2, i).Select statement. I am stumped.

Any thoughts?


Sub temp()
Dim i As Integer
i = 1
Do While Not (Sheets("sheet1").Cells(1, 1).Value = Sheets("sheet2").Cells(1, i))
i = i + 1
Loop ' find the column number of the matching date
Sheets("sheet1").Select
Range("A2:A36").Select
Selection.Copy
MsgBox i
Sheets("sheet2").Select
Cells(2, i).Select
ActiveSheet.Paste <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
I have created the sheets like this:

Sheet1 A! through A36 contains:<o:p></o:p>
<TABLE class=MsoNormalTable style="WIDTH: 67pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 0in 0in 0in" cellSpacing=0 cellPadding=0 width=89 border=0><TBODY><TR style="HEIGHT: 15pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 67pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .5pt" width=89>
9/24/2009<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">
1-Jan-00<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">
2<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 3"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">
3<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 4"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">
4-Jan-00<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 5"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">
5-Jan-00<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 6"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">qq<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 7"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">rr<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 8"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">ss<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 9"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">qq<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 10"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">rr<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 11"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">ss<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 12"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">qq<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 13"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">rr<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 14"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">ss<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 15"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">qq<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 16"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">rr<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 17"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">ss<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 18"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 19"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">qq<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 20"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">rr<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 21"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">ss<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 22"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">qq<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 23"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">rr<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 24"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">ss<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 25"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">ss<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 26"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">qq<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 27"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">rr<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 28"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">ss<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 29"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">qq<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 30"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">rr<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 31"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">ss<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 32"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">qq<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 33"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">rr<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 34"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">ss<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 35; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">qq<o:p></o:p>
</TD></TR></TBODY></TABLE>


And sheet 2 row 1 contains:<o:p></o:p>
<TABLE class=MsoNormalTable style="WIDTH: 511pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 0in 0in 0in" cellSpacing=0 cellPadding=0 width=681 border=0><TBODY><TR style="HEIGHT: 15pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 73pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .5pt" width=97>
9/24/2009<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 73pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" width=97>
9/23/2009<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 73pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" width=97>
9/22/2009<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 73pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" width=97>
9/21/2009<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 73pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" width=97>
9/20/2009<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 73pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" width=97>
9/19/2009<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 73pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" width=97>
9/18/2009<o:p></o:p>​
</TD></TR></TBODY></TABLE>


When I run the code I get a VBA 400 error.


PS Thank you for helping. I am learning a great deal, just not fast enough. <o:p></o:p>
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
Try this instead. No need to select ranges in order to copy/paste.
Code: 
Sub temp()
Dim i As Long
i = 1
Do While Not (Sheets("sheet1").Cells(1, 1).Value = Sheets("sheet2").Cells(1, i))
    i = i + 1
Loop ' find the column number of the matching date
Sheets("sheet1").Range("A2:A36").Copy Sheets("sheet2").Cells(2, i)
End Sub
The problem is that you referenced "Cells(2, i).Select" and it was assuming you were still on Sheet1. You could probably just make it "Sheets("Sheet2").Cells(2, i).Select", but it's still a less efficient way of doing what you're trying to do.
 
Upvote 0
Thank you, I spent all day wrapping myself around the axel. What more I now understand what I was doing wrong. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Thank you.<o:p></o:p>
 
Upvote 0
Hi mvptomlinson
I am trying to adapt your code to my code and it almost works.
The problem is when my code goes to another Dictionary item I cannot figure out how to make your code update to the new (i)
Can you help me
Thanks Charlie
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
'// set reference to microsoft scripting runtime (tools > references)
Dim rngCELL As Range
Dim rngSEARCHAREA As Range
Dim dic As Scripting.Dictionary
Dim dicITEM As Variant
'Worksheets("TheHdr").Activate
'// change to fit your filter range (excluding column label)
Set rngSEARCHAREA = Worksheets("TheHdr").Range("d2:d30")

If rngSEARCHAREA Is Nothing Then GoTo Finish

Set dic = New Scripting.Dictionary
For Each rngCELL In rngSEARCHAREA
If Not dic.Exists(rngCELL.Value) And Not rngCELL.Value = vbNullString Then
dic.Add rngCELL.Value, rngCELL.Value
End If
Next

If Not dic Is Nothing Then
For Each dicITEM In dic.Items
With rngSEARCHAREA.Offset(-1).Resize(rngSEARCHAREA.Rows.count + 1)
.AutoFilter field:=1, Criteria1:=dicITEM
'// do whatever it is that you want to do _
in this example I'm copying each filtered table to sheet2 _
and to the next available blank row
'
' mvptomlinson mvptomlinson mvptomlinson mvptomlinson mvptomlinson mvptomlinson mvptomlinson mvptomlinson
i = 1
Do While Not (Sheets("TheHdr").Cells(2, 4).Value = Sheets("Hdrformula").Cells(1, i))
i = i + 1
Loop ' find the column number of the matching date
Sheets("TheHdr").Range("TheHdrLft").Copy Sheets("Hdrformula").Cells(2, i)
' mvptomlinson mvptomlinson mvptomlinson mvptomlinson mvptomlinson mvptomlinson mvptomlinson mvptomlinson
<o:p></o:p>
.AutoFilter field:=1
End With
Next dicITEM
End If
Finish:
Set rngSEARCHAREA = Nothing
Set dic = Nothing
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
342
VBAEXCELNew
V
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
865
gvsumich
G
E
  • Question
VBA for Web Scraping help
Replies
0
Views
624
epactheactor
E
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
365
esben
E

Forum statistics

Threads
1,216,116
Messages
6,128,933
Members
449,480
Latest member
yesitisasport

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top