Define varying ranges in for next loop

Question

I would like to define a range that changes according to a value in a For loop and the values of this range to another sheet.

Here is the code.

For i = 2 To 161672
  If cgmfitbit.Worksheets(1).Cells(i, 2).Value = ptnames Then
    ptfile.Worksheets(1).Range("A" & i & ":P" & i).Value = cgmfitbit.Worksheets(1).Range("A" & i & ":P" & i).Value
  End If
Next i

I am getting a run time error 91.

Possible duplicate of [What is a NullReferenceException, and how do I fix it?](http://stackoverflow.com/q/4660142/11683) — GSerg, Sep 03 '16 at 13:47
@GSerg - this is a [tag:vba] question, so a [tag:.net] answer will not be appropriate for a duplicate candidate. — Robin Mackenzie, Sep 03 '16 at 16:00
@RobinMackenzie That is why I did not actually vote, but that answer is a canonical answer for the null reference problem. It is good enough to explain the principle, and VB.NET is a particularly good match to VBA in this regard. — GSerg, Sep 03 '16 at 16:41

h2so4 · Accepted Answer · 2016-09-03T14:32:31.583

1

a proposal, that will copy the value and the format as well

For i = 2 To 161672
  If cgmfitbit.Worksheets(1).Cells(i, 2).Value = ptnames Then
    cgmfitbit.Worksheets(1).Range("A" & i & ":P" & i).copy ptfile.Worksheets(1).Range("A" & i)
  End If
Next i

edited Sep 03 '16 at 14:32

answered Sep 03 '16 at 14:07

h2so4

1,559
1
10
11

Define varying ranges in for next loop

1 Answers1