Cells property - Run-time error 91

Question

So, I am trying to create sub procedure, where one of the arguments is column name.

Column name is then converted to column number.

Dim ColNum As Integer: ColNum = Range(ColumnNM & 1).Column

But when I try to use column number in range, with cell property I get 91 run-time error. Most of the solutions here are for not properly setting the range.

Run-time error 91

This here gives me that error :

Dim rngTA As Range: Set rngTA = wsTA.Range(Cells(24, ColNum))

and this (experimented, not sure if this is proper way to set range with string address)

Dim addr As String: addr = Cells(24, ColNum).Address(RowAbsolute:=False, ColumnAbsolute:=False)
Dim rngTA As Range: Set rngTA = wsTA.Range(addr)

I am stuck there.

Thanks.

Try `Set rngTA = wsTA.Range(wsTA.Cells(24, ColNum),wsTA.Cells(24, ColNum))` — Shai Rado, Nov 21 '17 at 14:23
@Shai RadoI don't know what is going on, when I create new sub, with only essentials your line is working,but in original code debugger stopped on setting rngTA range, I have to recheck whole code. Sorry, and thanks. — Jovica, Nov 21 '17 at 14:33

score 3 · Accepted Answer · answered Nov 21 '17 at 14:23

3

You do not need the Range. Cells() is a range object:

Dim rngTA As Range: Set rngTA = wsTA.Cells(24, ColNum)

When using range objects in a Range it needs to have two a beginning an an end. Since you only want one, just refer to that directly.

answered Nov 21 '17 at 14:23

Scott Craner

148,073
10
49
81

score 2 · Answer 2 · answered Nov 21 '17 at 14:25

2

You do not need a module, you can just use match, nevertheless, here is a function:

Function Letter(oSheet As Worksheet, name As String, Optional num As Integer)
If num = 0 Then num = 1
Letter = Application.Match(name, oSheet.Rows(num), 0)
'Letter = Split(Cells(, Letter).Address, "$")(1)
End Function

Name here is the name of your column header, oSheet is the name of the sheet, there is an optional variable that can be used as a number if the row you are searching is not 1. If you remove the quotation you can get the letter, rather than the number.

answered Nov 21 '17 at 14:25

Lowpar

897
10
31

2

This is the slowest method to get the job done. Excel works with numbers and has to parse any string to a numerical coordinates. By changing the column to a string, Excel then needs to parse it back to a number. – Scott Craner Nov 21 '17 at 14:26
2

See: https://stackoverflow.com/questions/36073943/range-vs-cells-run-times – Scott Craner Nov 21 '17 at 14:31

Cells property - Run-time error 91

2 Answers2