Selecting Active Cell based on variable column offset values (HLOOKUP?)

[JWoodOz]

Hi,

I have a formula that I need to copy from a certain cell ("Cell A") to the cell immediately to the right on the same row ("Cell "B").

These cells change each week as I roll forward a cash flow forecast. So for the 2nd week Cell B becomes Cell A and needs to be copied directly to the right again.

I want to select the appropriate Cell A each week based on a static row but the moving column. The columns represent the week of the year for the cash flow.

The date I want to lookup against is the current week, always present in cell H19 of the worksheet. For example, Cell H19 might contain the value "9/5/2020"

The dates I want to match against are in row 27. With 9/5/2020 being in cell AQ27.

In my code I set the active cell as F20 to start with as a base. The number of columns to the right are mapped out on row 20 of the worksheet (grouped rows, not shown on output). For example, cell AQ20 contains the hardcoded value "38". Being 38 columns from column F.

My code is below (with row 81 being the correct row, shown as 61 in row offset):

ActiveCell.Offset(61, (Application.WorksheetFunction.Index(Range("R20:DH20"), WorksheetFunction.Match(Cells(H19).Value, Range("R27:DH27"), 0)))).Range("A1").Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:B1"), Type:= _
xlFillDefault

I am receiving run time error 1004: Application-defined or object defined error

Any assistance would be hugely appreciated!

Thanks,

Jeff

 

[Oaktree]

Welcome to the board!

I wouldn't recommend using ActiveCell.Offset or hardcoding the ranges that way. If you happen to have a different cell selected or insert/delete rows or columns, your model will go haywire, and it will be difficult to revert to the pre-macro changes.

Instead, I'd do this:

1) Set up named range UpdateRow to be cell R81 (i.e. the leftmost cell in the range you're updating). If you're not familiar with named ranges, just select cell R81 and type UpdateRow into the name box to the left of the formula bar.
2) In any unused cell (say, A1) enter the formula =MATCH(H19,R27:DH27,0). Then, set up named range OffsetColumn to be cell A1 using the same process as (1) above
3) Use code like this:

Range(Cells(Range("UpdateRow").Row, Range("UpdateRow").Column + Range("OffsetColumn").Value - 1), _
           Cells(Range("UpdateRow").Row, Range("UpdateRow").Column + Range("OffsetColumn").Value - 2)).FillRight

If you have more than one row to update, consider changing step 1 above to be "FirstUpdateRow" and repeating the same step for the last cell in column R that you're updating, naming that "LastUpdateRow" as well.

Range(Cells(Range("FirstUpdateRow").Row, Range("FirstUpdateRow").Column + Range("OffsetColumn").Value - 1), _
           Cells(Range("LastUpdateRow").Row, Range("LastUpdateRow").Column + Range("OffsetColumn").Value - 2)).FillRight

 

[JWoodOz]

Life saver! Thanks very much.

Not to push my luck, but if I wanted to use the same approach to locate a range of cells in the current week's column, (e.g. AQ88:AQ91,) and copy those cells to another worksheet, is there a similar approach to locate and select a range?

 

[Oaktree]

I'm not sure what you mean by "locate a range of cells in the current week's column".

What value are you looking for, which range are you trying to find that value in, and which range do you want to copy if you find it?

 

[JWoodOz]

The method you provided above successfully located the column that shifts each week based on the date in H19.

I want to stay in that same column but instead of finding a single cell in that column and filling it one cell to the right, I want to find 4 rows (sequential, rows 88:91) in that same column, select and copy them to another worksheet.

 

[Oaktree]

The basic construct is Sheet("SourceSheetName").Range(...).copy Sheet("DestinationSheetName").Range(...)

Building on the 2nd example from my first post, try something like:

Range(Cells(Range("FirstUpdateRow").Row, Range("FirstUpdateRow").Column + Range("OffsetColumn").Value - 1), _
      Cells(Range("LastUpdateRow").Row, Range("LastUpdateRow").Column + Range("OffsetColumn").Value - 1)).Copy _
Sheets("Other Sheet Name").Range("B1")