英文:
Copy and Paste row under specific column Heading VBA
问题
这是您的代码的翻译部分:
Sub CopyRow()
Dim lastRow As Long
Dim lRow As Long
Dim Data As String
Dim Exceptions As String
Data = "Data" '工作表名称
Exceptions = "Exceptions" '工作表名称
lastRow = Sheets(Data).Range("A" & Rows.Count).End(xlUp).Row
For lRow = 2 To lastRow '循环遍历所有行
If Application.CountIf(Sheets(Exceptions).Columns("A"), Sheets(Data).Cells(lRow, "A").Value2) > 0 Then
Sheets(Data).Range("A" & Rows.Count).End(xlUp).Offset(1).EntireRow.Value = Sheets(Data).Rows(lRow).Value2
End If
If Application.CountIf(Sheets(Exceptions).Columns("B"), Sheets(Data).Cells(lRow, "A").Value2) > 0 Then
Sheets(Data).Range("A" & Rows.Count).End(xlUp).Offset(1).EntireRow.Value = Sheets(Data).Rows(lRow).Value2
End If
If Application.CountIf(Sheets(Exceptions).Columns("C"), Sheets(Data).Cells(lRow, "A").Value2) > 0 Then
Sheets(Data).Range("A" & Rows.Count).End(xlUp).Offset(1).EntireRow.Value = Sheets(Data).Rows(lRow).Value2
End If
Next lRow
End Sub
请注意,这只是您代码的翻译部分,不包括问题的回答。
英文:
Right now I have code that is copying and pasting rows where the column "A" numbers on the "Data" sheet are found on the "Exceptions" sheet in columns A,B or C.
As of now they are pasting as last rows on the sheet, however Im wondering if theres a way to modify my code so they paste under the corresponding header from the "Exceptions" sheet.
Example 1: Numbers 456, 678 rows should be copied under the "A" Description header on the "Data" sheet since 456, 678 are labeled as "Move to A" on the "Exceptions" sheet.
Example 2: Number 123 row should be copied under the "B" Description header since labeled "Move to B" on "Exceptions" sheet
Example 3: Numbers 345 should be copied under the "C" description header on "Data" sheet since labled "Move to C" on the "Exceptions" sheet.
Sub CopyRow()
Dim lastRow As Long
Dim lRow As Long
Dim Data As String
Dim Exceptions As String
Data = "Data" 'Sheet name
Exceptions = "Exceptions" 'Sheet name
lastRow = Sheets(Data).Range("A" & Rows.Count).End(xlUp).Row
For lRow = 2 To lastRow 'Loop through all rows
If Application.CountIf(Sheets(Exceptions).Columns("A"), Sheets(Data).Cells(lRow, "A").Value2) > 0 Then
Sheets(Data).Range("A" & Rows.Count).End(xlUp).Offset(1).EntireRow.Value = Sheets(Data).Rows(lRow).Value2
End If
If Application.CountIf(Sheets(Exceptions).Columns("B"), Sheets(Data).Cells(lRow, "A").Value2) > 0 Then
Sheets(Data).Range("A" & Rows.Count).End(xlUp).Offset(1).EntireRow.Value = Sheets(Data).Rows(lRow).Value2
End If
If Application.CountIf(Sheets(Exceptions).Columns("C"), Sheets(Data).Cells(lRow, "A").Value2) > 0 Then
Sheets(Data).Range("A" & Rows.Count).End(xlUp).Offset(1).EntireRow.Value = Sheets(Data).Rows(lRow).Value2
End If
Next lRow
End Sub
Data Sheet (Before)
Exceptions Sheet
Data Sheet (After)
[
答案1
得分: 3
这是一段用VB语言编写的VBA宏代码,用于在Excel工作簿中移动数据行。代码中包括了从名为"Data"的工作表复制数据到名为"Exceptions"的工作表的逻辑。需要注意的是,代码中包含了一些注释和消息框,用于提示操作状态。
如果需要进一步的解释或帮助,请提出具体问题。
英文:
Feels a bit clunky, but try this out:
Sub MoveRows()
Dim wsD As Worksheet, wsE As Worksheet, r As Long
Dim rngData As Range, rngEx As Range, mH, mR, col As Long, hdr, v
Dim rngCopy As Range, lr As Long, addr
Set wsD = ThisWorkbook.Worksheets("Data")
Set wsE = ThisWorkbook.Worksheets("Exceptions")
Set rngData = wsD.Range("A2:B" & wsD.Cells(Rows.Count, "A").End(xlUp).Row)
For col = 1 To 3 'loop the columns on the exceptions sheet
lr = wsE.Cells(Rows.Count, col).End(xlUp).Row
If lr > 1 Then
'get the section header and find on "Data"
hdr = Replace(wsE.Cells(1, col).Value, "Move to ", "")
mH = Application.Match(hdr, rngData.Columns(2), 0)
If Not IsError(mH) Then 'matched the header?
For r = 2 To lr
v = wsE.Cells(r, col).Value
mR = Application.Match(v, rngData.Columns(1), 0)
If Not IsError(mR) Then
Set rngCopy = rngData.Rows(mR)
rngData.Rows(mH + 1).Insert 'add row below header
rngCopy.Copy rngData.Rows(mH + 1) 'copy values
rngCopy.Delete shift:=xlShiftUp 'delete source row
Else
'value was not matched
MsgBox "Value '" & v & "' not found on Data sheet!"
End If
Next r
Else
'header not matched
MsgBox "Header '" & hdr & "' not found on data sheet!"
End If
End If
Next col
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论