CODEWITHSUNDEEP
excelvba
andvch
andvch

Reputation: 21

Method 'range' of object'_worksheet failed

I have this Code in VBA, i just followed a tutorial about the code same code but the results is always showing "Method 'range' of object'_worksheet failed" what is wrong with this code tho?

KktkRow = .Range("B5").Value

    For KktkCol = 1 To 39
    .Range(Sheet4.Cells(1, KktkCol).Value).Value = Sheet4.Cells(KktkRow, KktkCol).Value
    Next KktkCol

Upvotes: 0

Views: 221

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

.Range(Sheet4.Cells(1, KktkCol).Value).Value = Sheet4.Cells(KktkRow, KktkCol).Value

There's a With block somewhere that isn't for Sheet4. Remove the Sheet4 qualifier and leave the . dot operator to make it look like this (assuming the With block isn't for Sheet4 but the source values are on that sheet):

.Range(.Cells(1, KktkCol).Value).Value = Sheet4.Cells(KktkRow, KktkCol).Value

That way the .Cells member calls will consistently be made against the same worksheet that's qualifying this .Range; whenever you invoke .Range with inner .Cells, make sure the inner .Cells are on the same worksheet as the outer .Range.

This is very similar to a common issue caused by implicit ActiveSheet references, when the inner .Cells calls aren't qualified at all:

.Range(Cells(1, KktkCol).Value).Value = .Cells(KktkRow, KktkCol).Value
       ^^^^^ if the ActiveSheet isn't the same as the parent of .Range ~> error 1004

If the With block isn't for Sheet4 but the target cell is, then you need to qualify the .Range with it:

Sheet4.Range(Sheet4.Cells(1, KktkCol).Value).Value = Sheet4.Cells(KktkRow, KktkCol).Value

If the With block is for Sheet4, then we're missing context. As John Coleman pointed out, Sheet4.Cells(KktkRow, KktkCol) looks suspicious (are you reading the row number from a cell?).

Upvotes: 1

Related Questions