excel 常用函数

excel 函数：
dir /b > pic.xls
SUBSTITUTE(A4,"M2","M3") 替换功能
CONCATENATE 连接功能 连接多少都行
ren 原文件名 新文件名 重命名文件名 bat 命令 修改名字

 

一个简单宏的写法：

Sub general_zuheji()
   
    end_row = Cells(2, 1).Value
    file_name = Cells(2, 2).Value
   
    ' general_id = 20271
   
    file_path = "d://" & file_name & ".sql"
   
    Open file_path For Output As #1
   
    sqlSQL = "INSERT INTO `t_general_coalition_group_info` (`group_skill_id`, `general_ids`, `group_type`, `group_value`) values "

    Print #1, sqlSQL
   
    For start_row = 4 To end_row
           
            skill_1 = Cells(start_row, 3).Value
            general_names = Split(Cells(start_row, 2).Value, ",")
            general_ids = ""
            arr_length = UBound(general_names) - LBound(general_names)
            Dim general_arr() As String
            ReDim general_arr(arr_length)
            For g = 0 To arr_length
                For i = 2 To 1030
               
                    If (general_names(g) = Cells(i, 5).Value) Then
                       
                        general_arr(g) = Cells(i, 4).Value
                    Else
                   
                    End If
                   
                Next i
            Next g
           
            For m = 0 To UBound(general_arr) - LBound(general_arr)
                If m = UBound(general_arr) Then
                   general_ids = general_ids & general_arr(m)
                Else
                   general_ids = general_ids & general_arr(m) & ","
                End If
               
               
            Next m
               
                If start_row = end_row Then
                   sqlSQL = "(" & Split(Cells(start_row, 1).Value, ":")(0) & ", '" & general_ids & "', " & skill_1 & ",'');"
                Else
                    sqlSQL = "(" & Split(Cells(start_row, 1).Value, ":")(0) & ", '" & general_ids & "', " & skill_1 & ",''),"
                End If
               
           
                Print #1, sqlSQL
          
           
    Next start_row
   
    Close #1
   
  
End Sub

 

2. 查找单元格数据

    Sheets(Sheet13.Name).Columns("C").Find("玉石").Row

 dim goodcell as Range

  Set goodCell = Sheets(Sheet13.Name).Columns("C").Find(good_name) //查找变量，需要加上 set 设置成对象

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝结束 ＝＝＝＝＝＝＝＝＝＝＝＝＝