0

The following code will result in a

Type Mismatch error

and will highlight the Set Cnt = App... line in yellow. I couldn't figure out what the problem is, I hope somebody can help me.

Dim Sel As Range

Set Sel = Selection

Sel.Offset(1, 2).Select

Range(Selection, Selection.End(xlDown)).Select

Dim Cnt As Variant
Set Cnt = Application.Selection.Cells.Count

Sel = Cnt.Value
braX
  • 11,506
  • 5
  • 20
  • 33
K. Robert
  • 101
  • 2
  • 12
  • 2
    `Count` returns a number, not an `Object` - So you don't use `Set` – braX Jan 16 '18 at 16:28
  • 2
    .Count is a number, not an object. Maybe remove Set and just Sel = Cnt. –  Jan 16 '18 at 16:29
  • 1
    One line of code to do this `Selection = Range(Selection.Offset(1,2),Selection.Offset(1,2).End(xlDown)).Cells.Count` :) ... [avoid select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Scott Holtzman Jan 16 '18 at 18:25
  • Thanks for all of the answers. @BraX: What should I use then? – K. Robert Jan 17 '18 at 10:54
  • @Jeeped If I just use Sel = Cnt and do not declare what Cnt is it won't work. – K. Robert Jan 17 '18 at 10:56
  • @Scott Holtzman Thanks, I have tried to avoid it but wasn't really bothered because the whole thing wasn't working anyway :). – K. Robert Jan 17 '18 at 10:56
  • Eventually I won't want to use it as a value for a cell, but to declare how many times a loop has to repeat the action. – K. Robert Jan 17 '18 at 11:24

0 Answers0