行を削除してもエラーが起きない関数の組み合わせ 関数 ワークシート関数

たまには趣向を変えて関数の投稿をしたいと思います。

 

今回はVlookUp関数についての投稿です

 

表のマッチングでよく使うVlookUp関数

 

マクロを使えない頃私はよくお世話になってました。

 

非常に便里なVlookですが、

 

参照元の表の行を削除するとエラーで崩れてしまいます。

 

そんなときには、

 

VlookUP + Offset関数 を使うべし!

 

サンプルコードは添付をご覧ください。

入力シートにはIDとサンプル関数・リスト一覧があります。

サンプル関数をよくご覧になって参考にしてください。

 

 

サンプルコード(Vlookup+Offset)

 

Offset関数は基準セルからの高さが肝心!

 

 

とは言ってもマクロを使えばイチコロですが☆彡

 

この入れ子の構造がわかればマクロを学ぶとき、大きな助けになります♪

 

ご質問があればなんなりと♪

外枠罫線を設定する マクロ VBA 外枠

前回の投稿では罫線を設定するマクロを紹介しました。

http://kimama-vba.com/2018/06/28/borders_vba_macro_lattice/

 

今回は外枠の設定方法について投稿します♪

Bordersプロパティとは違い、

 

BorderAround メソッド

を使用します。

 

パラメーター

名前 必須 / 省略可能 データ型 説明
LineStyle 省略可能 バリアント型 (Variant) XlLineStyle クラスの定数のいずれかで、罫線の種類を指定します。
Weight 省略可能 XlBorderWeight 罫線の太さを指定します。
ColorIndex 省略可能 XlColorIndex 罫線の色を現在のカラー パレットのインデックス番号または XlColorIndex定数で指定します。
Color 省略可能 バリアント型 (Variant) 罫線の色を示す RGB 値を指定します。
ThemeColor 省略可能 バリアント型 (Variant) テーマの色を現在の配色テーマのインデックス番号または XlThemeColor値で指定します。

 

外枠罫線は Weight の xlMedium とすれば太枠の外枠罫線となります。

 

サンプルコード

B2からC5まで外枠罫線を引きます。

 

ではでは♪

罫線を設定する VBA マクロ 格子

表の設定などで必要になってくる罫線の設定

 

わざわざマクロの記録をして罫線を設定して、

モジュールから登録されたマクロを確認して、、

やたら長ったらしいコードから必要なコードをみつけて。。。

なんてめんどくさい!って思いますよね。

 

 

そんな時には

Borders コレクション の Linestyle プロパティ

を使用しましょう♪

種類は下記の通りです。

名前 説明
xlContinuous 1 実線
xlDash -4115 破線
xlDashDot 4 一点鎖線
xlDashDotDot 5 ニ点鎖線
xlDot -4118 点線
xlDouble -4119 2 本線
xlLineStyleNone -4142 線なし
xlSlantDashDot 13 斜破線

= True にしても格子罫線を引けます。

 

格子罫線を外す場合は

 

= False

 

にすれば外せます。

Boldersオブジェクトの引数には

xlBordersIndex クラスの

xlDiagonalDown  左上隅から右下への罫線

xlDiagonalUp  左下隅から右上への罫線

xlEdgeBottom  下側の罫線

xlEdgeLeft  左端の罫線

xlEdgeRight  右端の罫線

xlEdgeTop  上側の罫線

xlInsideHorizontal  範囲外の罫線を除く、範囲内のすべてのセルの水平罫線

xlInsideVertical 範囲外の罫線を除く、範囲内のすべてのセルの垂直罫線

 

で指定できます。

 

 

ではでは♪

定数のみを選択する VBA マクロ

久々の投稿です。

 

ネタはたくさんあるんですが、更新する時間がとれず。。

 

ってことで今回のネタ 「定数のみを選択するマクロ」

 

金額のリストや計算書では手入力セルとシート関数セルがありますよね。

週次の計算の更新をするために、

この手入力セルとシート関数セルをわけて消すのも面倒ですよね。

 

そんなときに使える

 

Range.SpecialCells メソッド

 

セルの取得とは言っても、たくさんの取得種類があります。

 

XlCellType 定数
xlCellTypeAllFormatConditions 表示形式が設定されているセル -4172
xlCellTypeAllValidation 条件の設定が含まれているセル -4174
xlCellTypeBlanks 空の文字列 4
xlCellTypeComments コメントが含まれているセル -4144
xlCellTypeConstants 定数が含まれているセル 2
xlCellTypeFormulas 数式が含まれているセル -4123
xlCellTypeLastCell 使われたセル範囲内の最後のセル 11
xlCellTypeSameFormatConditions 同じ表示形式が設定されているセル -4173
xlCellTypeSameValidation 同じ条件の設定が含まれているセル -4175
xlCellTypeVisible すべての可視セル 12

 

今回は定数を取得するので、xlCellTypeConstants となります。

サンプルは下記です♪

定数は消えて、数式は残ります。

Sub SelectType()
Cells.SpecialCells(xlCellTypeConstants).Select
Selection.ClearContents
End Sub

 

 

ではでは♪

バイナリーモードで読み込む(そもそもバイナリーってなんだ?) VBAマクロ

テキストファイルを操作するマクロ書いてたらぶち当たったので備忘録として。

 

バイナリーモードってのは 2進数で表現されたデータ(テキストファイル以外)を読み込みますよってこと

 

