Microsoft Excel 2010复制/粘贴编辑想法和帮助请求小细节
发布时间:2020-12-14 02:51:49 所属栏目:Windows 来源:网络整理
导读:是的,我已经在其他人的帮助下为Microsoft Excel编写了这个宏(如果有帮助的话,那就是2010年).我想知道是否有人有办法缩短它,并使其更有效率.然而,它之前仍然得到了相同的结果?我可以在这里找到我需要使用的CSV格式的一个例子……是的,不幸的是,它们必须被放
是的,我已经在其他人的帮助下为Microsoft Excel编写了这个宏(如果有帮助的话,那就是2010年).我想知道是否有人有办法缩短它,并使其更有效率.然而,它之前仍然得到了相同的结果?我可以在这里找到我需要使用的CSV格式的一个例子……是的,不幸的是,它们必须被放入那些列和单元格中.
我真正难以接受的唯一问题是: 这是一个例子CSV:Media Fire很干净,我保证.感谢您的时间. 宏代码将某些列/行数据单??元格从一个工作表复制到另一个工作表到特定单元格 Sub FormatData() Dim col As Integer For col = 1 To 1 With Worksheets(2) .Cells(2,2) = Cells(1,col) .Cells(2,3) = Cells(2,col) & ". " & Cells(3,col) & ". " & Cells(4,col) & ". " & Cells(5,col) & "." .Cells(2,4) = Cells(7,5) = Cells(10,col) End With Next col For col = 2 To 2 With Worksheets(2) .Cells(3,col) .Cells(3,col) & "." .Cells(3,col) End With Next col For col = 3 To 3 With Worksheets(2) .Cells(4,col) .Cells(4,col) & "." .Cells(4,col) End With Next col For col = 4 To 4 With Worksheets(2) .Cells(5,col) .Cells(5,col) & "." .Cells(5,col) End With Next col For col = 1 To 1 With Worksheets(2) .Cells(6,2) = Cells(13,col) .Cells(6,3) = Cells(14,col) & ". " & Cells(15,col) & ". " & Cells(16,col) & ". " & Cells(17,col) & "." .Cells(6,4) = Cells(19,5) = Cells(22,col) End With Next col For col = 2 To 2 With Worksheets(2) .Cells(7,col) .Cells(7,col) & "." .Cells(7,col) End With Next col For col = 3 To 3 With Worksheets(2) .Cells(8,col) .Cells(8,col) & "." .Cells(8,col) End With Next col For col = 4 To 4 With Worksheets(2) .Cells(9,col) .Cells(9,col) & "." .Cells(9,col) End With Next col For col = 1 To 1 With Worksheets(2) .Cells(10,2) = Cells(25,col) .Cells(10,3) = Cells(26,col) & ". " & Cells(27,col) & ". " & Cells(28,col) & ". " & Cells(29,col) & "." .Cells(10,4) = Cells(31,5) = Cells(34,col) End With Next col For col = 2 To 2 With Worksheets(2) .Cells(11,col) .Cells(11,col) & "." .Cells(11,col) End With Next col For col = 3 To 3 With Worksheets(2) .Cells(12,col) .Cells(12,col) & "." .Cells(12,col) End With Next col For col = 4 To 4 With Worksheets(2) .Cells(13,col) .Cells(13,col) & "." .Cells(13,col) End With Next col For col = 1 To 1 With Worksheets(2) .Cells(14,2) = Cells(37,col) .Cells(14,3) = Cells(38,col) & ". " & Cells(39,col) & ". " & Cells(40,col) & ". " & Cells(41,col) & "." .Cells(14,4) = Cells(43,5) = Cells(46,col) End With Next col For col = 2 To 2 With Worksheets(2) .Cells(15,col) .Cells(15,col) & "." .Cells(15,col) End With Next col For col = 3 To 3 With Worksheets(2) .Cells(16,col) .Cells(16,col) & "." .Cells(16,col) End With Next col For col = 4 To 4 With Worksheets(2) .Cells(17,col) .Cells(17,col) & "." .Cells(17,col) End With Next col For col = 1 To 1 With Worksheets(2) .Cells(18,2) = Cells(49,col) .Cells(18,3) = Cells(50,col) & ". " & Cells(51,col) & ". " & Cells(52,col) & ". " & Cells(53,col) & "." .Cells(18,4) = Cells(55,5) = Cells(58,col) End With Next col For col = 2 To 2 With Worksheets(2) .Cells(19,col) .Cells(19,col) & "." .Cells(19,col) End With Next col For col = 3 To 3 With Worksheets(2) .Cells(20,col) .Cells(20,col) & "." .Cells(20,col) End With Next col For col = 4 To 4 With Worksheets(2) .Cells(21,col) .Cells(21,col) & "." .Cells(21,col) End With Next col For col = 1 To 1 With Worksheets(2) .Cells(22,2) = Cells(61,col) .Cells(22,3) = Cells(62,col) & ". " & Cells(63,col) & ". " & Cells(64,col) & ". " & Cells(65,col) & "." .Cells(22,4) = Cells(67,5) = Cells(70,col) End With Next col For col = 2 To 2 With Worksheets(2) .Cells(23,col) .Cells(23,col) & "." .Cells(23,col) End With Next col For col = 3 To 3 With Worksheets(2) .Cells(24,col) .Cells(24,col) & "." .Cells(24,col) End With Next col For col = 4 To 4 With Worksheets(2) .Cells(25,col) .Cells(25,col) & "." .Cells(25,col) End With Next col For col = 1 To 1 With Worksheets(2) .Cells(26,2) = Cells(73,col) .Cells(26,3) = Cells(74,col) & ". " & Cells(75,col) & ". " & Cells(76,col) & ". " & Cells(77,col) & "." .Cells(26,4) = Cells(79,5) = Cells(82,col) End With Next col For col = 2 To 2 With Worksheets(2) .Cells(27,col) .Cells(27,col) & "." .Cells(27,col) End With Next col For col = 3 To 3 With Worksheets(2) .Cells(28,col) .Cells(28,col) & "." .Cells(28,col) End With Next col For col = 4 To 4 With Worksheets(2) .Cells(29,col) .Cells(29,col) & "." .Cells(29,col) End With Next col For col = 1 To 1 With Worksheets(2) .Cells(30,2) = Cells(85,col) .Cells(30,3) = Cells(86,col) & ". " & Cells(87,col) & ". " & Cells(88,col) & ". " & Cells(89,col) & "." .Cells(30,4) = Cells(91,5) = Cells(94,col) End With Next col For col = 2 To 2 With Worksheets(2) .Cells(31,col) .Cells(31,col) & "." .Cells(31,col) End With Next col For col = 3 To 3 With Worksheets(2) .Cells(32,col) .Cells(32,col) & "." .Cells(32,col) End With Next col For col = 4 To 4 With Worksheets(2) .Cells(33,col) .Cells(33,col) & "." .Cells(33,col) End With Next col End Sub 解决方法
这是一个重构的Sub
注意事项: >使用变量数组进行循环/数据处理,因为循环遍历单元格很慢 . Sub FormatData() Dim rw2 As Integer,rwA As Integer,colA As Integer Dim vDst() As Variant,vSrc As Variant Dim srcBlocks As Integer srcBlocks = 8 ' process 8 blocks of 12 rows vSrc = ActiveSheet.Range("A1:D" & srcBlocks * 12) ReDim vDst(1 To srcBlocks * 4 + 1,1 To 5) For rwA = 0 To srcBlocks * 12 - 1 Step 12 ' = 0,12,24,... For colA = 1 To 4 ' 4 columns in Src rw2 = (rwA 12) * 4 + colA + 1 ' 4 Dst rws per block,= 2..5,6..9,... vDst(rw2,2) = vSrc(rwA + 1,colA) vDst(rw2,3) = vSrc(rwA + 2,colA) & ". " & _ vSrc(rwA + 3,colA) & ". " & _ vSrc(rwA + 4,colA) & ". " & _ vSrc(rwA + 5,colA) & "." vDst(rw2,4) = vSrc(rwA + 7,5) = vSrc(rwA + 10,colA) Next colA Next rwA Worksheets(2).Range("A1:E" & CStr(srcBlocks * 4 + 1)) = vDst End Sub 要为Source数据中的可变数量的列添加灵活性 Sub FormatData() Dim rw2 As Integer,vSrc As Variant Dim srcBlocks As Integer,srcColumns As Integer srcBlocks = 8 ' process 8 blocks of 12 rows ' srcColumns = 5 ' Columns in source data ' vSrc = ActiveSheet.Range( _ ActiveSheet.Cells(1,1),_ ActiveSheet.Cells(srcBlocks * 12,srcColumns)) ReDim vDst(1 To srcBlocks * srcColumns + 1,1 To 5) For rwA = 0 To srcBlocks * 12 - 1 Step 12 ' = 0,... ' For colA = 1 To srcColumns ' srcColumns columns in Source ' rw2 = (rwA 12) * srcColumns + colA + 1 ' srcColumns rows in Destination per Source block ' vDst(rw2,colA) & ". " & _ vSrc(rwA + 3,colA) & ". " & _ vSrc(rwA + 4,colA) & ". " & _ vSrc(rwA + 5,colA) Next colA Next rwA Worksheets(2).Range("A1:E" & CStr(srcBlocks * 4 + 1)) = vDst End Sub (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
相关内容
- 适用于Windows的Snmp Server
- microsoft-office-365 – Office 365 – Outlook在会议请求
- 与在NTFS上存储数百万个文件相关的性能
- Windows Vista是否具有应用级别的远程处理或者我是否产生幻
- 为什么在Windows 7中使用错误的格式说明符崩溃我的程序?
- windows-10 – Windows 10,两个不同会话的监视器
- active-directory – 如何登录不信任自己的域控制器
- 如何获取Windows批处理的父文件夹
- windows-7 – Windows 7上的Windows批处理文件问题
- windows-phone-7 – 提示文本的密码框