0

I am working with two worksheets named Sheet1 and Sheet2

Worksheets("SMSv2").Activate
Dim smsv2 As Range
Dim rng As Range
Dim arrayRange As Variant

Set smsv2 = ActiveSheet.Range("A2")
Set rng = Range(smsv2, smsv2.End(xlToRight))
Set rng = Range(rng, rng.End(xlDown))

arrayRange = Range(rng).Value

arrayRange = Range(rng).Value

'Go back to Sheet1
Worksheets("Sheet1").Activate

'Enter  index match
Range("N2").Select
ActiveCell.FormulaR1C1 = _
    "=INDEX(Sheet2!arrayRange,MATCH(Sheet1!RC[-11],Sheet2!arrayRange,0),4)"

How to include arrayRange correctly to the index function to get the value?

braX
  • 11,506
  • 5
  • 20
  • 33
E Vision
  • 23
  • 1
  • 8
  • 1
    You are mixing worksheet syntax with VBA variables. There are many examples on SO on how to concatenate a formula string correctly. In your case (if you want to go the array route), you'll need to `Join` the array first (and change your formula string). – JvdV Feb 03 '20 at 10:22
  • Also, [this](https://stackoverflow.com/q/10714251/9758194) would help you out also on avoiding `.Activate` and `.Select` methods. – JvdV Feb 03 '20 at 10:26
  • Please give me a brief description about the error point – E Vision Feb 03 '20 at 16:08
  • ActiveCell.FormulaR1C1 = _ "=INDEX(Sheet2!R2C1:R26768C11,MATCH(Sheet1!RC[-11],Sheet2!R2C1:R26768C1,0),4)" – E Vision Feb 03 '20 at 17:36
  • The above is the original code. I want to replace the Sheet2!R2C1:R26768C11 with the range of data. Since range differs in different months I need to attach a variable which takes the range. – E Vision Feb 03 '20 at 17:39

0 Answers0