使用Excel VBA添加下一个或上一个字母
使用Excel VBA添加下一个或上一个字母
提问于 2021-10-18 06:36:49
我有这样的数据集
我想将每行复制两次，并在"code“列中添加下一个或前一个字母。我能够实现第一个目标(每行复制两次)，但我坚持在"code“列中添加下一个或前一个字母。
这就是我所做的：
Sub mysub()
Dim r As Range, n As Long, i As Long
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws1, ws2 As Worksheet
Set ws1 = wb.Sheets("Sheet1")
Set ws2 = wb.Sheets("Sheet2")
Set r = ws1.Range("C3", ws1.Range("E" & Rows.Count).End(xlUp))
For i = 1 To r.Rows.Count
n = n + 1
ws2.Cells(n + 1, 1).Value = r.Cells(i, 1).Value
ws2.Cells(n + 1, 2).Value = r.Cells(i, 2).Value
ws2.Cells(n + 1, 3).Value = r.Cells(i, 3).Value
n = n + 1
ws2.Cells(n + 1, 1).Value = r.Cells(i, 1).Value
ws2.Cells(n + 1, 2).Value = r.Cells(i, 2).Value * -1
Next i
End Sub我得到了这个
但我想说的是：
如果能帮上忙，我们将不胜感激
回答 2
Stack Overflow用户
回答已采纳
发布于 2021-10-18 07:24:11
你已经很接近了，只需要加一行。
您可以使用Resize略微缩短代码。
Sub mysub()
Dim r As Range, n As Long, i As Long
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws1 As Worksheet, ws2 As Worksheet 'need to specify each
Set ws1 = wb.Sheets("Sheet1")
Set ws2 = wb.Sheets("Sheet2")
Set r = ws1.Range("C3", ws1.Range("C" & Rows.Count).End(xlUp))
For i = 1 To r.Rows.Count
n = n + 1
ws2.Cells(n + 1, 1).Resize(2, 2).Value = r.Cells(i, 1).Resize(, 2).Value
ws2.Cells(n + 1, 3).Value = r.Cells(i, 3).Value
n = n + 1
ws2.Cells(n + 1, 2).Value = r.Cells(i, 2).Value * -1
ws2.Cells(n + 1, 3).Value = IIf(r.Cells(i, 3) = "C", "D", "C") 'added
Next i
End SubStack Overflow用户
发布于 2021-10-18 07:47:46
我将执行以下操作:将原始范围复制到sheet2，然后向后循环，复制每一行并调整值。
这应该比单独处理每个单元快一点。您可以使用数组更快地获得它。
Option Explicit
Public Sub mysub()
Dim wb As Workbook
Set wb = ThisWorkbook
Dim ws1 As Worksheet
Set ws1 = wb.Sheets("Sheet1")
Dim ws2 As Worksheet
Set ws2 = wb.Sheets("Sheet2")
Dim r As Range
Set r = ws1.Range("C3", ws1.Range("E" & Rows.Count).End(xlUp))
'copy from ws1 to ws2
ws2.Range("A2").Resize(r.Rows.Count, r.Columns.Count).Value = r.Value
'duplicate values: loop backwards when inserting or deleting
Dim i As Long
For i = r.Rows.Count To 1 Step -1
' duplicate row
ws2.Rows(i + 1).Copy
ws2.Rows(i + 1).Insert xlShiftDown
' adjust value *-1
ws2.Rows(i + 1).Cells(1, 2).Value = ws2.Rows(i + 1).Cells(1, 2).Value * -1
' adjust D/C
If ws2.Rows(i + 1).Cells(1, 3).Value = "D" Then
ws2.Rows(i + 1).Cells(1, 3).Value = "C"
Else
ws2.Rows(i + 1).Cells(1, 3).Value = "D"
End If
Next i
Application.CutCopyMode = False
End Sub页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/69617983
复制相关文章
相似问题
社区富文本编辑器全新改版！诚邀体验～
全新交互，全新视觉，新增快捷键、悬浮工具栏、高亮块等功能并同时优化现有功能，全面提升创作效率和体验
腾讯云开发者
