The below code will not work when i = 1, but works for all remaining values. I have validated there is a value in columns AU & J for i = 1.

If i < 10 Then

 For j = 2 To effCountC
  MSI = Range("AU" & j).Value
  Worksheets("SN" & i).Range("A" & j).Value = "X000" & i & "_" & MSI
 Next j

Else

 For j = 2 To effCountC
  Worksheets("SN" & i).Range("A" & j).Value = "X00" & i & "_" & Range("AU" & j).Value
 Next j

End If

I would like to understand why this code does not work for the first iteration.

I have tried using stops to validate that Excel was reading the values in Column AU, and it appears to be doing so. For some reason, however, the value does not get printed to Column A. Only the first part (X000i_) prints to Column A. However, when i > 1, Excel prints the desired value (X000i_MSI).

The below code will not work when i = 1, but works for all remaining values. I have validated there is a value in columns AU & J for i = 1.

If i < 10 Then

 For j = 2 To effCountC
  MSI = Range("AU" & j).Value
  Worksheets("SN" & i).Range("A" & j).Value = "X000" & i & "_" & MSI
 Next j

Else

 For j = 2 To effCountC
  Worksheets("SN" & i).Range("A" & j).Value = "X00" & i & "_" & Range("AU" & j).Value
 Next j

End If

I would like to understand why this code does not work for the first iteration.

I have tried using stops to validate that Excel was reading the values in Column AU, and it appears to be doing so. For some reason, however, the value does not get printed to Column A. Only the first part (X000i_) prints to Column A. However, when i > 1, Excel prints the desired value (X000i_MSI).

• excel
• vba

• 2 Some of your ranges don't have a sheet and are defaulting to the active sheet. That is most likely the issue. – Warcupine Commented Feb 14 at 19:32

1 Answer 1

This can be simpler as:

For j = 2 To effCountC
    MSI = Range("AU" & j).Value '<< you need to specify a worksheet here...
    Worksheets("SN" & i).Range("A" & j).Value = Format(i, "X0000") & "_" & MSI
Next j

For more on specifying specific worksheets (and workbooks) in your code, there's some useful reading here:

How to avoid using Select in Excel VBA?

Following the advice there will help you avoid most of the common bugs people encounter when getting started with VBA in Excel.