Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection.ClearContents makes macro halt???
What would make this macro stop after "Selection.ClearContents" It is
random. It makes it to the end most of the time. Very unreliable. excel97 Sub ModuleImport() Dim FileNameWithPath As Variant Dim FNum As Variant Dim SeparatorSymbol As String Dim RowNdx As Long Dim ColNdx As Integer Dim TempVal As Variant Dim WholeLine As String Dim Pos As Integer Dim NextPos As Integer Dim SaveColNdx As Integer SeparatorSymbol = "!" 'On Error Resume Next Sheets("DataModule").Select SaveColNdx = 1 RowNdx = 3 ' Get File Name <<<<<<<<<<<<<<<<<<<< FileNameWithPath = Application.GetOpenFilename(FileFilter:="Module Files (*.dme),*.dme") If FileNameWithPath = False Then Exit Sub End If ' Clear old Data <<<<<<<<<<<<<<<<<<<< Range("A3:N300").Select Selection.ClearContents Selection.NumberFormat = "@" Range("A1").Select msgbox "Made It" end sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection.ClearContents makes macro halt???
I assume that you have left some of the code out of the post.
I have no idea why it is not working. However, it is almost never necessary to select ranges to manipulate data. You can simply refererence the range. Try the following and see if it makes a difference. Replace the following code Range("A3:N300").Select Selection.ClearContents Selection.NumberFormat = "@" with this code Range("A3:N300").ClearContents Range("A3:N300").NumberFormat = "@" Note that your code is applied to the currently active sheet. -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection.ClearContents makes macro halt???
Range("A3:N300").NumberFormat = "@"
gives "NumberFormat method of range class failed" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection.ClearContents makes macro halt???
The code would fail if the selected sheet in the workbook wasn't a worksheet. A workbook contains charts and pictures which doesn't have a range of cells. The selected sheet in the open workbook could be protected. There are userform sheets in workbooks that may not have 300 rows or columns out to column N. I agree with Ossi and also recommend alway specifying the sheet name besides avoiding the selection method Set sourcesht = sheets("sheet1") With Sourcesht LastRow = .Range("A" & Rows.count).End(Xlup).row LastCol = .Cells(1,Columns.count).End(Xltoleft).column if LastRow < 3 then msgbox("There is not Data on Worksheet - Exiting Macro ") exit sub end if Set DataRange = .Range(.range("A3"),cells(LastRow,LastCol) DataRange.ClearContents DataRange.NumberFormat = "@" end with -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=179040 Microsoft Office Help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection.ClearContents makes macro halt???
In my previous post I indicated that you had left out some of the code in
your post. Is it running from a Userform? There are some conditions when code runs from a Userform if it comes across an error then the code appears to simply abort instead of stopping on the error. My thoughts are that for some reason your original problem was because the range could not be selected. In my previous answer I should have been more specific and said to include the sheet name in the code instead of just saying it will run on the active sheet. The sheet name is included with both of the following examples although the latter is the more professional. Replace "Sheet1" with your sheet name. Sheets("Sheet1").Range("A3:N300").ClearContents Sheets("Sheet1").Range("A3:N300").NumberFormat = "@" or 'Note the dot in from of Range which ties it to Sheets("Sheet1") With Sheets("Sheet1") .Range("A3:N300").ClearContents .Range("A3:N300").NumberFormat = "@" End With Using the above methods neither the sheet nor the range need to be selected. -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro halt - Why? | Excel Programming | |||
Selection.ClearContents for data only | Excel Programming | |||
Grouping selection in Pivot table makes all data disappear | Excel Discussion (Misc queries) | |||
Macro Makes List Box Selection | Excel Discussion (Misc queries) | |||
Code to halt a macro | Excel Programming |