作者:赵庭洪 | 来源:互联网 | 2023-05-19 09:56
HiIhaveaspreadsheetwhichIneedtoextractsomedataandcopyittoanewsheet.嗨,我有一个电子表格,我需
Hi I have a spreadsheet which I need to extract some data and copy it to a new sheet.
嗨,我有一个电子表格,我需要提取一些数据并将其复制到新表。
Below is an image of the spreadsheet. What I want is a copy of all the yellow highlighted columns in a new sheet when column M & Column N contain numbers (in this case there are only 2 that will be on the second sheet- row 7 & row 26). This sheet is quite big so this is just a sample.
下面是电子表格的图像。当列M和列N包含数字时,我想要的是新表中所有黄色突出显示列的副本(在这种情况下,只有2个将在第二个表 - 第7行和第26行)。这张表非常大,所以这只是一个样本。
![enter image description here](https://www.#.com/imgs/6/9/6/9/32/86c6f1ad89f11e6e9582559fad324c81.jpe)
Here is the code I have tried to use:
Sub CopyYesAdrian()
Dim a As Variant, o As Variant
Dim i As Long, j As Long, n As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")
a = .Cells(1, 1).CurrentRegion
n = Application.CountIf(.Columns(9), "Yes")
ReDim o(1 To n + 1, 1 To 11)
End With
j = j + 1: o(j, 1) = "CORP ID": o(j, 2) = "Application ID"
o(j, 3) = "Orig - Country": o(j, 4) = "Network DDI": o(j, 5) = "PTT ACCESS":
o(j, 6) = "TERM - COUNTRY": o(j, 7) = "TERM SW/TRUNK"
: o(j, 8) = "TERM DIGITS": o(j, 9) = "PSTN ROUTE": o(j, 10) = "PLAN": o(j,
11) = "DNIS"
For i = 2 To UBound(a, 1)
If IsError(a(i, 6)) Then
ElseIf a(i, 8) = "Yes" And a(i, 6) Like "[0-9][0-9][0-9][/][0-9]*" Then
j = j + 1: o(j, 1) = a(i, 1): o(j, 2) = a(i, 3)
o(j, 3) = a(i, 4): o(j, 4) = a(i, 6): o(j, 5) = a(i, 8): o(j, 6) = a(i,
9): o(j, 7) = a(i, 10): o(j, 8) = a(i, 11)
: o(j, 9) = a(i, 12): o(j, 10) = a(i, 12): o(j, 11) = a(i, 12)
End If
Next i
With Sheets("Sheet2")
.UsedRange.Clear
.Cells(1, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
.UsedRange.Columns.AutoFit
.Activate
End With
Application.ScreenUpdating = True
End Sub
2 个解决方案
0
Replace your multi-statement lines with this:
用以下内容替换多语句行:
j = j + 1
o(j, 1) = "CORP ID"
o(j, 2) = "Application ID"
o(j, 3) = "Orig - Country"
o(j, 4) = "Network DDI"
o(j, 5) = "PTT ACCESS"
o(j, 6) = "TERM - COUNTRY"
o(j, 7) = "TERM SW/TRUNK"
o(j, 8) = "TERM DIGITS"
o(j, 9) = "PSTN ROUTE"
o(j, 10) = "PLAN"
o(j, 11) = "DNIS"
For i = 2 To UBound(a, 1)
If IsError(a(i, 6)) Then
ElseIf a(i, 8) = "Yes" And a(i, 6) Like "[0-9][0-9][0-9][/][0-9]*" Then
j = j + 1
o(j, 1) = a(i, 1)
o(j, 2) = a(i, 3)
o(j, 3) = a(i, 4)
o(j, 4) = a(i, 6)
o(j, 5) = a(i, 8)
o(j, 6) = a(i, 9)
o(j, 7) = a(i, 10)
o(j, 8) = a(i, 11)
o(j, 9) = a(i, 12)
o(j, 10) = a(i, 12)
o(j, 11) = a(i, 12)
End If
Please try this and let me know if it works.
请尝试这个,让我知道它是否有效。