[已解决]修改VBA代码，求高手完善啊！！

whaisu

Sub Sv()
    Dim a$ '定义A为变量
     a = [F1] '在单元格K1显示编号
      [F1] = "恒生[2014]2014000" & (Right([F1], 4) * 1) + 1 '每运行一次宏编号增加1,公式中3为从右向左取第3位数开始计数,并且计算最大值为999,
ActiveWorkbook.Save '保存文件
'如要改变编号则只要改变[F1] = "4567891003000"中的  4567891003000  即可
Dim sFileName As String
sFileName = "d:\" & Sheet1.Range("f4").Value
ActiveWorkbook.SaveAs Filename:=sFileName, FileFormat:=xlNormal, _
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
    CreateBackup:=False
End Sub



如题：要求保存文件编号为恒生[2014]20140001至恒生[2014]20149999

最佳答案

月排行榜 / 总排行榜

yewei113

2014-1-7 22:32

1. Sub Sv()
   
2.     Dim a$ '定义A为变量
   
3.      a = [F1] '在单元格f1显示编号
   
4.       [F1] = "恒生[2014]2014" & Format(Val(Right(Range("f1"), 4)) + 1, "0000")
   
5.   ActiveWorkbook.Save
   
6. 
   
7.   Dim sFileName As String
   
8.    sFileName = "d:" & Sheet1.Range("f4").Value
   
9.     ActiveWorkbook.SaveAs Filename:=sFileName, FileFormat:=xlNormal, _
   
10.     Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
    
11.     CreateBackup:=False
    
12. End Sub

复制代码

跳转到最佳答案楼层

whaisu

也就是把最大值改为9999，求赐教

yewei113

1. Sub Sv()
   
2.     Dim a$ '定义A为变量
   
3.      a = [F1] '在单元格f1显示编号
   
4.       [F1] = "恒生[2014]2014" & Format(Val(Right(Range("f1"), 4)) + 1, "0000")
   
5.   ActiveWorkbook.Save
   
6. 
   
7.   Dim sFileName As String
   
8.    sFileName = "d:" & Sheet1.Range("f4").Value
   
9.     ActiveWorkbook.SaveAs Filename:=sFileName, FileFormat:=xlNormal, _
   
10.     Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
    
11.     CreateBackup:=False
    
12. End Sub

复制代码