Correct Qualification for Cells()

[NewOrderFac33]

Good afternoon,

After battering my head all afternoon with various permutations of the below, I'm conceding defeat and throwing myself on the mercy of the Excel Gods

Dim StatsSheet As Worksheet
Set StatsSheet = Worksheets("Stats") 

Dim TransSheet As Worksheet
Set TransBook = Workbooks.Open(TransBookName)
Set TransSheet = TransBook.Sheets("Table")

For Each MyCell In StatsSheet.Range("BR_Engineers")
      For Each MyCell2 In TransSheet.Range("Trans_Engineers")
              If Trim(UCase(MyCell2)) = UCase(Trim(MyCell))
                    StatsSheet.Cells(Range("Totals_SUPBAR_APR").Row, MyCell.Column) = TransSheet.Cells(MyCell2.Row, Range("Trans_SUPBAR_APR").Column) 
              end if
        Next
Next

The Names I'm searching on in "StatsSheet" run Horizontally, and in TransSheet they run vertically, so "Totals_SUPBAR_APR" also runs horizontally and "Trans_SUPBAR_APR" runs vertically.

So, I'm looking to transfer AND transpose values based on if the Engineer name matches.

I really need someone to take a look at the way I've qualified the ranges in the "StatsSheet.Cells..." line, as no matter what I do, the values don't transfer correctly

Excel errors out with "Method 'Range' of object'_Global' failed, by the way.

Thanks in advance

Pete

Pete

 

[Fluff]

Try qualifying the named ranges with the sheet name, like

StatsSheet.Range("Totals_SUPBAR_APR").Row

 

[NewOrderFac33]

Hi, Fluff - it's 21:50 here, so I'll give it a go tomorrow - thank you.
Pete

 

[Fluff]

That's fine, it's the same time here & I'm off down the pub.

 

[NewOrderFac33]

Sorted - there was nothing wrong with my code, rather something contained within the line of code that I couldn't see (if that makes sense)
There were a number of lines similar to:

StatsSheet.Cells(Range("Totals_SUPBAR_APR").Row, MyCell.Column) = TransSheet.Cells(MyCell2.Row, Range("Trans_SUPBAR_APR").Column)

all working on different ranges and it was this (the first of 6) one that was erroring (I didn't include all the lines in my original post for brevity)
When I deleted the line of code, then built it back up, exactly the same as it had been before, it ran perfectly!
I've encountered this before in the VBE on a number of occasions where I delete the offending line, replace it with something similar from somewhere else, or type it in again from scratch, then change it to fit and it always solves the problem.

StatsSheet.Cells(StatsSheet.Range("Totals_SUPBAR_APR").Row, MyCell.Column) = TransSheet.Cells(MyCell2.Row, TransSheet.Range("Trans_SUPBAR_APR").Column)
StatsSheet.Cells(StatsSheet.Range("Totals_SUPBAR_PRC").Row, MyCell.Column) = TransSheet.Cells(MyCell2.Row, TransSheet.Range("Trans_SUPBAR_PRC").Column)
StatsSheet.Cells(StatsSheet.Range("Totals_PRJGCM_APR").Row, MyCell.Column) = TransSheet.Cells(MyCell2.Row, TransSheet.Range("Trans_PRJGCM_APR").Column)
StatsSheet.Cells(StatsSheet.Range("Totals_PRJGCM_PRC").Row, MyCell.Column) = TransSheet.Cells(MyCell2.Row, TransSheet.Range("Trans_PRJGCM_PRC").Column)
StatsSheet.Cells(StatsSheet.Range("Totals_PRJGIT_APR").Row, MyCell.Column) = TransSheet.Cells(MyCell2.Row, TransSheet.Range("Trans_PRJGIT_APR").Column)
StatsSheet.Cells(StatsSheet.Range("Totals_PRJGIT_PRC").Row, MyCell.Column) = TransSheet.Cells(MyCell2.Row, TransSheet.Range("Trans_PRJGIT_PRC").Column)

I wish I knew what causes this - it's as though there are invisible characters in the line of code somewhere that cause it to error.
I also wish I'd remembered this before I spent the best part of 6 hours trying to solve a range referencing issue that didn't exist!
Hope you enjoyed your pint, Fluff!
Pete

 

[Fluff]

In your second block of code you are qualifying the named ranges like

StatsSheet.Cells([COLOR=#ff0000]StatsSheet.[/COLOR]Range("Totals_SUPBAR_APR").Row, MyCell.Column) = TransSheet.Cells(MyCell2.Row, [COLOR=#ff0000]TransSheet.[/COLOR]Range("Trans_SUPBAR_APR").Column)

which you weren't doing originally. If the ranges are workbook scope you don't need to do that, bu if they are sheet scope then you do.

 

[NewOrderFac33]

Morning, Fluff - they are now, yes, but originally, before I added the qualifications, the first line failed but the 2nd to the 6th ones didn't (which I discovered by commenting out the first line).
I then deleted the first line copied the second line to the first line and changed it and it all worked fine.
This isn't the first time I've encountered this with the VBE where something errors, you either delete it and type it all in again exactly the same, or copy it from somewhere else and change it (as opposed to trying to change parts of the exiting line line, which doesn't).
I wouldn't have thought it was possible to have hidden characters in the VBE, but that's how it appears.
never mind, I'll just have to remember my "Tpe it in again" rule if I encounter this in the future.
Thanks - Pete