This code executed perfectly for 2 years, now returning errors. Debug shows the breakpoint as below. ( If Len(cel) > 0 Then)

Viewed 44
Sub TranslateAllCells()
    Dim rng As Range
    Dim translateFrom As String
    Dim translateTo As String
    Dim getParam As String, trans As String, objHTTP As Object, URL As String
    Dim cel As Range
    translateFrom = Range("L4").Value
    translateTo = Range("L5").Value
    lastColumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
    lastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
    Set rng = Application.Range(Cells(11, 1), Cells(lastRow, lastColumn))
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    For Each cel In rng.Cells
      With cel
        If Len(cel) > 0 Then
          getParam = cel.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"".*?""t0"".*?>(.+?)</div>")
            cel.Value = Clean(trans)
          Else
            cel.Value = CVErr(xlErrValue)
          End If
        End If
      End With
    Next cel
End Sub
0 Answers
Related