Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range On Error Resume Next For Each r In Target.Rows With r.Cells(1, 1) If Not Intersect([$X$13:$X$18], .Cells) Is Nothing Then .Offset(1).EntireRow.Hidden = (.Value = "") For Each v In Array(Array("Заявление на деньги", 50), _ Array("Маршрутный лист", 7), _ Array("СЗ по прибытию", 11), _ Array("Авансовый отчет", 12)) xx(Evaluate("'" & v(0) & "'!A1"), .Row - 12, v(1)).Hidden = (.Value = "") Next End If End With Next End Sub Function xx(ByRef r As Range, i, n) As Range Set xx = r.Offset(, i * n).Resize(, n).EntireColumn End Function
[/vba]
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range On Error Resume Next For Each r In Target.Rows With r.Cells(1, 1) If Not Intersect([$X$13:$X$18], .Cells) Is Nothing Then .Offset(1).EntireRow.Hidden = (.Value = "") For Each v In Array(Array("Заявление на деньги", 50), _ Array("Маршрутный лист", 7), _ Array("СЗ по прибытию", 11), _ Array("Авансовый отчет", 12)) xx(Evaluate("'" & v(0) & "'!A1"), .Row - 12, v(1)).Hidden = (.Value = "") Next End If End With Next End Sub Function xx(ByRef r As Range, i, n) As Range Set xx = r.Offset(, i * n).Resize(, n).EntireColumn End Function
#If VBA7 <> 1 Then Function ЕСЛИОШИБКА(ByVal проверяемое_значение As Variant, ByVal значение_если_ошибка As Variant) As Variant Dim i As Long, j As Long If IsArray(проверяемое_значение) Then On Error Resume Next j = UBound(проверяемое_значение, 2) If Err Then Err.Clear For i = 1 To UBound(проверяемое_значение) If IsError(проверяемое_значение(i)) Then проверяемое_значение(i) = значение_если_ошибка Next Else For i = 1 To UBound(проверяемое_значение) For j = 1 To UBound(проверяемое_значение, 2) If IsError(проверяемое_значение(i, j)) Then проверяемое_значение(i, j) = значение_если_ошибка Next Next End If Else If IsError(проверяемое_значение) Then проверяемое_значение = значение_если_ошибка End If ЕСЛИОШИБКА = проверяемое_значение End Function #End If
[/vba] + xlm функция-обертка _xlfn.IFERROR на листе макросов [vba]
#If VBA7 <> 1 Then Function ЕСЛИОШИБКА(ByVal проверяемое_значение As Variant, ByVal значение_если_ошибка As Variant) As Variant Dim i As Long, j As Long If IsArray(проверяемое_значение) Then On Error Resume Next j = UBound(проверяемое_значение, 2) If Err Then Err.Clear For i = 1 To UBound(проверяемое_значение) If IsError(проверяемое_значение(i)) Then проверяемое_значение(i) = значение_если_ошибка Next Else For i = 1 To UBound(проверяемое_значение) For j = 1 To UBound(проверяемое_значение, 2) If IsError(проверяемое_значение(i, j)) Then проверяемое_значение(i, j) = значение_если_ошибка Next Next End If Else If IsError(проверяемое_значение) Then проверяемое_значение = значение_если_ошибка End If ЕСЛИОШИБКА = проверяемое_значение End Function #End If
[/vba] + xlm функция-обертка _xlfn.IFERROR на листе макросов [vba]
у меня в word 2003, 2007, 2010, 2013 отрабатывает без ошибок раскрашенные файлы прилагаю
upd. немного переписал код пробуйте так [vba]
Код
Sub colorize() Dim p As Paragraph, prev&, b As Boolean 1 On Error GoTo colorize_Error
11 Application.ScreenUpdating = 0 21 With CreateObject("vbscript.regexp") 31 .Global = False: .Pattern = "^\d+\.\d+\s" 41 For Each p In ThisDocument.Paragraphs 51 If p.Next Is Nothing Then 61 If .test(p.Range.Text) Then 71 p.Range.HighlightColorIndex = IIf(b, 3, 7) 81 ElseIf prev > 0 Then 91 p.Parent.Range(prev - 1, p.Range.End).HighlightColorIndex = IIf(b, 3, 7) 101 End If 111 ElseIf .test(p.Range.Text) Then 121 If prev > 0 Then 131 p.Parent.Range(prev - 1, p.Previous.Range.End).HighlightColorIndex = IIf(b, 3, 7) 141 End If 151 b = Not b 161 prev = p.Range.Start + 1 171 End If 181 Next 191 End With 201 Application.ScreenUpdating = 1 211 On Error GoTo 0 221 Exit Sub colorize_Error: 231 MsgBox "Error " & Err.Number & " (" & Err.Description & _ ") in procedure colorize of VBA Document ThisDocument on line " & Erl & vbLf & _ "paragraphs.count: " & Paragraphs.Count & ", current paragraph: " & Range(0, _ p.Range.End).Paragraphs.Count End Sub
[/vba]
у меня в word 2003, 2007, 2010, 2013 отрабатывает без ошибок раскрашенные файлы прилагаю
upd. немного переписал код пробуйте так [vba]
Код
Sub colorize() Dim p As Paragraph, prev&, b As Boolean 1 On Error GoTo colorize_Error
11 Application.ScreenUpdating = 0 21 With CreateObject("vbscript.regexp") 31 .Global = False: .Pattern = "^\d+\.\d+\s" 41 For Each p In ThisDocument.Paragraphs 51 If p.Next Is Nothing Then 61 If .test(p.Range.Text) Then 71 p.Range.HighlightColorIndex = IIf(b, 3, 7) 81 ElseIf prev > 0 Then 91 p.Parent.Range(prev - 1, p.Range.End).HighlightColorIndex = IIf(b, 3, 7) 101 End If 111 ElseIf .test(p.Range.Text) Then 121 If prev > 0 Then 131 p.Parent.Range(prev - 1, p.Previous.Range.End).HighlightColorIndex = IIf(b, 3, 7) 141 End If 151 b = Not b 161 prev = p.Range.Start + 1 171 End If 181 Next 191 End With 201 Application.ScreenUpdating = 1 211 On Error GoTo 0 221 Exit Sub colorize_Error: 231 MsgBox "Error " & Err.Number & " (" & Err.Description & _ ") in procedure colorize of VBA Document ThisDocument on line " & Erl & vbLf & _ "paragraphs.count: " & Paragraphs.Count & ", current paragraph: " & Range(0, _ p.Range.End).Paragraphs.Count End Sub
Dim sh As Worksheet For Each sh In Sheets(Array("Лист3", "Лист4")) With sh.UsedRange.Interior If .Color = vbRed Then .Color = vbBlue Else .Color = vbRed End With Next
Dim sh As Worksheet For Each sh In Sheets(Array("Лист3", "Лист4")) With sh.UsedRange.Interior If .Color = vbRed Then .Color = vbBlue Else .Color = vbRed End With Next
Option Explicit Function UsedRangeByCodeName(sCodeName$) As Range Set UsedRangeByCodeName = ThisWorkbook.VBProject. _ VBComponents(sCodeName). _ Properties("usedrange").Object End Function Sub test() Dim MyCollection As New Collection Dim v, r As Range, r1 As Range, r2 As Range, addr$ For Each v In Array("Лист3", "Лист4") MyCollection.Add UsedRangeByCodeName(CStr(v)), v Next With Application.FindFormat .Clear With .Interior .PatternColorIndex = xlAutomatic .Color = vbBlue .TintAndShade = 0 .PatternTintAndShade = 0 End With End With For Each r In MyCollection Set r1 = r.Find(What:="", After:=r(1, 1), LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=True) If Not r1 Is Nothing Then addr = r1.Address Set r2 = r1 Do If r1.Address <> addr Then Set r2 = Union(r2, r1) Set r1 = r.Find(What:="", After:=r1, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=True) Loop While Not r1 Is Nothing And r1.Address <> addr End If If Not r2 Is Nothing Then r2.Interior.Color = 255: Set r2 = Nothing Next End Sub
[/vba]
[vba]
Код
Option Explicit Function UsedRangeByCodeName(sCodeName$) As Range Set UsedRangeByCodeName = ThisWorkbook.VBProject. _ VBComponents(sCodeName). _ Properties("usedrange").Object End Function Sub test() Dim MyCollection As New Collection Dim v, r As Range, r1 As Range, r2 As Range, addr$ For Each v In Array("Лист3", "Лист4") MyCollection.Add UsedRangeByCodeName(CStr(v)), v Next With Application.FindFormat .Clear With .Interior .PatternColorIndex = xlAutomatic .Color = vbBlue .TintAndShade = 0 .PatternTintAndShade = 0 End With End With For Each r In MyCollection Set r1 = r.Find(What:="", After:=r(1, 1), LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=True) If Not r1 Is Nothing Then addr = r1.Address Set r2 = r1 Do If r1.Address <> addr Then Set r2 = Union(r2, r1) Set r1 = r.Find(What:="", After:=r1, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=True) Loop While Not r1 Is Nothing And r1.Address <> addr End If If Not r2 Is Nothing Then r2.Interior.Color = 255: Set r2 = Nothing Next End Sub
var data = chunk(UrlFetchApp.fetch(baseUrl + queryString , hparams).getContentText(),5000); Logger.log(data) var ss = SpreadsheetApp.getActive(); var sheet = ss.getSheetByName("rates"); var raw = sheet.getRange(13,1).offset(0, 0, data.length).setValue(data) } function chunk(a,b){ return Array.apply(null, new Array(Math.ceil(a.length/b))). map(function (c, d) {return [a.substr(d*b,b)];}); }
[/vba]или делим строку по скобкам [vba]
Код
var data = BtwBraces(UrlFetchApp.fetch(baseUrl + queryString , hparams).getContentText());
[/vba] [vba]
Код
function BtwBraces(a){ return a.split(/[{}]/g). filter(function(a){return a;}). map(function (a){return [a];}) }
[/vba]
тупо делим на куски по 5к символов [vba]
Код
function commas() { var key = 'api'; var secret = 'secret';
var baseUrl = "https://exchange.com"; var endPoint = "/public/api/ver1/smart_trades"; var pointParams = "?account_id=xxx&scope=finished"; var queryString = endPoint + pointParams; var signature = Utilities.computeHmacSha256Signature(queryString, secret); signature = signature.map(function(e) {return ("0" + (e < 0 ? e + 256 : e).toString(16)).slice(-2)}).join("");
var data = chunk(UrlFetchApp.fetch(baseUrl + queryString , hparams).getContentText(),5000); Logger.log(data) var ss = SpreadsheetApp.getActive(); var sheet = ss.getSheetByName("rates"); var raw = sheet.getRange(13,1).offset(0, 0, data.length).setValue(data) } function chunk(a,b){ return Array.apply(null, new Array(Math.ceil(a.length/b))). map(function (c, d) {return [a.substr(d*b,b)];}); }
[/vba]или делим строку по скобкам [vba]
Код
var data = BtwBraces(UrlFetchApp.fetch(baseUrl + queryString , hparams).getContentText());
[/vba] [vba]
Код
function BtwBraces(a){ return a.split(/[{}]/g). filter(function(a){return a;}). map(function (a){return [a];}) }
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address <> "$B$12" Then Exit Sub Cells((([F15] - 1) \ 3 + 1) * 2, 4 - (([F15] - 1) Mod 3)) = Target End Sub
[/vba]
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address <> "$B$12" Then Exit Sub Cells((([F15] - 1) \ 3 + 1) * 2, 4 - (([F15] - 1) Mod 3)) = Target End Sub
Function xx(ParamArray r() As Variant) As Range Dim rng, b As Boolean If Application.Caller.Address = ActiveCell.Address Then Set xx = r(0).Cells(1, 1) b = xx For Each rng In r rng.Value = Not b Next End If End Function
Function xx(ParamArray r() As Variant) As Range Dim rng, b As Boolean If Application.Caller.Address = ActiveCell.Address Then Set xx = r(0).Cells(1, 1) b = xx For Each rng In r rng.Value = Not b Next End If End Function