0

Wondering why this piece of code does not work properly.

Sub copyfirst()


For i = 1 To Sheet20.Cells(Rows.Count, 1).End(xlUp).Row
Set x = Sheet20.Range("A" & i)

'here i tell vba to find text in bold and in black

If x.Characters(i, 1).Font.Color = RGB(0, 0, 0) And x.Characters(1, 1).Font.Bold = True Then
res = x.Text

'then i copy it in another sheet in column 1

x.Copy Sheet21.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

Debug.Print x.Address

x.Offset(1, 2).Activate

'PROBLEM! here is the problem
Debug.Print x.Offset(1, 2).Address

Range(ActiveCell, ActiveCell.End(xlDown)).Copy

Sheet21.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues, Operation:=xlNone, _
                                                    SkipBlanks:=False, Transpose:=True
Else: GoTo Nextiteration
End If

Nextiteration:
Next i
End Sub

So the problem is in the line x.Offset(1, 2).Activate. In fact, x is in A & i, so the offset(1,2) should give me C & i+1.

Instead, it gives me column D! Why is that?

Here the debug.print address of x and x.0ffset(1,2)

x.address= $A$3

x.offset(1,2)= $D$4

It should be C4! Not D4! It always gives me D4 except for the last variable it finds, where it correctly gives me column C.

Why is that?

Saverio
  • 111
  • 8
  • 2
    Do you have merged cells? Please also read up on indenting your code and how to avoid activating/selecting things. – SJR Sep 17 '18 at 10:25
  • need to understand better how to indent because is frustrating to always activate sheets and cells. But yes, you are right. The problem was on the merging cells! Do you know a shortcut to avoid the offset function to consider merged cells? Thanks – Saverio Sep 17 '18 at 10:29
  • 2
    The use of `GoTo` as flow control in a loop seems a bit *questionable*. I'd recommend getting rid of it. In your case, you wouldn't lose anything if you just removed the lines `Else: GoTo Nextiteration` and the label `Nextiteration:`, as execution will just naturally continue with the next iteration once it has evaluated the `If` statement. – Inarion Sep 17 '18 at 10:34
  • 2
    This is a useful link for avoiding Select https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?s=1|133.3447 Ideally avoid merged cells if possible. If that's not possible there is probably a workaround, not sure this minute. – SJR Sep 17 '18 at 10:34

1 Answers1

0

If x is merged cell then do like this. IF x is range("a1") then x(1,1) equals range("a1"), x(2,2)= B2, x(2,3)= C2

x(2,3).activate
Dy.Lee
  • 7,527
  • 1
  • 12
  • 14