class range...select...(reserved for expert)

[active_x]

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)

 

[Bernie Deitrick]

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

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)

 

[active_x]

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

 

[Bernie Deitrick]

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

 

[Bernie Deitrick]

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

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