EXCEL(memo)

2018年6月19日

excel関数やVBAのまとめるまえのメモです。

‘### 文字列を置き換える ###

SUBSTITUTE(A1,".jpg","")

※セルのA1の.jpgをスペースに置き換える。
文字列置き換え
.jpg→スペースに修正


‘### セルに色を塗る ###

Dim set_data01 As Object
Cells.Interior.ColorIndex = xlNone '###全セルセル色付クリア

Set set_data01 = Worksheets("Sheet1").Range("A1")
Do Until set_data01.Offset(0, 0).Value = "" '###A1を基準に、行方向に空白が出るまで繰り返す。

   If set_data01.Offset(0, 0).Value = "りんご" then
      set_data02.Offset(0, 0).Interior.ColorIndex = 3 '###りんごを赤
   Else
      set_data02.Offset(0, 0).Interior.ColorIndex = xlNone "###りんご以外は塗り潰し無し
   Endif
   Set set_data01 = set_data01.Offset(1, 0) '###1行下へ移動

Loop

‘### メッセージBOX改行※他にも使用できます ###

MsgBox " 処理スタート " & vbCrLf & " クリアして良いですか? "

計算式の関数を書くと、以下のように、excel特有の結果が出ることがあります。
気にしなければそのままで構わないと思うのですが、見栄えが悪いですよね。

#VALUE!
#DIV/0!

こんなときにIFERROR関数を使用するときれいに表示できます。
以下の例は、計算結果がエラー扱いのときは”err”と表示します。

=IFERROR(A1/A2,"err")

Office2003の時には、この機能が無くて、ISERRORを使用してたんですが、面倒でした。


VBAの四捨五入(※Roundとは結果が変わります。)
※Applicationを付けないと、計算結果が変わります。銀行型丸めと言うようで、EXCELとマクロで違うのでややこしいですね。

Application.Round

Excelの中に改行が入っていて消したいとき無いですか?少しなら手で消せば良いですけど沢山は面倒ですよね。
検索する文字列に「 Ctrl+J 」と入れて、置き換え後はそのまま、セル内容が完全に一致する…のチェックを外して実行すると、改行をNullへ変換します。
※違う種類の改行はこれでは変換できません。


‘※※※※※※オートフィルター解除 ※※※※※※
フィルタをマクロで解除する

If Sheets("UNMATCH").AutoFilterMode Then
   Sheets("UNMATCH").Range("A1").AutoFilter
End If

<hr width="100%" />

Bookの表示されているシートを全解除します。

‘### 全シートフィルタのクリア サブプロシージャ ###
Sub sheet_clear()
For CLEAR001 = 1 To Worksheets.Count
If Sheets(Worksheets(CLEAR001).Name).AutoFilterMode Then
   Sheets(Worksheets(CLEAR001).Name).Range(“A1”).AutoFilter
End If
Next
End Sub

‘### マクロのメッセージで止まることがありますよね。それをスキップします。 ###
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs “D:SAMPLE\Book1.xls”
Application.DisplayAlerts = True


‘### 文字の置き換えです。前半角
Dim check As String
check = “BOOKservice”
MsgBox StrConv(check , vbWide)         ’BOOKserviceに変更される。

vbProperCase 文字列を大文字に変換します
vbLowerCase 文字列を小文字に変換します
vbProperCase 文字列の各単語の先頭の文字を大文字に変換します
vbWide 文字列内の半角文字を全角文字に変換します
vbNarrow 文字列内の全角文字を半角文字に変換します
vbKatakana 文字列内のひらがなをカタカナに変換します
vbHiragana 文字列内のカタカナをひらがなに変換します
vbUnicode システムの既定のコードページを使って文字列をUnicodeに変換します
vbFromUnicode 文字列をUnicodeからシステムの既定のコードページに変換します

Posted by master