Example: (refer to help34.zip)

On running
..Range(sRng(i)).Select

¡urunning error '1004':¡v
¡uClass Range ... Select ... method error¡v

The problem may arise from this:

"db_rsImport", "db_rsExport" and "db_rsStock" are DYNAMIC RANGES
defined by 'offset' function
(for the purpose that in the three data-table, *only unlock* those
cells with data and the row just below the last record) .

How to solve it?

File Attached: http://www.excelforum.com/attachment.php?postid=330171 (help34.zip)

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Dear No-name,

Range(sRng(i)).Select

is improper syntax. sRng(i) is a range object, so you could use

sRng(i).Select

or

Range(sRng(i).Address).Select

I didn't open your attachment (and won't) but if you need more help
than that, post your code as text only.

HTH,
Bernie
MS Excel MVP

"active_x" wrote in message
...
Example: (refer to help34.zip)

On running
Range(sRng(i)).Select

¡urunning error '1004':¡v
¡uClass Range ... Select ... method error¡v

The problem may arise from this:

"db_rsImport", "db_rsExport" and "db_rsStock" are DYNAMIC RANGES
defined by 'offset' function
(for the purpose that in the three data-table, *only unlock* those
cells with data and the row just below the last record) .

How to solve it?

File Attached:
http://www.excelforum.com/attachment.php?postid=330171 (help34.zip)

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/

Scripts:

Sub Macro4()
Dim sRng(1 To 10) As String, sSht(1 To 4) As String
Dim i As Integer

sSht(1) = "main"
sSht(2) = "rsImport"
sSht(3) = "rsExport"
sSht(4) = "rsStock"

sRng(1) = "input_main"
sRng(2) = "db_rsImport"
sRng(3) = "db_rsExport"
sRng(4) = "db_rsStock"

For i = 1 To 4
With Sheets(sSht(i))

'lock ALL cells in ALL sht
Cells.Locked = True
Cells.FormulaHidden = True

'unlock specific cells in main, rsImport, rsExport and rsStock
Range(sRng(i)).Select '**the error arises
from this line
Selection.Locked = False

End With 'End With Sheets(sSht(i))
Next i

End Sub



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Try changing

Range(sRng(i)).Select
Selection.Locked = False

to
..Range(sRng(i)).Locked = False
(There is no need to select the range first.)

Note the leading ., since you are using a with construct. This
assumes that nmaed range 1 is on named sheet 1, etc.

HTH,
Bernie
MS Excel MVP

"active_x" wrote in message
...
Scripts:

Sub Macro4()
Dim sRng(1 To 10) As String, sSht(1 To 4) As String
Dim i As Integer

sSht(1) = "main"
sSht(2) = "rsImport"
sSht(3) = "rsExport"
sSht(4) = "rsStock"

sRng(1) = "input_main"
sRng(2) = "db_rsImport"
sRng(3) = "db_rsExport"
sRng(4) = "db_rsStock"

For i = 1 To 4
With Sheets(sSht(i))

'lock ALL cells in ALL sht
Cells.Locked = True
Cells.FormulaHidden = True

'unlock specific cells in main, rsImport, rsExport and rsStock
Range(sRng(i)).Select '**the error arises
from this line
Selection.Locked = False

End With 'End With Sheets(sSht(i))
Next i

End Sub



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/

Note that you should also change

Cells.Locked = True
Cells.FormulaHidden = True
to
..Cells.Locked = True
..Cells.FormulaHidden = True

Otherwise, you are simply acting on the activesheet, and not the cells
of the 'withed' sheet.

HTH,
Bernie
MS Excel MVP

"Bernie Deitrick" wrote in message
...
Try changing

Range(sRng(i)).Select
Selection.Locked = False

to
.Range(sRng(i)).Locked = False
(There is no need to select the range first.)

Note the leading ., since you are using a with construct. This
assumes that nmaed range 1 is on named sheet 1, etc.

HTH,
Bernie
MS Excel MVP

"active_x" wrote in message
...
Scripts:

Sub Macro4()
Dim sRng(1 To 10) As String, sSht(1 To 4) As String
Dim i As Integer

sSht(1) = "main"
sSht(2) = "rsImport"
sSht(3) = "rsExport"
sSht(4) = "rsStock"

sRng(1) = "input_main"
sRng(2) = "db_rsImport"
sRng(3) = "db_rsExport"
sRng(4) = "db_rsStock"

For i = 1 To 4
With Sheets(sSht(i))

'lock ALL cells in ALL sht
Cells.Locked = True
Cells.FormulaHidden = True

'unlock specific cells in main, rsImport, rsExport and rsStock
Range(sRng(i)).Select '**the error arises
from this line
Selection.Locked = False

End With 'End With Sheets(sSht(i))
Next i

End Sub



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/