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

Long_III

就是说，你不要在这里跟贴提问，你可以自己新建一个帖子，在里面详细说明你的问题，并上传附件加以说明，这样大家才好帮你写代码

这个帖子是专门用来解释代码的，不是用来提问的

andysky

Worksheets("Sheet1").UsedRange.Columns("A:C").Calculate

以上代码重算Columns("A:C").其它工作表内容不会重算?

也就是说这种方式比F9或者SHIFT+F9更有灵活性,书上说F9无法针对工作表某个区域是不精确的说法?

女强人_1234

Sub save1()
  Dim i As Integer
  Dim j As Integer
  Dim k As Integer
   'If store <> 12 Then
   'Dim msg As Integer
     ' msg = MsgBox("是否存盘?", 4)
   'If msg = 6 Then
closefilename = Application.GetSaveAsFilename(fileFilter:="TEXT 文件(*.txt),*.txt,所有文件(*.*),*.*")

  If closefilename <> False Then
     Open closefilename For Output As #1
    For j = 1 To 4
      For i = 1 To 5
         Write #1, Sheets("Sheet2").Cells(i, j):
      Next i
    Next j
   
   
   Close #1
  
   End If
  
End Sub

Sub Load1()
  Dim i As Integer
  Dim j As Integer
 
 openfilename = Application.GetOpenFilename("TEXT 文件 (*.txt),*.txt,所有文件(*.*),*.*")
 If openfilename <> False Then
    Open openfilename For Input As #1
      For i = 1 To 20
         j = (i - 1) Mod 5
         k = Fix((i - 1) / 5)
         Input #1, t:
         Sheets("Sheet2").Cells(1 + j, 1 + k) = t
      Next i
 
  Close #1
 End If
 
End Sub

解释一下代码

[此贴子已经被作者于2007-1-20 12:50:17编辑过]

303hd

QUOTE:

以下是引用Long_III在2006-11-9 15:13:18的发言：

代码主要在于Split的用法，你可以找找帮助或看看相关资料，我这里大致说下

如 s ＝ "a,b,c"

bn = split(s,",") '就是说把s变量，按,分开成一个数组。这时bn就是一个数组，从0开始，具体为：

bn(0) = "a",bn(1) = "b",bn(2) = "c"

例子中是按“编号”来分开的Bh = Split(Cells(n, 2), "编号")，如单元格里为“a编号1”，则按上面的分开之后就是bn(0) = "a",bn(1) = "1"

谢谢兰老师，但我还有一句不明白(红色这句):

 

Sub Me_Micro()
    Dim t_Str$
    Dim i%
    Dim Bh() As String
    For n = 2 To [B65536].End(xlUp).Row
        Bh = Split(Cells(n, 2), "编号")
        If UBound(Bh) > 0 Then
            For i = 1 To UBound(Bh)
                t_Str = t_Str & i & Split(Cells(n, 2), "编号")(i)
            Next
            Cells(n, 2) = t_Str
        End If
    Next
End Sub

请百忙中给个解释，

[此贴子已经被作者于2007-1-21 10:23:58编辑过]

chenxi2911

Sub Macro1()
Dim n As Long
n = Sheets("汇总表").[a65536].End(xlUp).Row
Sheets("汇总表").[W3].Resize(n - 2, 1) = "=SUMPRODUCT(1*(输入表!R3C2:R" & n & "C2=RC2)*(输入表!R3C3:R" & n & "C3=RC3)*(输入表!R3C4:R" & n & "C4=RC4)*(输入表!R3C5:R" & n & "C5=RC5)*(输入表!R3C17:R" & n & "C17>0))"
End Sub

麻烦老师了。说得细点

gzxw

Function dx(r1)
dx = Application.WorksheetFunction.VLookup(r1,arr("","";"吖","A";"八","B";"嚓","C";"咑","D";"鵽","E";"发","F";"猤","G";"铪","H";"夻","J";"咔","K";"垃","L";"嘸","M";"旀","N";"噢","O";"妑","P";"七","Q";"囕","R";"仨","S";"他","T";"屲","W";"夕","X";"丫","Y";"帀","Z"),2)
End Function

