- ©«¤l
- 63
- ¥DÃD
- 32
- ºëµØ
- 0
- ¿n¤À
- 125
- ÂI¦W
- 0
- §@·~¨t²Î
- WIn10
- ³nÅ骩¥»
- OFFICE2013
- ¾\ŪÅv
- 20
- µù¥U®É¶¡
- 2018-7-19
- ³Ì«áµn¿ý
- 2022-8-11
|
[µo°Ý] ½Ð±Ð±NVBA ½X¹B¥Î¦bVB¤º°ÝÃD¡C
§Ú·Q§â¥H¤U2²ÕVBA ªº¥N½X§Ë¶iVB ªº«ö¶s¤º,Åý§Ú«ö¤UVBªº«ö¶s´N¥i¥H°õ¦æVBA ªº°Ê§@,¦ý§Ú¦bVB¶}¤Fªí³æ«á,¦A³]©w¤F«ö¶s,¦P®É¤]¿ï¾Ü¤F¥LªºExcel Library 16 °Ñ¦Ò¡C
¦ý¬O§Ú¦bÂI¤F«ö¶s«á,n©w¸q¥L¬OExcel ªºµ{§Ç,¥L¥u¬O¤Ï¥Õ¤F,¦n¹³¤£¦æ,©Ò¥H·Q½Ð±Ð¤j®a,¦pªG§Ú»Ýn§âVBAªº·½½X§Ë¶iVBªº«ö¶s¤ºÀ³¸Ó®t¤°»ò,²{ªþ¤W¥N½Xµ¹¤j®a«ü±Ð¤@¤U,«D±`·PÁ¡C
VB¤ºªº±¡ªp¬O¥H¤U:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace WindowsFormsApp5
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
}
}
}
§Úªº2²Õ쥻VBA ·½½X¡C
²Õ1:
Sub unite_data()
Dim BookNum As Integer, rowNum As Integer, currentRow As Integer
Dim i As Integer
Dim j As Integer
Dim BookName As String
currentRow = 2
BookNum = Workbooks.Count
'MsgBox BookNum
For i = 1 To BookNum
BookName = Workbooks(i).Name
If BookName <> "Data.xlsx" Then
For j = 1 To 1
Workbooks(BookName).Activate
rowNum = Workbooks(BookName).Worksheets("Sheet1").UsedRange.Rows.Count
'MsgBox rowNum
If (rowNum - 1 >= 1 And Workbooks(BookName).Worksheets("Sheet1").Cells(2, 1).Value <> "") Then
Workbooks(BookName).Worksheets("Sheet1").Rows("15:" & rowNum).Select
Selection.Copy
Windows("Data.xlsx").Activate
Rows(currentRow).Select
ActiveSheet.Paste
currentRow = currentRow + (rowNum - 14)
Windows(BookName).Activate
End If
Next j
End If
Next i
Windows("Data.xlsx").Activate
Range("K2:K14").Value = "1"
Range("L2").Formula = "=J2"
[L2].Copy
With Range("L3:L" & [A65536].End(xlUp).Row).PasteSpecial
Range("M2").Formula = "=J2"
[M2].Copy
With Range("M3:M" & [A65536].End(xlUp).Row).PasteSpecial
End With
End With
End Sub
²Õ2:
Sub ¥¨¶°1()
Application.ScreenUpdating = False
LineNo = ActiveSheet.UsedRange.Rows.Count - 1
Sheets.Add.Name = "rr"
Sheets("AA").Rows(1).Copy Sheets("rr").Rows(1)
rrLow = 1
For i = 1 To LineNo
If Sheets("AA").Cells(i + 1, 7) > 1000 Then
n = WorksheetFunction.RoundUp(Sheets("AA").Cells(i + 1, 7) / 1000, 0)
For k = 1 To n
rrLow = rrLow + 1
Sheets("AA").Rows(i + 1).Copy Sheets("rr").Rows(rrLow)
If k <> n Then
Sheets("rr").Cells(rrLow, 7) = 1000
Else
Sheets("rr").Cells(rrLow, 7) = Sheets("AA").Cells(i + 1, 7) - 1000 * (k - 1)
End If
Sheets("rr").Cells(rrLow, 8) = k
Sheets("rr").Cells(rrLow, 9) = n
Next k
Else
rrLow = rrLow + 1
Sheets("AA").Rows(i + 1).Copy Sheets("rr").Rows(rrLow)
End If
Next i
End Sub |
|