Debugs

[Rudi]

Hi,

This works...

2013-12-19_12h17_14.jpg

This does not...

2013-12-19_12h18_19.jpg

Any idea why I need to first activate the workbook before I can apply the action?
I cannot recall this being an issue before...(AFAIK)?

[HansV]

You have to qualify ALL ranges:

Code: Select all

Private Sub IDMismatch()
    Dim wsh1 As Worksheet
    Dim wsh2 As Worksheet
    Dim wsh3 As Worksheet
    Application.ScreenUpdating = False
    Set wsh1 = ThisWorkbook.Worksheets("ID_Temp")
    Set wsh2 = Workbooks(WB1).Worksheets(1)
    Set wsh3 = Workbooks(WB2).Worksheets(1)
    wsh2.Range("A5", wsh2.Range("A1048000").End(xlUp)).Copy _
        wsh1.Range("A4")
    wsh1.Range("A4", wsh1.Range("A1048000").End(xlUp)).Offset(0, 1).Value = _
        "=MATCH(RC[-1]),'" & WBD3 & "!C52,0)"
    wsh3.Range("A5", wsh2.Range("A1048000").End(xlUp)).Copy _
        wsh1.Range("D4")
    wsh1.Range("D4", wsh1.Range("D1048000").End(xlUp)).Offset(0, 1).Value = _
        "=MATCH(RC[-1]),'" & WBD3 & "!C52,0)"
    wsh1.Columns("A:E").Copy
    wsh1.Columns("A:E").PasteSpecial xlValues

Note: I'd use FormulaR1C1 instead of Value.

[Rudi]

OK. TX.

I have...

Option Explicit
Dim WBD3 As String, WB1 As String, WB2 As String

...at the top of the module.

So it seems then that it needs to be an object reference in stead.
Strange as i would have expected Worksheets(WB1).Sheets(1).... to be fully qualified?

[HansV]

It doesn't have to be a variable. The problem was in lines such as

Workbooks(WB1).Sheets(1).Range("A5", Range("A1048000").End(xlUp)).Copy _

Here, Range("A1048000") isn't qualified, so it refers to cell A1048000 on the active worksheet, NOT on Workbooks(WB1).Sheets(1).

[Rudi]

Missed that....I think it's time for

[Rudi]

Note: I'd use FormulaR1C1 instead of Value.

I thought that when one writes to a cell, value, formula and formular1c1 are the same, but when you read from a cell, they are different. Can you give me your thoughts as to why you say it is better in the above macro to use formular1c1?

TX

[HansV]

You're technically correct that you can assign a formula to a cell by setting its Value property to a formula string in A1 or R1C1 format.
I have two reasons for preferring to use the Formula or FormulaR1C1 property:

1) It makes it more obvious that you're assigning a formula: it's more self-documenting.
2) Some formulas are ambiguous: for example the formula =RC5 can be an A1-style formula and an R1C1-style formula, but they will have a different meaning.
As an A1-style formula, =RC5 refers to the cell in column RC, row 5.
But as an R1C1-style formula, =RC5 refers to the cell in column 5 ("E") in the same row as the cell with the formula.
If you set the Value of a cell to "=RC5", it will be interpreted as an A1-style formula, while you may have intended an R1C1-style formula.
Using Formula or FormulaR1C1 makes your intention explicit, it doesn't leave room for a different interpretation.

[Rudi]

Understood. I've never really taken time to think through it properly and always just thought typing value is quicker. But having drawn my attention to it, i'll be more aware of my implicideness. TX