"Select method of Range class failed" error messag

[lovallee]

Hello!

I copied my code hereunder.

When I run it, Visual Basic stops at the "b.select" line and give me the following error message:

"Select method of Range class failed"

I just can't understand why...anyone can help?

Thanks

Louis

Sub ActivatePeriod()

Dim c As Range
Dim b As Range
    
PeriodOpeningDate = Evaluate("DATE(YEAR(NAVDate),MONTH(NAVDate),1)")
PeriodEndingDate = Range("NAVDate").Value

For Each c In Range("FSLockLookUpRange")
    If IsDate(c.Value) Then
        If c.Value >= PeriodOpeningDate And c.Value <= PeriodEndingDate Then
            For Each b In c.EntireRow
                If b.Interior.ColorIndex = xlNone Then
                    b.Select
                    Selection.Locked = False
                    Else
                    b.Select
                    Selection.Locked = True
                End If
            Next
        End If
        If c.Value > PeriodEndingDate Then
            c.EntireRow.Hidden = True
            Else
            c.EntireRow.Hidden = False
        End If
    Else
    End If
Next

End sub

 

[TommyGun]

Re: "Select method of Range class failed" error me

This is because your worksheet is protected. You cannot select/change a locked cell on a protected sheet. You will need to unprotect your worksheet at the beginning of your code, and then reprotect it after it finishes.

 

[lovallee]

Re: "Select method of Range class failed" error me

I should have opied the whole code...
In fact, my code also include the following at the beginning...


For i = 2 To 4
Sheets(i).Unprotect
Next

So sheet protection is not the source of the error...[/code]

 

[TommyGun]

Re: "Select method of Range class failed" error me

Well...then...try removing the SELECT method...

b.Locked = False

Also...do you have merged cells within this range?

 

[lovallee]

Re: "Select method of Range class failed" error me

This is exactly what I first tried...

using b.Locked = false I get no error message but then each cell on c.entirerow is unlocked.

It seems that the Visual Basic refuse to accept two range variable embedded in a for each loop...

 

[TommyGun]

Re: "Select method of Range class failed" error me

Well...from your first code...if the ColorIndex of the cell is empty, then the cell is not locked...so your row must contain cells that have no interior color.

Here is a "cleaned up" version of your code. This is not tested, but should work...

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ActivatePeriod()
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range, b <SPAN style="color:#00007F">As</SPAN> Range
        
    PeriodOpeningDate = Evaluate("DATE(YEAR(NAVDate),MONTH(NAVDate),1)")
    PeriodEndingDate = Range("NAVDate").Value
    
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range("FSLockLookUpRange")
        <SPAN style="color:#00007F">If</SPAN> IsDate(c.Value) <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">If</SPAN> c.Value >= PeriodOpeningDate And c.Value <= PeriodEndingDate <SPAN style="color:#00007F">Then</SPAN>
                <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> b <SPAN style="color:#00007F">In</SPAN> c.EntireRow.Cells
                    b.Locked = <SPAN style="color:#00007F">Not</SPAN> (b.Interior.ColorIndex = xlNone)
                <SPAN style="color:#00007F">Next</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
            
            c.EntireRow.Hidden = (c.Value > PeriodEndingDate)
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[lovallee]

Re: "Select method of Range class failed" error me

It works perfectly!

Thanks!

 

[y481184]

Select Method of Range Class Failed

I saw the following post in your message board. I am having the same problem, although I can seem to resolve it with the methods that you have proposed so far. Below, you will find the code that is giving me the problem. I am receiving the error message when I hit "Worksheets("Report").Columns(14).Select"

I have no merged cells, and my worksheet is not protected. Do you have any further suggestions?

Worksheets("Report").Columns(4).NumberFormat = "###,##0.00"
Worksheets("Report").Columns(5).NumberFormat = "###,###,###,##0"
Worksheets("Report").Columns(6).NumberFormat = "###,###,###,##0"
Worksheets("Report").Columns(7).NumberFormat = "###,###,###,##0"
Worksheets("Report").Columns(8).NumberFormat = "###,###,###,##0"
Worksheets("Report").Columns(9).NumberFormat = "###,##0.00"
Worksheets("Report").Columns(10).NumberFormat = "##0.000"
Worksheets("Report").Columns(11).NumberFormat = "###,###,###,##0"
Worksheets("Report").Columns(12).NumberFormat = "###,###,###,##0"
Worksheets("Report").Columns(13).NumberFormat = "###,###,###,##0"
Worksheets("Report").Columns(14).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With

 

[biswarup88]

I have written this code to delete row from Excel database but when i am running this i get the above error. Please help to to solve this.
Here is my code:




if (MessageBox.Show("Do you realy want to delete this data?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{ if (index != -1)
{ ex.Application xlApp; ex.Workbook xlWorkBook; ex.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value;
xlApp = new ex.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Open(Path.GetDirectoryName(Application.ExecutablePath) + "\\Taas SMS.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
if (TaasSMS.AppCode.GenModule.Type == "Member") { xlWorkSheet = (ex.Worksheet)xlWorkBook.Worksheets.get_Item(2);
ex.Range range; range = (ex.Range)xlWorkSheet.Rows[index + 2, Type.Missing]; range.Select(); range.Delete(ex.XlDirection.xlUp); xlWorkBook.SaveAs(Path.GetDirectoryName(Application.ExecutablePath) + "\\Temp\\Taas SMS.xls", ex.XlFileFormat.xlExcel8, Type.Missing, Type.Missing, Type.Missing, Type.Missing, ex.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); }
else if (TaasSMS.AppCode.GenModule.Type == "Media")
{ xlWorkSheet = (ex.Worksheet)xlWorkBook.Worksheets.get_Item(3);
ex.Range range;
range = (ex.Range)xlWorkSheet.Rows[index + 2, Type.Missing];
range.Select();
range.Delete(ex.XlDirection.xlUp);
xlWorkBook.SaveAs(Path.GetDirectoryName(Application.ExecutablePath) + "\\Temp\\Taas SMS.xls", ex.XlFileFormat.xlExcel8, Type.Missing, Type.Missing, Type.Missing, Type.Missing, ex.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlWorkBook.Close(true, misValue, misValue); xlApp.Quit();
releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); } index = -1; string sourcePath2 = Path.GetDirectoryName(Application.ExecutablePath) + "\\Temp\\Taas SMS.xls"; string DestinationPath2 = Path.GetDirectoryName(Application.ExecutablePath) + "\\Taas SMS.xls"; File.Delete(DestinationPath2); File.Move(sourcePath2, DestinationPath2); FillGrid();
MessageBox.Show("Data Deleted!..."); }


at range.Select();
i get that above error: COM exception was unhandled (Select method of Range class failed)