CODEWITHSUNDEEP
vbaexcel
Tree
Tree

Reputation: 5

Referencing worksheets to draw data

I am writing a vba macro to allow me to reference data from a worksheet and summarize some of the data rather than using a ton of formulas to do so.

I am having difficulties in referencing worksheets and have reverted to activating sheets. I'm not sure what I am doing incorrectly. For example: 

     Sheets("Rainfall").Activate
     Set x = Range(Range("C2"), Range("C2").End(xlDown))

rather than

     Set x = Sheets("Rainfall").Range(Range("C2"), Range("C2").End(xlDown))

When I attempt to reference code such as 

     Cells(2 + j, 3) = Application.WorksheetFunction.VLookup(Cells(2 + j, 2), Worksheets("Raw Data").Range(Range("C4"), Range("H4").End(xlDown)), 6, False)

I get a 1004 error. Below is my code and if anyone has any suggestions on the simplification of the code that would be great as well.

  Sub selectall()
  Dim x, y As Range
  Dim nv, rd As Long
  Set Wkb = Workbooks("DWH Calculations V1.xlsm")
  Sheets("Rainfall").Activate
  Set x = Range(Range("C2"), Range("C2").End(xlDown))
  nv = x.Rows.Count
  'MsgBox (nv)
  Sheets("Raw Data").Activate
  Set y = Range(Range("E4"), Range("E4").End(xlDown))
  rd = y.Rows.Count
  'MsgBox (rd)

  MinD = Round(Application.WorksheetFunction.Min(y), 0)
  MaxD = Round(Application.WorksheetFunction.Max(y), 0)
  Ndays = MaxD - MinD
  'MsgBox (Ndays)

  Sheets("Rainfall").Activate
  Cells(2, 2) = MinD

  For j = 1 To Ndays - 1
  Cells(2 + j, 2) = Cells(1 + j, 2) + 1
  Cells(2 + j, 3) = Application.WorksheetFunction.VLookup(Cells(2 + j, 2),   Worksheets("Raw Data").Range(Range("C4"), Range("H4").End(xlDown)), 6, False)
  Next j

  End Sub

Thank you all for your help

Upvotes: 0

Views: 51

Answers (2)

Rory
Rory

Reputation: 34065

This has been asked many times before - you need to qualify all the Range calls with a worksheet object, so:

Set x = Sheets("Rainfall").Range(Sheets("Rainfall").Range("C2"), Sheets("Rainfall").Range("C2").End(xlDown))

or use a With...End With block:

With Sheets("Rainfall")
Set x = .Range(.Range("C2"), .Range("C2").End(xlDown))
End With

and note the periods before all three Range calls. You can also use a Worksheet variable:

Dim ws as Worksheet
Set ws = Sheets("Rainfall")
Set x = ws.Range(ws.Range("C2"), ws.Range("C2").End(xlDown))

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96763

The problem is the range-within-range:

replace:

Set x = Range(Range("C2"), Range("C2").End(xlDown))

with:

With Sheets("Rainfall")
        Set x = .Range(.Range("C2"), .Range("C2").End(xlDown))
End With

Activate is not needed to Set ranges.

Upvotes: 0

Related Questions