[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『マクロ実行時のエラーについて』(emidij)
マクロ実行時のエラーについてご教授頂ければ幸いです。
下記マクロを実行すると、
Range(Cells(4, lColumn * 2 + 8)).Select
のところでエラーになります。
どう修正してよいのか分からず、どなたかお助け頂けないでしょうか?
よろしくお願い致します。
Sub Macro1()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim lRow As Long Dim lColumn As Long
lRow = Cells(Rows.Count, "F").End(xlUp).Row lColumn = Range("F1").Value
With Range(Cells(6, 8), Cells(lRow - 2, lColumn * 2 + 7)).Select Selection.NumberFormatLocal = "0.00%"
End With
With Range(Cells(5, 2), Cells(5, lColumn * 3 + 7)).Select With Selection.Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorDark2 .TintAndShade = -9.99786370433668E-02 .PatternTintAndShade = 0 End With
With Range(Cells(6, 7), Cells(lRow - 2, 7)).Select Selection.ClearContents
End With
Range(Cells(4, lColumn * 2 + 8)).Select ActiveCell.Formula = "=C3" Range(Cells(4, lColumn * 2 + 8)).Select Selection.NumberFormatLocal = "yy"".""mm" With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = True .ReadingOrder = xlContext .MergeCells = False End With Range(Cells(4, lColumn * 2 + 9)).Select ActiveCell.Formula = "=EDATE(AJ4,1)" Range(Cells(4, lColumn * 2 + 9)).Select Selection.NumberFormatLocal = "yy"".""mm" With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = True .ReadingOrder = xlContext .MergeCells = False End With Range(Cells(4, lColumn * 2 + 8), Cells(4, lColumn * 2 + 9)).Select Selection.Copy Range(Cells(4, lColumn * 2 + 8)).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range(Cells(4, lColumn * 2 + 8), Cells(4, lColumn * 2 + 9)).Select Selection.AutoFill Destination:=Range("AJ4:BZ4"), Type:=xlFillDefault
End With
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
< 使用 Excel:Office365、使用 OS:Windows10 >
>Range(Cells(4, lColumn * 2 + 8)).Select のところでエラーになります。
単に Cells(4, lColumn * 2 + 8).Select としてみる
(半平太) 2022/01/19(水) 16:56
軽く整理しただけでも↓のようになるので、無駄な部分がないか自己チェックしてみてはどうでしょうか?
Sub Macro1_整理() Dim lRow As Long Dim lColumn As Long
'Application.ScreenUpdating = False Application.DisplayAlerts = False
With ActiveSheet lRow = .Cells(.Rows.Count, "F").End(xlUp).Row lColumn = .Range("F1").Value
.Range("H6", .Cells(lRow - 2, lColumn * 2 + 7)).NumberFormatLocal = "0.00%"
With .Range("B5", .Cells(5, lColumn * 3 + 7)).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorDark2 .TintAndShade = -9.99786370433668E-02 .PatternTintAndShade = 0 End With
.Range("G7", .Cells(lRow - 2, 7)).ClearContents
With .Cells(4, lColumn * 2 + 8) .Formula = "=C3" .NumberFormatLocal = "yy"".""mm"
.HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = True .ReadingOrder = xlContext .MergeCells = False End With
With Cells(4, lColumn * 2 + 9) .Formula = "=EDATE(AJ4,1)" .NumberFormatLocal = "yy"".""mm"
.HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = True .ReadingOrder = xlContext .MergeCells = False End With
With .Cells(4, lColumn * 2 + 8).Resize .Value = .Value .AutoFill Destination:=Range("AJ4:BZ4"), Type:=xlFillDefault End With End With
Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub
(もこな2) 2022/01/19(水) 19:53
誤 With .Cells(4, lColumn * 2 + 8).Resize 正 With .Cells(4, lColumn * 2 + 8).Resize(,2)
ほかにもあったらごめんなさい。
(もこな2) 2022/01/19(水) 19:55
もこな2様、ご教授ありがとうございます。
Selectは不要なのですね。
知識不足で申し訳ありません。
もっと勉強していきたいと思います。
本当にありがとうございました
(emidij) 2022/01/21(金) 13:51
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.