代码报错，帮我看看全部代码：' Ö÷Êý¾Ý¸´Öƺ¯Êý£¨ÍêÈ«ÓÅ»¯°æ£©
Sub CopyData(dataType As String)
Dim wsTarget As Worksheet
Dim wsSource As Worksheet
Dim sourceSheets As Variant
Dim lastRow As Long, targetRow As Long
Dim i As Long, j As Long, k As Long
Dim copyCols As Variant
Dim startTime As Double
Dim firstSourceFound As Boolean
Dim colIndex As Long, maxCol As Long
Dim dataTypeColumn As Long
Dim dataArr As Variant
Dim rowCount As Long
startTime = Timer ' ¼Æʱ¿ªÊ¼
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
On Error GoTo ErrorHandler ' ´íÎó´¦Àí
Set wsTarget = ThisWorkbook.Sheets("summary")
' Çå³ýÄ¿±êÇøÓò¾ÉÊý¾Ý£¨±£Áô¸ñʽ£©
wsTarget.Range("A:Z").ClearContents ' ÐÞÕý£ºÖ±½ÓÇå³ýA-ZÁÐËùÓÐÄÚÈÝ
' ÉèÖò»Í¬Êý¾ÝÀàÐ͵ÄÁÐÓ³Éä
Select Case UCase(dataType)
Case "POE"
copyCols = Array("C", "D", "E", "F", "G", "I", "J", "K", "M", "N", "O")
Case "MD"
copyCols = Array("A", "C", "D", "E", "F", "G", "I", "J", "K", "L", "M")
Case "CB"
copyCols = Array("C", "D", "E", "F", "G", "I", "J", "K", "M", "N", "O", "Q", "R")
Case Else
MsgBox "ÎÞЧµÄÊý¾ÝÀàÐÍ: " & dataType, vbExclamation
GoTo Cleanup
End Select
' ±éÀúËùÓÐÔ´¹¤×÷±í
sourceSheets = Array("CGHR", "CGMA", "CHHT", "CGBO", "CPPL", "CGEL")
targetRow = 1 ' ³õʼֵ£¨ºóÐø¸ù¾ÝÊÇ·ñ¸´ÖƱêÌâµ÷Õû£©
firstSourceFound = False
rowCount = 0
For i = LBound(sourceSheets) To UBound(sourceSheets)
Set wsSource = Nothing
On Error Resume Next
Set wsSource = ThisWorkbook.Sheets(sourceSheets(i))
On Error GoTo 0
If Not wsSource Is Nothing Then
' »ñÈ¡KÁеÄ×îºóÊý¾ÝÐУ¨ÓÃÓÚÅжÏÊý¾Ý·¶Î§£©
lastRow = GetLastDataRow(wsSource, "K")
' ¸´ÖƱêÌâÐУ¨½öµÚÒ»¸öÓÐЧ¹¤×÷±íÖ´ÐУ©
If Not firstSourceFound And lastRow >= 1 Then
For k = 0 To UBound(copyCols)
colIndex = GetColumnIndex(copyCols(k)) ' ÁÐ×ÖĸתÊý×Ö
wsTarget.Cells(1, k + 1).Value = wsSource.Cells(1, colIndex).Value ' ÐÞÕý£ºÊ¹ÓÃÊý×ÖÁкÅ
Next k
firstSourceFound = True
targetRow = 2 ' Êý¾Ý´ÓµÚ2ÐпªÊ¼Ð´Èë
End If
' ½öµ±ÓÐÊý¾ÝÐÐʱ´¦Àí£¨Ìø¹ýÎÞÊý¾ÝµÄ¹¤×÷±í£©
If lastRow > 1 Then
' ¶¯Ì¬»ñÈ¡ÐèÒª¶ÁÈ¡µÄ×î´óÁУ¨±ÜÃâÊý×éÁÐÊý²»×㣩
maxCol = 0
For k = 0 To UBound(copyCols)
maxCol = Application.Max(maxCol, GetColumnIndex(copyCols(k)))
Next k
' ¶ÁÈ¡Ô´Êý¾Ýµ½Êý×飨º¬ËùÓÐÐèÒªµÄÁУ©
dataArr = wsSource.Range(wsSource.Cells(1, 1), wsSource.Cells(lastRow, maxCol)).Value
' KÁÐÊǵÚ11ÁУ¨¹Ì¶¨£©
dataTypeColumn = 11
' ±éÀúÔ´Êý¾ÝÐУ¨´ÓµÚ2ÐпªÊ¼£©
For j = 2 To UBound(dataArr, 1)
If UCase(Trim(dataArr(j, dataTypeColumn))) = UCase(Trim(dataType)) Then
' ¸´ÖÆÖ¸¶¨Áе½Ä¿±ê±í
For k = 0 To UBound(copyCols)
colIndex = GetColumnIndex(copyCols(k))
If colIndex <= UBound(dataArr, 2) Then ' È·±£ÁÐÓÐЧ
wsTarget.Cells(targetRow, k + 1).Value = dataArr(j, colIndex)
End If
Next k
targetRow = targetRow + 1
rowCount = rowCount + 1
End If
Next j
End If
End If
Next i
' Êä³öÍê³ÉÐÅÏ¢
Dim elapsed As Double
elapsed = Timer - startTime
If firstSourceFound Then
wsTarget.Range("E1").Value = dataType & "Êý¾Ý¸´ÖÆÍê³É! ºÄʱ: " & Format(elapsed, "0.00") & "Ãë"
wsTarget.Range("E1").Font.Color = RGB(0, 100, 0)
wsTarget.Columns("A:Z").AutoFit ' ×Ô¶¯µ÷ÕûÁпí
Else
wsTarget.Range("E1").Value = "δÕÒµ½ÓÐЧԴÊý¾Ý»ò¹¤×÷±í!"
wsTarget.Range("E1").Font.Color = RGB(255, 0, 0)
End If
MsgBox "³É¹¦¸´ÖÆÁË " & rowCount & " ÐÐ " & dataType & " Êý¾Ý!", vbInformation, "²Ù×÷Íê³É"
Cleanup:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Exit Sub
ErrorHandler:
MsgBox "´íÎó " & Err.Number & ": " & Err.Description & vbCrLf & _
"·¢ÉúÔÚµÚ " & Erl & " ÐÐ", vbCritical, "´íÎó"
Resume Cleanup
End Sub
' ¸¨Öúº¯Êý£ºÁÐ×ÖĸתÁкţ¨Èç"A"¡ú1£¬"AA"¡ú27£©
Function GetColumnIndex(colLetter As String) As Long
GetColumnIndex = Columns(colLetter & "1").Column
End Function
' ¸¨Öúº¯Êý£º»ñȡijһÁеÄ×îºóÊý¾ÝÐУ¨Èçws, "K"¡úKÁÐ×îºó·Ç¿ÕÐУ©
Function GetLastDataRow(ws As Worksheet, colLetter As String) As Long
With ws
GetLastDataRow = .Cells(.Rows.Count, colLetter).End(xlUp).Row
End With
End Function
' »ñÈ¡ÁÐË÷Òý£¨Ö§³Ö¶à×ÖĸÁÐÈçAA¡¢ABµÈ£©
Function GetColumnIndex(colLetter As String) As Long
Dim i As Long
Dim result As Long
Dim currentChar As String
' УÑéÊäÈëÊÇ·ñΪ¿Õ
If Len(colLetter) = 0 Then
MsgBox "´íÎó£ºÁÐ×Öĸ²»ÄÜΪ¿Õ£¡", vbExclamation
GetColumnIndex = 0
Exit Function
End If
' תΪ´óд²¢Ð£Ñéÿ¸ö×Ö·ûÊÇ·ñΪA-Z
colLetter = UCase(colLetter)
For i = 1 To Len(colLetter)
currentChar = Mid(colLetter, i, 1)
If currentChar < "A" Or currentChar > "Z" Then
MsgBox "´íÎó£ºÁÐ×Öĸ°üº¬ÎÞЧ×Ö·û '" & currentChar & "'£¨½öÖ§³ÖA-Z£©£¡", vbExclamation
GetColumnIndex = 0
Exit Function
End If
result = result * 26 + (Asc(currentChar) - 64) ' A=65¡ú1£¬B=66¡ú2...Z=90¡ú26
Next i
' УÑéÊÇ·ñ³¬¹ýExcel×î´óÁУ¨XFD=16384£©
If result > 16384 Then
MsgBox "´íÎó£ºÁÐ×Öĸ '" & colLetter & "' ³¬¹ýExcel×î´óÁÐXFD£¨16384ÁУ©£¡", vbExclamation
GetColumnIndex = 0
Exit Function
End If
GetColumnIndex = result
End Function
' »ñȡʵ¼ÊÊý¾ÝÐÐÊý£¨ÖÇÄܼì²â£©
Function GetLastDataRow(ws As Worksheet, col As String) As Long
Dim lastRow As Long
Dim tempRow As Long
' ·½·¨1£º°´Ö¸¶¨ÁвéÕÒ
lastRow = ws.Cells(ws.Rows.Count, col).End(xlUp).Row
' ·½·¨2£º°´AÁвéÕÒ£¨±¸Óã©
tempRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If tempRow > lastRow Then lastRow = tempRow
' ·½·¨3£º°´Õû¸ö¹¤×÷±í²éÕÒ£¨×îÖÕ±¸Óã©
tempRow = ws.UsedRange.Rows.Count
If tempRow > lastRow Then lastRow = tempRow
GetLastDataRow = lastRow
End Function
' °´Å¥¶ÔÓ¦µÄºê
Sub Copy_POE()
CopyData "POE"
End Sub
Sub Copy_MD()
CopyData "MD"
End Sub
Sub Copy_CB()
CopyData "CB"
End Sub
本文回顾了2007年中国IT行业的发展状况，强调了中国经济的蓬勃发展背景下信息技术产业的重要性。
扫一扫
4197
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
