作者:乔山办公网日期:
返回目录:excel表格制作
按alt+f11进vba,右键在thisworkbook上插入模块,贴入以下代7a686964616fe78988e69d83337码
Option Explicit
Sub 整行写入()
Dim i As Integer, c As Integer
Dim str As String
c = ActiveCell.Row
For i = 1 To Range("IV" & c).End(1).Column
str = str & Cells(c, i).Value & IIf(i = Range("IV" & c).End(1).Column, "", ",")
Next i
'MsgBox str
Dim fs, fi As Object, fn As String
fn = ThisWorkbook.Path & "\" & c & ".txt" '如果要改成固定的文件名,可以改成比如fn="c:\test.txt"
Set fs = CreateObject("Scripting.FileSystemObject")
Set fi = fs.createtextfile(fn, True)
fi.write str
fi.Close
End Sub
然后在你的宏下为这个宏指定一个快捷键,按下后,它会将一行的内容写入你的xls所在目录的行数.txt里。
代码如下:
Private Sub ComboBox1_Change()
Sheet2.Cells(1, 1).Value = ComboBox1.Value
Dim i As Integer
i = 2
Do While Not i > 100000
If Sheet3.Cells(i, 1) = "" Then
Sheet3.Cells(i, 1).Value = Sheet2.Cells(1, 1).Value
Sheet3.Cells(i, 2).Value = Now()
GoTo lastline
Else
i = i + 1
End If
Loop
lastline:
MsgBox "done!"
End Sub
效果是:
sheet1里面有copycombobox1, 用户选某值,
sheet2的第一行变成百用户选的值,
sheet3的第一非空度行追加记录用户值。
这里假设表1的数据在A:H列
用函数可在表二的B1输入公式如下,公式右拉完成(A1输入序号)
=VLOOKUP($A1,Sheet1!7a64e59b9ee7ad94335$A:$H,COLUMN(),0)
如果用VBA实现,不管表1数据有几列,整行复制。代码如下:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Count = 1 Then
With Sheets("sheet1")
For x = 1 To .Range("A65536").End(xlUp).Row
If .Cells(x, 1) = Target.Value Then
.Rows(x).Copy Target.Rows
Exit Sub
End If
Next x
End With
End If
End Sub
把这代码放到表2的代码编辑框中即可实现你要的结果