Worksheet_change event problems

[jacko2401]

I have a workbook with 2 worksheets. Worksheet 1 has data entry table while worksheet 2 has 2 columns for a list of names and their departments

I have a worksheet change procedure in worksheet 1 that does 3 things:
1. Checks if the cell(s) changed are within a specifc range of that worksheet (This will be a dynamic range since rows of data are always added)
2. Compare the changed cell(s) that are within that worksheet's range against a range within the 2nd worksheet (this is also a dynamic range with the list of names changing).
3. Change the offset (2 columns over) of the changed cell in the 1st range with values in the offset of the range in the 2nd worksheet.

Since the ranges in both worksheets are dynamic, I am trying to assign variables depending on where the last row of data is. The main problem is that I cant seem to successfully specify the dynamic named range in worksheet 2 from the worksheet_change procedure of worksheet 1. I get lots of different errors no matter how I declare my variables.

What I have tried to do is declare variables from the worksheet_change procedure of worksheet 1 to find the last row of data for worksheet 2. Then I need to confirm the range I am checking against using the last row. When I declare a variable from the worksheet change I get errors so I am not sure whether there is a smarter way to declare the variable like having a procedure in a module or whether I need to specific the variable that refers to the 2nd worksheet from the worksheet_change procedure in the 1st worksheet.

Any help much appreciated.
Thanks
Steve

 

[JLGWhiz]

I have a workbook with 2 worksheets. Worksheet 1 has data entry table while worksheet 2 has 2 columns for a list of names and their departments

I have a worksheet change procedure in worksheet 1 that does 3 things:
1. Checks if the cell(s) changed are within a specifc range of that worksheet (This will be a dynamic range since rows of data are always added)
2. Compare the changed cell(s) that are within that worksheet's range against a range within the 2nd worksheet (this is also a dynamic range with the list of names changing).
3. Change the offset (2 columns over) of the changed cell in the 1st range with values in the offset of the range in the 2nd worksheet.

Since the ranges in both worksheets are dynamic, I am trying to assign variables depending on where the last row of data is. The main problem is that I cant seem to successfully specify the dynamic named range in worksheet 2 from the worksheet_change procedure of worksheet 1. I get lots of different errors no matter how I declare my variables.

What I have tried to do is declare variables from the worksheet_change procedure of worksheet 1 to find the last row of data for worksheet 2. Then I need to confirm the range I am checking against using the last row. When I declare a variable from the worksheet change I get errors so I am not sure whether there is a smarter way to declare the variable like having a procedure in a module or whether I need to specific the variable that refers to the 2nd worksheet from the worksheet_change procedure in the 1st worksheet.

Any help much appreciated.
Thanks
Steve

In a case like this, you should post your code so that meaningful suggestions can be mad, based on what the code is actually doing. But, taking a stab in the dark, it migh be that you just need to qualify your objects or properties when trying to declare a variable between sheets. For example, if you want to assign a variable to a range value on the second sheet, but are declaring it in the event code of the first sheet, you need to use the sheet reference as well as the range. example: myVar = Sheets(2).Range("C5").Value

In any case, it is always a good idea to fully qualify your objects and properties when working with more than one sheet, else, VBA will revert to the host sheet for the code or the active sheet when it cannot determine otherwise. Also, one of the biggies is trying to use the Cells property as a range object. It just won't work that way. When the Cells property is used to define a range, it must be qualified with the sheet reference, else, VBA uses ActiveSheet or will error out.

 

[iliace]

If they're dynamic named ranges in the worksheet, you can simply address them as such:Worksheet2.Range("myRange"), where myRange is the named range with the dynamic formula.

When doing it at run time, you can use a similar approach. For example, in Worksheet1 code module, you can say:

Dim LastCell As Excel.Range
With Worksheet2
  Set LastCell = .Range("A" & .UsedRange.Rows.Count)
End With

And now LastCell is a Range object that is the last row in column A of Worksheet2.

 

[jacko2401]

Sorry i didn't post the code but it was rather lengthy.

Your stabs in the dark were spot on in both cases as i didn't fully qualify everything and was also using cells objects. Thanks very much.