带你入门VBA系列之：不懂的代码快点贴过来

shewoyu

Sub InsertHeader()
    Dim r As Long, LastRow As Long
    Application.ScreenUpdating = False
    LastRow = Sheet1.Range("A65536").End(xlUp).Row
    r = 3
    Do While r <= LastRow
         Sheet1.Rows(1).Copy
         Sheet1.Rows(r).Insert Shift:=xlDown
         LastRow = LastRow + 1
         r = r + 2
    Loop
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
   
End Sub

Sub DeleteHeader()
    Dim r As Long, LastRow As Long
    Application.ScreenUpdating = False
    LastRow = Sheet1.Range("A65536").End(xlUp).Row
    r = 3
    Do While r <= LastRow
        Sheet1.Rows(r).Delete
        r = r + 1
        LastRow = LastRow - 1
    Loop
    Application.ScreenUpdating = True
End Sub

请帮忙解释一下〉

看来老师很忙，没时间解释了。

[此贴子已经被作者于2006-6-22 11:02:28编辑过]

wcngakgl1972

老师你好,我是新手,下面代码能给我加个注释吗,

Private Sub CommandButton1_Click()
    Sheet2.Rows("2:1000").Delete               '
    Set X = CreateObject("ADODB.Connection")    '
    X.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName                       '
    If ComboBox5.Value <> "" And ComboBox6.Value = "" Then
        Sql = "select * from [数据$] where 柜号 like'" & ComboBox1.Value & "%' and 姓名 like'" & ComboBox2.Value & "%' and 编号 like'" & ComboBox3.Value & "%' and 类别 like'" & ComboBox4.Value & "%' and 日期>=#" & CDate(ComboBox5.Value) & "#"
    ElseIf ComboBox5.Value = "" And ComboBox6.Value <> "" Then
        Sql = "select * from [数据$] where 柜号 like'" & ComboBox1.Value & "%' and 姓名 like'" & ComboBox2.Value & "%' and 编号 like'" & ComboBox3.Value & "%' and 类别 like'" & ComboBox4.Value & "%' and 日期<=#" & CDate(ComboBox6.Value) & "#"
    ElseIf ComboBox5.Value <> "" And ComboBox6.Value <> "" Then
        Sql = "select * from [数据$] where 柜号 like'" & ComboBox1.Value & "%' and 姓名 like'" & ComboBox2.Value & "%' and 编号 like'" & ComboBox3.Value & "%' and 类别 like'" & ComboBox4.Value & "%' and 日期>=#" & CDate(ComboBox5.Value) & "# and 日期<=#" & CDate(ComboBox6.Value) & "#"
    Else
        Sql = "select * from [数据$] where 柜号 like'" & ComboBox1.Value & "%' and 姓名 like'" & ComboBox2.Value & "%' and 编号 like'" & ComboBox3.Value & "%' and 类别 like'" & ComboBox4.Value & "%'"
    End If
    Set yy = X.Execute(Sql)
    Sheet2.[a2].CopyFromRecordset yy
    Set yy = Nothing: Set X = Nothing
    Unload Me
    Sheet2.Select
End Sub

韩学明

这是放在Sheet1中代码的某一段：

If Target.Address = "$D$1" Then

其中$D$1为一个可以改变内容的下拉框，改变这个单元格的内容可触发事件执行代码；现在想把这个单元格移动到Sheet2的同样位置来触发事件，而原代码仍在Sheet1中，怎么改这句？

注：

把代码拷贝到Sheet2的代码窗口中或THISWORKBOOK代码窗口中

[此贴子已经被兰色幻想于2006-6-23 8:05:21编辑过]

giyear

TextToColumns 方法有什么作用？不理解VBA帮助的解释：“将包含文本的一列单元格分解为若干列。”

比如这个：

M_PN_C值为2

ActiveSheet.Columns(M_PN_C).TextToColumns Destination:=Range(Chr(64 + M_PN_C) & "1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 2), TrailingMinusNumbers:=True

我不明白为啥同样一列还要分解一次，目的是啥呀？

[此贴子已经被作者于2006-6-22 13:16:44编辑过]

lingzi8675

Private Sub CommandButton1_Click()
Dim sh As Worksheet
Dim rg As Range
Dim c As Range
Range("a4:aw" & [a65536].End(xlUp).Row + 2).ClearContents
For Each sh In Sheets
 If sh.Name <> "汇总" Then
     t = [a65536].End(xlUp).Row
     Cells(t + 1, 1) = sh.Name
     Cells(t + 1, 2) = sh.[d4]
     Cells(t + 1, 3) = sh.[g7]

   Set rg = Union(sh.Range("c12:c34"), sh.Range("i12:i34"))
   For Each c In rg.Cells
     If c.Value <> "" Then
     s = Rows("3:3").Find(c.Value, lookat:=xlPart).Column
     Cells(t + 1, s) = c.Offset(0, 1).Value
     End If
   Next
 End If
Next
End Sub

烟雨任平生

我在立即窗口中输入以下代码：

?thisworkbook.Names("data").RefersTo
=Sheet1!$A$1:$A$9
?thisworkbook.Names("data").RefersToLocal
=Sheet1!$A$1:$A$9

请问 .RefersTo 和 .RefersToLocal 有什么区别?

另外 Selection.NumberFormat 和 Selection.NumberFormatLocal 有什么区别?

[此贴子已经被作者于2006-6-25 0:11:40编辑过]

烟雨任平生

请兰版帮忙解释以下语句:

Private Sub UserForm_Initialize()
    Dim IStyle As Long, Hwnd As Long
    If Val(Application.Version) < 9 Then
        Hwnd = FindWindow("ThunderXFrame", Me.Caption)    
    Else
        Hwnd = FindWindow("ThunderDFrame", Me.Caption)    

    End If
    IStyle = GetWindowLong(Hwnd, GWL_STYLE)
    SetWindowLong Hwnd, GWL_STYLE, IStyle And Not WS_OVERLAPPEDWINDOW
    DrawMenuBar Hwnd
End Sub

谢谢了 !

pyglz

如何编制打印区域VBA代码.请指教,谢谢!

liling_124

想写一个过程实现工作表的隐藏(只留最后一个不隐藏)

我写的代码如下:

Sub hidesheets()
Dim n As Integer
Dim i As Integer
n = ThisWorkbook.Sheets.Count
For i = 1 To n - 1
ThisWorkbook.Sheets(n).Visible = False
Next
End Sub
怎么每执行都隐藏最后一个工作表?

哪里出问题了?

青山依旧

QUOTE:

以下是引用liling_124在2006-6-26 0:16:24的发言：

想写一个过程实现工作表的隐藏(只留最后一个不隐藏)

我写的代码如下:

Sub hidesheets()
Dim n As Integer
Dim i As Integer
n = ThisWorkbook.Sheets.Count
For i = 1 To n - 1
ThisWorkbook.Sheets(n).Visible = False
Next
End Sub
怎么每执行都隐藏最后一个工作表?

哪里出问题了?

ThisWorkbook.Sheets(i).Visible = False