Code stops unexpectedly (Excel 2007)

jmitchells5w

New Member
Joined
Jun 14, 2007
Messages
35
I have the following code in a module which basically changes the format of a selection of cells to General, inserts formulas, and then changes the format of the cells to Text. When I use F8 to step through the macro is executes Selection.NumberFormat = "General" and then stops. If I remove the code to change the formats it steps all the way through but I want to change the formats before and after inserting the formulas. I am using Excel 2007. When I copy this code into Excel 2003 it works fine. What am I missing?

Sub ResetVendorNumbers()
'
' ResetVendorNumbers Macro
'
Range("C5:M5").Select
Selection.NumberFormat = "General"
Range("C5").Select
Range("C5").Formula = "=VLOOKUP($C$3,VendorNum,COLUMN(B2),FALSE)"
Range("D5").Formula = "=VLOOKUP($C$3,VendorNum,COLUMN(C2),FALSE)"
Range("E5").Formula = "=VLOOKUP($C$3,VendorNum,COLUMN(D2),FALSE)"
Range("F5").Formula = "=VLOOKUP($C$3,VendorNum,COLUMN(E2),FALSE)"
Range("G5").Formula = "=VLOOKUP($C$3,VendorNum,COLUMN(F2),FALSE)"
Range("H5").Formula = "=VLOOKUP($C$3,VendorNum,COLUMN(G2),FALSE)"
Range("I5").Formula = "=VLOOKUP($C$3,VendorNum,COLUMN(H2),FALSE)"
Range("J5").Formula = "=VLOOKUP($C$3,VendorNum,COLUMN(I2),FALSE)"
Range("K5").Formula = "=VLOOKUP($C$3,VendorNum,COLUMN(J2),FALSE)"
Range("L5").Formula = "=VLOOKUP($C$3,VendorNum,COLUMN(K2),FALSE)"
Range("M5").Formula = "=VLOOKUP($C$3,VendorNum,COLUMN(L2),FALSE)"
Range("C5:M5").Select
Selection.NumberFormat = "@"
Range("C5").Select

End Sub
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Derek Brown

Well-known Member
Joined
Dec 26, 2005
Messages
2,390
The code works OK for me using Excel 2007 - as long as the worksheet is not protected, in which case it does fail at that point - also on the other Selection.NumberFormat line.

Exactly the same happens for me in Excel 2003.

Are you getting an error? Or has it 'appeared' to stop because it is recalculating the workbook?
 

jmitchells5w

New Member
Joined
Jun 14, 2007
Messages
35
I think the problem may be with the conditional formatting I have on the cells that I am trying to work with. If I have a conditional format in place then the code stops as mentioned. If there is no conditional formatting then the code is executed just fine. I haven't tested it yet but I'm assuming if I clear the conditional formatting and then reapply it at the end of the code then it will solve my problem. I'm not sure why it does this but this is the only solution I've been able to come up with.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,451
Messages
5,528,827
Members
409,839
Latest member
akashsadhu
Top