VBA
Sub CopyFormulasBasedOnCriteria() Dim wsSource As Worksheet Dim wsTemplate As Worksheet Dim lastRow As Long Dim i As Long Dim copyRow As Long Dim pasteRow As Long ' シートをセットアップ Set wsSource = ThisWorkbook.Sheets("Data") Set wsTemp…
Sub 判別して記入() Dim ws1 As Worksheet Dim ws2 As Worksheet Dim lastRow1 As Long Dim lastRow2 As Long Dim keyRange As Range Dim cell As Range Dim keyArray() As Variant Dim keyIndex As Long Dim labels() As String Dim labelIndex As Long Dim…
Sub コピー転記してから色付け() Dim wsSource As Worksheet Dim wsDestination As Worksheet Dim wsKeywords As Worksheet Dim lastRowSource As Long Dim lastRowKeywords As Long Dim i As Long Dim j As Long Dim keyword As String ' Sheet1をソース、S…
Sub UpdateDColumnWithRegex() Dim regex As Object Dim lastRow As Long Dim i As Long ' 正規表現オブジェクトを作成 Set regex = CreateObject("VBScript.RegExp") ' パターンを設定 regex.IgnoreCase = True regex.Global = True ' 最終行を取得 lastRow…
Sub 比較と記載1() ’Dictionaryにてremoveメソッド利用(ないものを削除パターン) Dim ws As Worksheet Dim lastRow1, lastRow2 As Long Dim aRange As Range, cRange As Range Dim aValue As Variant, cValue As Variant Dim compareColumnA As Range, compa…
Sub TransferData() Dim ws1 As Worksheet Dim ws2 As Worksheet Dim lastRow1 As Long Dim lastRow2 As Long Dim i As Long Dim j As Long Dim keyA As String Dim keyB As String Dim matchFound As Boolean ' Sheet1とSheet2を設定 Set ws1 = ThisWorkboo…
Sub 前日の日付を計算してセルに入力() Dim 検索日 As Date Dim 前日 As Date Dim 後日 As Date Dim フォーマット済み日付 As String Dim 休みの日 As Range ' セルA1に入力された日付を取得(ここではA1を例示しています) 検索日 = Range("A1").Value ' 休…
Sub 前日の日付を計算してセルに入力() Dim 検索日 As Date Dim 前日 As Date Dim フォーマット済み日付 As String Dim 休みの日 As Range ' セルA1に入力された日付を取得(ここではA1を例示しています) 検索日 = Range("A1").Value ' 休みの日をシート2の…
Sub ExtractWeekendAndHolidayDatesByMonth(yearToSearch As Integer, monthToSearch As Integer) Dim startDate As Date Dim endDate As Date Dim currentDate As Variant Dim ws As Worksheet Dim rowNum As Long Dim holidayDates As Variant Dim i As In…
Sub ExtractWeekendDates() Dim startDate As Date Dim endDate As Date Dim currentDate As Date Dim ws As Worksheet Dim rowNum As Long ' 新しいシートを作成します Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.C…
Sub ExtractWeekendAndHolidayDatesByYear(yearToSearch As Integer) Dim startDate As Date Dim endDate As Date Dim currentDate As Date Dim ws As Worksheet Dim rowNum As Long Dim holidayDates As Variant Dim i As Integer ' 新しいシートを作成しま…
Sub Sample() Range("B2:D5").EntireColumn.ClearEnd Sub
Sub Sample() Range("A:C").RemoveDuplicates(Array(1, 2, 3))End Sub
Sub UpdateDColumnWithRegex555() Dim regex As Object Dim lastRow As Long Dim i As Long '参照設定 Microsoft VBScript Regular Expressions 5.5 ' 正規表現オブジェクトを作成 Set regex = CreateObject("VBScript.RegExp") ' パターンを設定 regex.Igno…
Sub UpdateDColumn() Dim lastRow As Long Dim i As Long ' 最終行を取得 lastRow = Cells(Rows.Count, "C").End(xlUp).Row ' ループして条件に基づいてD列を更新 For i = 2 To lastRow If InStr(1, Cells(i, "C").Value, "AAA") > 0 And InStr(1, Cells(i, …
Sub Main() Dim executionTime As Double executionTime = MeasureExecutionTime() MsgBox "計測完了" & vbLf & "実行時間は" & Format(executionTime, "0.000秒") & "でした"End SubFunction MeasureExecutionTime() As Double Dim startTime As Double sta…
Sub MessagePrompt() Dim response As VbMsgBoxResult ' メッセージボックスを表示し、ユーザーからの応答を取得します response = MsgBox("処理を続行しますか?", vbYesNo + vbQuestion, "確認") ' ユーザーがYesを選択した場合の処理 If response = vbYes…
'time計測Dim ST As DoubleST = Timer '処理時間の取得Debug.Print Timer - STMsgBox "取得が完了しました" & vbLf & "実行時間は" & Format(Timer - ST, "0.000秒") & "でした" '//-------------------- Debug.Print Now() & Right(Format(Timer, "0.00"), …
Sub CreateResultFile() ’セルC2が空白でない場合にはCSVファイルが作成し、かつセルC2が空白の場合にはテキストファイルが作成 Dim searchDate As String Dim fileName As String Dim filePath As String Dim fileContent As String Dim ws As Worksheet Di…
'//-------------------------------------------------------- Sub SaveAttachmentsToFolder() Dim olFolder As Outlook.MAPIFolder Dim olItem As Object Dim olAttachment As Outlook.Attachment Dim saveFolder As String ' 受信メールフォルダを指定し…
Sub SaveSearchResultAsPDF() Dim searchDate As String Dim fileName As String Dim filePath As String ' 検索日の取得 searchDate = Format(Date, "yyyymmdd") ' ファイル名の作成 fileName = "検索結果_" & searchDate & ".pdf" ' ファイルパスの作成(…
Sub CreateResultTextFile() Dim searchDate As String Dim fileName As String Dim filePath As String Dim fileContent As String ' 検索日の取得 searchDate = Format(Date, "yyyymmdd") ' ファイル名の作成 fileName = "対象なし_" & searchDate & ".txt…
Sub 値のみ貼り付け() ' セルA1をコピー Worksheets("Sheet1").Range("A1").Copy ' セルB1に値のみを貼り付け Worksheets("Sheet2").Range("B1").PasteSpecial Paste:=xlPasteValues ' コピー状態を解除 Application.CutCopyMode = FalseEnd Sub
Sub 転記() Dim LastRow As Long Dim i As Long ' Sheet1の最終行を取得 LastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row ' データ転記 For i = 2 To LastRow ' 1行目はヘッダーとしてスキップ ' Sheet1からSheet2へ値のみ転記 Sheets("Shee…
Sub CreatePivotTableWithPrompt() Dim ws As Worksheet Dim pt As PivotTable Dim pc As PivotCache Dim rngData As Range Dim rngDest As Range Dim response As VbMsgBoxResult ' データ範囲を指定します。適切に変更してください。 Set ws = ThisWorkboo…
Sub CreatePivotTableWithFormat() Dim ws As Worksheet Dim pt As PivotTable Dim pc As PivotCache Dim rngData As Range Dim rngDest As Range ' データ範囲を指定します。適切に変更してください。 Set ws = ThisWorkbook.Worksheets("Sheet1") Set rngD…
Sub CopyData1() Dim sourceSheet As Worksheet Dim targetSheet As Worksheet Dim lastRow As Long ' ソースシート(コピー元のシート)とターゲットシート(コピー先のシート)を設定 Set sourceSheet = ThisWorkbook.Sheets("Sheet1") Set targetSheet = …
Sub 編集結果を転記する()'売上見込み編集 Dim sourceSheet As Worksheet Dim targetSheet As Worksheet Dim lastRow As Long Dim i As Long Dim targetRow As Long Dim targetColumn As Long Dim sourceColumns As Long '編集結果シート初期化 Sheets("編集…
Option ExplicitSub sheet初期化() Columns("A:H").Select Selection.ClearContents Range("A1").SelectEnd SubSub 売上編集結果()'出品した商品 販売利益データのデータから編集 Dim sourceSheet As Worksheet Dim targetSheet As Worksheet Dim lastRow As…
Sub CreateTableOfContents() Dim ws As Worksheet Dim tocSheet As Worksheet Dim rowNum As Integer Dim sheetNum As Integer ' 新しいシートを作成して目次を作成 Set tocSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheet…