Dim problem

[nikki333]

Hi Forum

got a weird problem...my routine worked perfectly for quite a while, but suddenly it tells me that there's a problem with my dimensions...(the one marked up in red)
I just can't find a typo or something wrong with it...anybody see something wrong?

'Worksheets dim Dim wsTeilnehmer As Worksheet: Set wsTeilnehmer = A01
Dim wsMatrix As Worksheet: Set wsMatrix = A02
Dim wsHelper As Worksheet: Set wsHelper = A99

'Rows dim
Dim lrMatrix As Long: lrMatrix = wsMatrix.Cells(Rows.count, 2).End(xlUp).Row
Dim lrTeilnehmer As Long: lrTeilnehmer = wsTeilnehmer.Cells(Rows.count, 2).End(xlUp).Row
Dim lrHelperMatrix As Long: lrHelperMatrix = wsHelper.Cells(Rows.count, 1).End(xlUp).Row
Dim lrHelperMatrixFehler As Long: lrHelperMatrixFehler = wsHelper.Cells(Rows.count, 6).End(xlUp).Row

'Columns dim
Dim lcMatrix As Long: lcMatrix = wsMatrix.Cells(3, Columns.count).End(xlToLeft).Column

'Anzahl dim
Dim numMitarbeiter As Long: numMitarbeiter = lrMatrix - 5
Dim numTeilnehmer As Long: numTeilnehmer = lrTeilnehmer - 3
Dim numHelperMatrix As Long: numHelperMatrix = lrHelperMatrix - 1

'Ranges dim
Dim rngLuNrMatrix As Range: Set rngLuNrMatrix = wsMatrix.Range("B6:B" & lrMatrix)
Dim rngTrainingNrMatrix As Range: Set rngTrainingNrMatrix = wsMatrix.Range(Cells(3, 1), Cells(3, lcMatrix))
Dim rngLuNrTeilnehmer As Range: Set rngLuNrTeilnehmer = wsTeilnehmer.Range("N4:N" & lrTeilnehmer)
Dim rngTraining As Range: Set rngTraining = wsTeilnehmer.Range("K4:K" & lrTeilnehmer)
Dim rngDatum As Range: Set rngDatum = wsTeilnehmer.Range("B4:B" & lrTeilnehmer)
Dim rngStufe As Range: Set rngStufe = wsTeilnehmer.Range("M4:M" & lrTeilnehmer)

[SamT]

Works: Range("B6:B" & lrMatrix)
Fails: Range(Cells(3, 1), Cells(3, lcMatrix))

What about Range("A3").Resize(1, lcMatrix)

[yujin]

Hello, nikki333.
You need to modify your code like this:

Set rngTrainingNrMatrix = wsMatrix.Range(wsMatrix.Cells(3, 1), wsMatrix.Cells(3, lcMatrix))

When you use worksheet specified Range, each Cells property also needs to be worksheet specified.

[Paul_Hossler]

Try

Set rngTrainingNrMatrix = Range(wsMatrix.Cells(3, 1), wsMatrix.Cells(3, lcMatrix))

Cells() without a dot defaults to the activesheet

[nikki333]

Many thanks for the quick reply...that works now