灿烂的阳光,苦涩的生活,认真做,你能行!

vba练习

上一篇 / 下一篇  2009-09-14 15:23:18 / 个人分类:脚本

1编写VBA代码,随机生成050之间的100个数字,放到一个sheet中;

Sub RndNumber()

  Dim RndNumber, TempArray(99), i As Integer, a As Integer

 

  Randomize (Timer)

 

  For i = 0 To 50

      TempArray(i) = i

  Next i

  For i = 99 To 0 Step -1

     RndNumber = Int(i * Rnd)

     Cells(100 - i, 1) = TempArray(RndNumber) + 1

     TempArray(RndNumber) = TempArray(i)

  Next i

End Sub

2.编写VBA代码,将上面生成的数据中隔行抽取出来,分别放到另外两个sheet中;Public Sub Rowscopy()

Dim nRows As Long

  Dim i As Long

  With Worksheets("sheet1")

                  nRows = .UsedRange.Rows.count

                  For i = nRows To 2 Step -1

                          If i Mod 2 = Odd Then

  Workbooks("luojing.xls").Sheets("sheet1").Rows(i).copy Workbooks("luojing.xls").Sheets("sheet2").Cells(i / 2, 1)

                          End If

                  Next

          End With

End Sub

3. .只用excel公式统计100个数字中等于30的个数

Public Sub count()

Dim a     As Range

 Dim nRows As Long

 Set a = Sheet1.Range("A1:A100")

  Dim i As Long

  Dim j As Long

  With Worksheets("sheet1")

                  nRows = .UsedRange.Rows.count

                  For i = nRows To 1 Step -1

                          If a.Cells(i, 1).Value = 30 Then

                  j = j + 1

                          End If

                  Next

  End With

         

   Sheets("sheet3").Cells(2, 1).Value = j

 

End Sub

 

 


TAG:

 

评分:0

我来说两句

Open Toolbar