Excel VBA skipping loop order

[danielbond]

Dear all,

I am going to change a bundle of excels which named according to incomplete ascending number( etc: first one calls 1_2016, second calls 2_2016, but the third calls 4_2016, etc.), my purpose is renew their names in 2017 instead of 2016.

However, I encounter into using loop, when I set i =1 to 4, VBA would show error message when it finished running i=2, how can I adjust the command to let the loop can skip n(3) automatically while it does not have any corresponding file?

The following is my current steps:

Dim n(4) As String
n(1) = "1"
n(2) = "2"
n(4) = "4"

For i = 1 To 4

(...skip the path setting...)

ActiveWorkbook.SaveAs (n(i)&"_2017")
ActiveWorkbook.Close

Next i
End Sub


Thank you guys!!

 

[FormR]

The following is my current steps:

Hi, if you know in advance which ones exist or not (and your code implies that you do) then it would look more like this.

Dim n(3) As String
 n(1) = "1"
 n(2) = "2"
 n(3) = "4" 

For i = 1 To UBound(n)

'Etc

 

[danielbond]

Hi, if you know in advance which ones exist or not (and your code implies that you do) then it would look more like this.

Dim n(3) As String
 n(1) = "1"
 n(2) = "2"
 n(3) = "4" 

For i = 1 To UBound(n)

'Etc

Thank you dude, may I ask in advance? Since my real problem facing is as below:

My purpose is preparing a new questionnaire for respondents in 2017. The excel name is formed as NAME_DATE (eg: MARY_2016.xls), furthermore, in the first sheet of the questionnaire, there would be a fixed member number showed in one cell. But the problem is they are not following ascending number completely, if I use loop i to generate those document, it would be error.

When I prepare those things for 2017, I would first establish a plain excel called XXXX_2017.xls then make couple of copies as XXXX_2017-copy(1), XXXX_2017-copy(2),XXXX_2017-copy(3),XXXX_2017-copy(4).
I want the result is renaming them and filling the member number cell as below:

MARY_2017.xls (with member no:0001); JEAN_2017.xls( no:0002); NICK_2017.xls (no: 0004); JOHN_2017.xls (no:0005), etc.

So my code is as below:
Dim n(4) As String
n(1) = "MARY"
n(2) = "JEAN"
n(3) = "NICK"
n(4) = "JOHN"

For i = 1 To 4
s1 = "C:\questionnaire"
s2 = "XXXX_2017 - Copy ("
s3 = ")"
s4 = ".xls"
code = "000"

Workbooks.Open s1 & s2 & i & s3 & s4
ActiveWorkbook.Sheets("main").Activate


Cells(1, 1) = code & i
End If

ActiveWorkbook.SaveAs (s1 & n(i) & "_2017")
ActiveWorkbook.Close


Next i
End Sub

If I run this, member number will start to go wrong from NICK, may you have any suggestion how I can solve it?

Much thanks!

 

[FormR]

If I run this, member number will start to go wrong from NICK, may you have any suggestion how I can solve it?

How do you know what number Nick should be? Maybe you could store the names and the associated numbers on a worksheet and look them up from there?

 

[danielbond]

How do you know what number Nick should be? Maybe you could store the names and the associated numbers on a worksheet and look them up from there?

Yes I would know all member number as it started from 0001, but some of them quite their membership results in this situation.

That will be a good idea to store them first if VBA cannot do my favorite.