使用Excel宏实现数据自动处理

最新推荐文章于 2024-07-15 21:31:41 发布

nksmiles

最新推荐文章于 2024-07-15 21:31:41 发布

阅读量1.4w

点赞数 2

分类专栏： Excel VBA 文章标签： excel header vba input assembly insert

本文链接：https://blog.csdn.net/nksmiles/article/details/7418842

版权

本文介绍了如何利用Excel宏提高工作效率，通过录制宏并优化代码，处理两种不同格式的数据源，实现了数据处理的自动化。

摘要由CSDN通过智能技术生成

因为工作关系，需要对一些数据进行一些重复性的处理工作，为了提高效率，录制宏进行重复处理非常有效，但录制的宏有大量无效代码，而且需要处理的数据源有两种不同的格式，通过宏录制和自己的处理，可以很好的解决这些问题。

Option Explicit

Sub SpareCatalogueFormat()
    Dim sMessage As String
    Dim answer As Integer
    
    On Error GoTo ErrHandler01
    
    sMessage = "Do you want to convert data to SPARE PARTS CATALOGUE format?" & vbCrLf & vbCrLf & _
               "To do that you must paste data from CADIM to cells from ""A1""." & vbCrLf & vbCrLf & _
               "Press ""OK"" to continue, and Press ""Cancel"" to exit."
    answer = MsgBox(sMessage, vbOKCancel + vbQuestion, "User Question:")
    If answer = vbOK Then
        '''do nothing
    ElseIf answer = vbCancel Then
        End
    End If


    ''' Check the minumium value of column A to identify this is BoM list
    ''' from Scope of Supply or Assembly and Components. For SoS, column A
    ''' is DWG Pos. No., so it starts from 1, for AaC, column A is Position
    ''' and it starts from 10 or a even bigger number.
    Range("S1").Cells(1, 1) = "=MIN(A:A)"
    If Range("S1").Cells(1, 1).Value = 1 Then
        Call Format1(True)
    ElseIf Range("S1").Cells(1, 1).Value > 1 Then
        Call Format2(True)
    Else
        MsgBox "Unkown error! Please check the VBA code!", vbCritical
        End
    End If
    
    Exit Sub
    
ErrHandler01:
    sMessage = "Ooops! Something didn't work quite correctly." & vbCrLf & vbCrLf & _
        "Error number: " & Err.Number & vbCrLf & _
        "Error message: " & Err.Description & vbCrLf & vbCrLf & _
        "Please check the VBA code!"
    MsgBox sMessage, vbOKOnly + vbCritical, "iFE-VICTAL