[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『数式下フィルマクロで質問です』(kou)
以下の通り数式を下にフィルしていますが、途中不規則に複数箇所「=SUM(H150:H210)」小計値があります。
ココを飛ばして以下数式をフィルすることは可能でしょうか
よろしくお願いします。
Sub test()
Range("G5:G3100").FormulaR1C1 = "=IF(RC[6]="""","""",IF(RC[-1]=1,"""",IFERROR(ROUNDDOWN(RC[4],ROUNDUP(LOG10(RC[4]),0)*-1+4),"""")))" Range("H5:H3100").FormulaR1C1 = "=IF(RC[-2]=1,RC[3],IF(ISTEXT(RC[5]),RC[5],IF(RC[-1]="""","""",ROUND(RC[-2]*RC[-1],0))))" Range("K5:K3100").FormulaR1C1 = "=IF(RC[2]="""","""",IF(RC[4]="""",RC[2],ROUNDDOWN(RC[4],ROUNDUP(LOG10(RC[4]),0)*-1+4)))" Range("O5:O3100").FormulaR1C1 = "=IF(RC[-1]="""","""",ROUND(RC[-2]*RC[-1],-3))" End Sub
< 使用 Excel:Excel2010、使用 OS:Windows7 >
>=SUM(H150:H210)
は、どの列にあるのか不明なので、H列にあるという前提です。
Sub test() Dim dic As Object Dim c As Range Dim k
Set dic = CreateObject("Scripting.dictionary")
Range("G5:G3100").FormulaR1C1 = "=IF(RC[6]="""","""",IF(RC[-1]=1,"""",IFERROR(ROUNDDOWN(RC[4],ROUNDUP(LOG10(RC[4]),0)*-1+4),"""")))" Range("K5:K3100").FormulaR1C1 = "=IF(RC[2]="""","""",IF(RC[4]="""",RC[2],ROUNDDOWN(RC[4],ROUNDUP(LOG10(RC[4]),0)*-1+4)))" Range("O5:O3100").FormulaR1C1 = "=IF(RC[-1]="""","""",ROUND(RC[-2]*RC[-1],-3))"
With Range("H5:H3100") For Each c In .Cells If c.Formula Like "=SUM(*" Then dic(c.Address) = c.Formula End If Next .FormulaR1C1 = "=IF(RC[-2]=1,RC[3],IF(ISTEXT(RC[5]),RC[5],IF(RC[-1]="""","""",ROUND(RC[-2]*RC[-1],0))))" End With
For Each k In dic.keys With Range(k) .Formula = dic(k) .Offset(, -1).Value = "" .Offset(, 3).Value = "" .Offset(, 7).Value = "" End With Next
End Sub
(マナ) 2015/03/04(水) 22:18
どんくさいけど、こんな処理も。 個人的に R1C1形式が好きじゃないのでA1形式で。
Sub test2()
SetFormula Range("G5:G3100"), "=IF(M5="""","""",IF(F5=1,"""",IFERROR(ROUNDDOWN(K5,ROUNDUP(LOG10(K5),0)*-1+4),"""")))" SetFormula Range("H5:H3100"), "=IF(F5=1,K5,IF(ISTEXT(M5),M5,IF(G5="""","""",ROUND(F5*G5,0))))" SetFormula Range("K5:K3100"), "=IF(M5="""","""",IF(O5="""",M5,ROUNDDOWN(O5,ROUNDUP(LOG10(O5),0)*-1+4)))" SetFormula Range("O5:O3100"), "=IF(N5="""""","",ROUND(M5*N5,-3))"
End Sub
Private Sub SetFormula(r As Range, fm As String)
Dim v As Variant Dim w As Variant Dim i As Long
v = r.Formula r.Formula = fm w = r.Formula
For i = 1 To UBound(v, 1) If Left(v(i, 1), 5) = "=SUM(" Then w(i, 1) = v(i, 1) Next
r.Formula = w
End Sub
(β) 2015/03/05(木) 08:47
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.