Define changing column range

[bujaman]

I am trying to define a range of cells that may change. I have a worksheet that imports data from another sheet, and the number of columns in one part may change depending on how many months of data are available. What I need to do is define the number of columns for use in the code. Here is what I have so far, but it isn't working. The code needs to look through the values in Range N1:BJ1 for a cell that starts with "CY", then pass the column value just to the left of this cell to a range. There are also other columns that need to be defined, but they are always 2 columns and 25 columns to the right of the cell starting with "CY." Any thoughts? and thanks in advance.

Dim LastMonthY1 As Double
Dim FirstMonthY2 As Double
Dim LastMonthY2 As Double

For Each Cell In Worksheets("Data").Range("N1:BJ1")

If Left(Cell.Value, 2) = "CY" Then

LastMonthY1 = ActiveCell.Offset(0, -1).Column
FirstMonthY2 = ActiveCell.Offset(0, 2).Column
LastMonthY2 = ActiveCell.Offset(0, 25).Column


End If

Next Cell


Worksheets("Data").Activate

Set allData = Worksheets("Data").Range("N3:" & LastMonthY1 & Range("A65536").End(xlUp).Row)
Set allData1 = Worksheets("Data").Range(FirstMonthY2 & "3:" & LastMonthY2 & Range("A65536").End(xlUp).Row)

 

[Marcelo Branco]

Hi,

The column property returns a number, not a letter, ie, 1 for column A, 2 for B, and so on.

So try something like this

With Worksheets("Data")
        Set alldata = .Range(.Cells(3, "N"), .Cells(.Range("A65536").End(xlUp).Row, LastMonthY1))
End With

HTH

M.