代码之家 › 专栏 › 技术社区 › anon

Excel VBA:查找当前行号并根据条件插入行

macros excel vba

anon · 技术社区 · 9 年前

我目前正在编写一个宏,它逐行比较Excel和其他程序之间的值。99%的情况下,出现差异是因为从未添加交易。因此,当这个宏比较这些值时,在发现差异时,我希望它添加一个新的“行”(但是,不是 全部的 行,仅从 A_:K_ ,其中 _ 活动单元格的行号)。这将允许我简单地进入Excel,输入事务,然后在宏上按OK并继续。我的宏实际上非常简单和短&说到点子上,所以我可以继续在这里发布整个事情,以便更好地了解正在发生的事情。我不是在Excel的VBA中做这件事,我是在其他程序的VBA里做这件事情,并且 appXL 是Excel的函数对象:

Function appXL As Object
    Set appXL = GetObject(, "Excel.Application")
End Function

主要宏:

Sub FeeBrdVerifier
    On Error Resume Next
    With InitSession
        Dim iComm As Currency   ' Compare this with Excel's data
        Dim sComm As String     ' Needed string to allow app to stop at end of report
        Dim xL As Currency      ' Compare this with Host's data
        Dim Counter As Byte     ' Counter for the loop (need to do a new page)
        Dim r As Byte           ' Row # on the page
        Dim Page As Byte

        Page = 1
        Debug.Print "Page # " & Page & vbNewLine & "========="
        Counter = 0     ' 19 unique lines in transaction board per page

        appXL.Workbooks("2016 FEE BOARD.xlsx").Activate
        appXL.Range("J2").Select    'Starting point of the transaction amounts
        r = 3

        Do
            Counter = Counter + 1
            .Copy 69, r, 78, r      ' This copies text from host app, consider it a 'cell'
            sComm = Clipboard
            iComm = CCur(sComm)
            xL = appXL.ActiveCell.Value
            appXL.ActiveCell.Offset("1", "0").Select
            Debug.Print "# [" & Format(Counter,"00") & "].. sComm = [" & sComm & "] ... Excel Value = [" & xL & "]"
            If iComm <> xL Then
                .SetSelection 0, r, 80, r   'Highlights the row in host app that doesnt match
    '           appXL.      '<<<< where I need assistance, insert line and shift down
                MsgBox "Did not match..."
                .ClearSelection     'Get rid of highlight after msgbox cleared
            End If
            r = r + 1               ' This allows the loop to copy the next line
            If Counter = 19 Then
                Page = Page + 1
                Counter = 0
                .Output E           ' E is a function I use for the Return Key
                Sleep 250           ' Waiting for next page to load
                r = 3               ' On a new page now, go back to the top
                Debug.Print vbNewLine & "Page # " & Page & vbNewLine & "========="
            End If
        Loop Until sComm = ""   ' Reached last transaction
    End With
End Sub

所以,概括一下,如果活动细胞 J495 ,手动执行的操作是选择 A495:K495 ,在选择上单击鼠标右键,然后单击 Insert ,然后单击 Shift Cells Down 现在我只需要这是自动化的。最终,我还计划自动填写缺失的数据,但这部分是第一步(否则我将继续自己手动填写)。

作为附加奖金 ,如果有人能解释一下如何获取插入行的当前行号,以便我可以将此行号添加到调试器窗口,我将不胜感激,但如果必要的话,我可以不使用行号

2 回复 | 直到 9 年前

cyboashu 9 年前

这应该对你想做的事情有效

  .SetSelection 0, r, 80, r 
    appXL.ActiveSheet.Range(appXL.cells(appXL.activecell.Row,1),appXL.cells(appXL.activecell.Row,11)).Insert Shift:=xlDown
    MsgBox "Did not match..." & " the current row number is : " & appXL.ActiveCell.Row()



  'Then move to next row to continue the loop
    appXL.ActiveCell.Offset(1)

Comintern 9 年前

根据上面的评论,我会接受@cyboashu的答案,并稍加考虑。从使用 Active* 对象和使用 Activate 和 Select 将使代码更易于维护和扩展。这里有一个重构示例,可以使用绝对引用来代替(给你一个想法)。这显然是未经测试的-我甚至不知道它在什么应用程序下运行-P

Sub FeeBrdVerifier()
    On Error Resume Next
    With InitSession
        Dim iComm As Currency   ' Compare this with Excel's data
        Dim sComm As String     ' Needed string to allow app to stop at end of report
        Dim xL As Currency      ' Compare this with Host's data
        Dim Counter As Byte     ' Counter for the loop (need to do a new page)
        Dim r As Byte           ' Row # on the page
        Dim Page As Byte

        Page = 1
        Debug.Print "Page # " & Page & vbNewLine & "========="
        Counter = 0     ' 19 unique lines in transaction board per page

        'Get a reference to the ActiveSheet
        Dim sheet As Object
        Set sheet = appXL.Workbooks("2016 FEE BOARD.xlsx").ActiveSheet

        r = 3

        Dim currentRow As Long
        currentRow = 2 'Starting point of the transaction amounts in Column J (ordinal is 10)
        Do
            Counter = Counter + 1
            .Copy 69, r, 78, r      ' This copies text from host app, consider it a 'cell'
            sComm = Clipboard
            iComm = CCur(sComm)
            xL = sheet.Cells(currentRow, 10).Value
            currentRow = currentRow + 1
            Debug.Print "# [" & Format(Counter, "00") & "].. sComm = [" & sComm & "] ... Excel Value = [" & xL & "]"
            If iComm <> xL Then
                .SetSelection 0, r, 80, r   'Highlights the row in host app that doesnt match
                sheet.Range(sheet.Cells(currentRow, 1), sheet.Cells(currentRow, 11)).Insert
                MsgBox "Did not match..."
                .ClearSelection     'Get rid of highlight after msgbox cleared
            End If
            r = r + 1               ' This allows the loop to copy the next line
            If Counter = 19 Then
                Page = Page + 1
                Counter = 0
                .Output E           ' E is a function I use for the Return Key
                Sleep 250           ' Waiting for next page to load
                r = 3               ' On a new page now, go back to the top
                Debug.Print vbNewLine & "Page # " & Page & vbNewLine & "========="
            End If
        Loop Until sComm = vbNullString   ' Reached last transaction
    End With
End Sub