テキストファイル以外のファイルを扱う時に使うって覚えておけば問題ないかな!

 

あんま使わないけどw

 

サンプルコード

 

Sub filesousa()
Dim fun As String
Dim Num As Integer
Num = FreeFile
Open “‘C:\Users\Yusuke\Documents\名前入力.txt” For Binary As #Num ※下線部分は適宜作成してください。
fun = Space(FileLen(“C:\Users\Yusuke\Documents\名前入力.txt”))
Get #Num, , fun
Range(“A1”).Value = fun
Close Num
End Sub

 

セルに関数を入れる VBA マクロ

マクロで合計出せるけど、しがらみがあって(笑)マクロにできないという場面があるかと思います。

 

そんなときにセルに関数を挿入したいなと思うかもしれません。

 

そんな時に使用するのが Formula プロパティ

 

Range.Formula

 

もしくは

 

Cells.Formula

 

を使用すればサクッとできます♪

 

Sub Formulasam()
Range(“A1:A10”).Value = 5
Range(“A11”).Formula = “= SUM(A1:A10)”
End Sub

 

 

 

 

ペースト方法を指定して貼り付け!PasteSpecial VBAマクロ

値で貼り付けたいのにミスった!ってことあるかと思います。

 

そんな時には PasteSpecialメソッド を使いましょう。

 

xlPasteValues -4163 値のみ
xlPasteComments -4144 コメントを貼り付け
xlPasteFormulas -4123 数式を貼り付け
xlPasteFormats -4122 コピーしたソースの形式を貼り付け
xlPasteAll -4104 すべてを貼り付け
xlPasteValidation 6 入力規則を貼り付け
xlPasteAllExceptBorders 7 輪郭以外のすべてを貼り付け
xlPasteColumnWidths 8 コピーした列の幅を貼り付け
xlPasteFormulasAndNumberFormats 11 数式と数値の書式を貼り付け
xlPasteValuesAndNumberFormats 12 値と数値の書式を貼り付け
xlPasteAllUsingSourceTheme 13 ソースのテーマを使用してすべてを貼り付け
xlPasteAllMergingConditionalFormats 14 すべてを貼り付け、条件付き書式をマージ

 

ごちゃごちゃ余計な引数は使わん!って人は下記のサンプルをご閲覧あれ♪

 

Sub Pastes()
Range(“A1”) = “ペースト練習”
Range(“A1”).Select
Selection.Copy
Range(“E1”).PasteSpecial xlPasteValues
End Sub

 

ではでは。

 

 

変更を保存しないで閉じる VBA マクロ

マクロ書いているとブックとブックの操作ってよくあると思います。

指定のブックを開いてにょろにょろする とかですね。

 

ただ開いたブックを閉じるときに ”変更を保存しますか?”

ってメッセージがマクロ実行中に起きると何だか萎えますよね笑

 

そんな時は Closeメソッドを使ってサクッと解消しましょ♪

Closeメソッド

Workbooks.Close 

Savechanges:(省略可)=True(変更を保存する)もしくはFalse(保存しない) 

Filename:=ファイル名を指定して保存(省略可)

既定値はダイアログを表示します。

 

サンプルは下記

 

ではでは♪(*´ω`*)

文字列を検索するマクロ VBA

何かと便利な検索機能はVBAでも使うシーンがあるかと思います。

 

そこで使うのはFindメソッド

構文

Object:Rangeオブジェクト

What:(必須)検索データを指定

After:(省略可) 指定したセルの次のセルから検索を始める

LookIn:(省略可)検索対象を指定 数式(xlFormulas)、値(xlValues)、コメント(xlComments)

LookAt:(省略可)完全一致(xlWhole)もしくは部分一致(xlPart)かを指定

SearchOrder:(省略可) 検索方向を指定する。列方向(横方向)で検索(xlByColumns)、行方向(縦方向)に検索する(xlByRows)

SearchDirection:(省略可) 前方に検索(xlNext:既定値)、後方に検索(xlPrevious)

MatchCase:(省略可) 大文字と小文字を区別する(True)、区別しない(False)

MatchByte:(省略可) 半角と全角を区別する(True)、区別しない(False)

SearchFormat:(省略可) 書式の検索をする (True)、検索しない(False)

検索文字が見つからない場合は ”Nothing” を返します。

 

構文長いですね(^-^;

 

長い!

余計な構文いいから文字探したいんだ!( ゚Д゚)

 

という人は下記のサンプル使ってみてはいかがでしょう。

Sub sagasutest()
Dim R As Range
Set R = Cells.Find(what:=”ミスチル”)
If R Is Nothing Then
MsgBox “文字が見つかりません”
Else
MsgBox “文字が見つかりました。”
R.Activate
End If

End Sub

ミスチルが好きなので

例文でミスチルとしていますが検索したい文字を入力してください笑

 

ではでは

今月末日・先月末日・来月末日 を返すマクロ VBA

事務職してるとexcelで月末日を出さなきゃならなくなりますよね。

 

下記、サンプルコードです。

 

Sub Hiduke()
‘今月の末日
Debug.Print DateSerial(Year(Date), Month(Date) + 1, 0)
‘前月の月末日
Debug.Print DateSerial(Year(Date), Month(Date), 0)
‘翌月の月末日
Debug.Print DateSerial(Year(Date), Month(Date) + 2, 0)
End Sub

 

DateSerialはVBA使っていると必須のデータ型関数です。

 

覚えれば非常に便利です♪