PDA

View Full Version : [SOLVED] Dim problem



nikki333
01-27-2018, 03:22 PM
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
01-27-2018, 03:31 PM
Works: Range("B6:B" & lrMatrix)
Fails: Range(Cells(3, 1), Cells(3, lcMatrix))

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

yujin
01-27-2018, 04:36 PM
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
01-27-2018, 06:57 PM
Try



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


Cells() without a dot defaults to the activesheet

nikki333
01-28-2018, 04:11 AM
Many thanks for the quick reply...that works now :)