时间戳

首页 > 解决方案 > 忽略隐藏行并在 10 个空单元格后停止宏

excel - 忽略隐藏行并在 10 个空单元格后停止宏

问题描述

我正在使用以下宏通过谷歌翻译翻译选定的单元格。

它就像一个魅力，但我也希望它忽略隐藏的行并确保它在 10 个空单元格后停止。

此外，由于某种原因，我实际上想保留已翻译单元格中的换行符。

在此先感谢您的帮助。

Sub Translate()
    Dim getParam As String, trans As String, translateFrom As String, translateTo As String
    translateFrom = "en"
    translateTo = "fr"
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    Dim cell As Range
    Dim blanks As Long

    For Each cell In Selection.SpecialCells(xlCellTypeVisible)

    If blanks > 10 Then Exit For

    If cell.Value = "" Then
        blanks = blanks + 1
    Else

        getParam = ConvertToGet(cell.Value)
        URL = "https://translate.google.pl/m?hl=" & translateFrom & "&sl=" & translateFrom & "&tl=" & translateTo & "&ie=UTF-8&prev=_m&q=" & getParam
        objHTTP.Open "GET", URL, False
        objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
        objHTTP.send ("")

        If InStr(objHTTP.responseText, "div dir=""ltr""") > 0 Then
            trans = RegexExecute(objHTTP.responseText, "div[^""]*?""ltr"".*?>(.+?)</div>")
            cell.Value = Clean(trans)
            cell.Interior.Color = RGB(255, 0, 0)
            cell.Font.Color = Black
            Cells.ClearComments
        End If

    End If

Next cell
End Sub

标签： excelvba

解决方案

以下是一些您可以使用的满足您的两个条件的示例代码：

  1. 仅循环通过可见单元格( xlCellTypeVisible)
  2. 循环期间找到 10 个空白单元格后的停止代码( Exit For)

请注意，空白必须可见才能在此处考虑

您的操作语句将代替Debug.Print rng.Value下面提供的示例代码

Sub Shelter_In_Place()

Dim cell As Range
Dim blanks As Long

For Each cell In Selection.SpecialCells(xlCellTypeVisible)

    If blanks > 10 Then Exit For
    'Depending on your actual code, you may need to use 'Exit Sub' instead

    If cell.Value = "" Then
        blanks = blanks + 1
    Else

        getParam = ConvertToGet(cell.Value)
        URL = "https://translate.google.pl/m?hl=" & translateFrom & "&sl=" & translateFrom & "&tl=" & translateTo & "&ie=UTF-8&prev=_m&q=" & getParam
        objHTTP.Open "GET", URL, False
        objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
        objHTTP.send ("")

        If InStr(objHTTP.responseText, "div dir=""ltr""") > 0 Then
            trans = RegexExecute(objHTTP.responseText, "div[^""]*?""ltr"".*?>(.+?)</div>")
            cell.Value = Clean(trans)
            cell.Interior.Color = RGB(255, 0, 0)
            cell.Font.Color = Black
            Cells.ClearComments
        End If

    End If

Next cell


End Sub

推荐阅读