可是在编写的时候,老是提醒: "编译错误,缺少列表分隔符"

我主要的意思是请教老师,在Excel的VBA中怎样设置常量数量,能否以此来详细说明一下.

 

dxy27

Sub FTY()

Application.ScreenUpdating = False
On Error Resume Next
Dim arr, arr2, i As Long, j As Long, x As New Collection
arr = Sheets("Detail").UsedRange
arr2 = Sheets("FTY & LSP Pick List").[a2].Resize([a1].End(xlDown).Row - 1, 1)
For i = 1 To UBound(arr2)
x.Add i, CStr(arr2(i, 1))
Next
ReDim arr2(1 To UBound(arr2), 1 To 8)

For i = 2 To UBound(arr)

If Len(arr(i, 23)) > 0 Then
arr2(x(CStr(arr(i, 21))), 1) = arr2(x(CStr(arr(i, 21))), 1) + 1
arr2(x(CStr(arr(i, 21))), 3) = arr2(x(CStr(arr(i, 21))), 3) + arr(i, 12)
If arr(i, 12) > 0.5 Then arr2(x(CStr(arr(i, 21))), 2) = arr2(x(CStr(arr(i, 21))), 2) + 1

If arr(i, 7) = "LSP" Then
arr2(x(CStr(arr(i, 21))), 5) = arr2(x(CStr(arr(i, 21))), 5) + 1
If arr(i, 12) > 24 Then
arr2(x(CStr(arr(i, 21))), 6) = arr2(x(CStr(arr(i, 21))), 6) + 1
arr2(x(CStr(arr(i, 21))), 7) = arr2(x(CStr(arr(i, 21))), 7) + arr(i, 12)
End If
End If
End If
Next
[C2].Resize(UBound(arr2), 8) = arr2
[F2].Resize(UBound(arr2), 1) = "=RC[-1]/RC[-3]"
[J2].Resize(UBound(arr2), 1) = "=RC[-1]/RC[-3]"
[C2].Resize(UBound(arr2), 8) = [C2].Resize(UBound(arr2), 8).Value
[E2].Resize(UBound(arr2), 1) = "=100*RC[-1]/RC[-2] &""%"""
[I2].Resize(UBound(arr2), 1) = "=100*RC[-1]/RC[-2] &""%"""
[C2].Resize(UBound(arr2), 8).SpecialCells(4) = 0
[C2].Resize(UBound(arr2), 8) = [C2].Resize(UBound(arr2), 8).Value
[C2].Resize(UBound(arr2), 8).Replace "#DIV/0!", "0"
Application.ScreenUpdating = True

End Sub

i3uWk5IO.rar (153.57 KB, 下载次数: 2)

2007-1-22 09:40 上传

点击文件名下载附件
带你入门VBA系列之：不懂的代码快点贴过来

alexlaupeng

偶是新人一个

来学习的

xyq123456

If Sheet2.Range("a" & i1).Value >= Sheet1.[c1].Value And Sheet2.Range("a" & i1).Value <= Sheet1.[d1].Value And Sheet2.Range("H" & i1) = Sheet1.[f1] Or Sheet2.Range("H" & i1) = Sheet1.[g1] Then

烦请解释

cjglb

  On Error Resume Next
  Dim CNN As New ADODB.Connection
  Dim RST As New ADODB.Recordset
  Dim SQL As String
  Dim Jcount As Integer
     CNN.Open "Driver={SYBASE SYSTEM 11};Srvr='dataserver'; UID='sa';PWD='sa';Database='ywtz';"
     SQL = "select * from bm_a005 where bm='01'"
     RST.Open SQL, CNN, adOpenKeyset, adLockOptimistic
     If RST.EOF = False Then
       Jcount = RST.RecordCount
       For J = 1 To Jcount
         Range("B1").Value = RST.Fields("bm")
         Range("B2").Value = RST.Fields("mc")
         RST.MoveNext
       Next J
       Set RST = Nothing
       CNN.Close
     Else
       Set RST = Nothing
       CNN.Close
     End If