VBA
Sub 分解して展開する() Dim ws As Worksheet Dim lastRow As Long Dim i As Long Dim num As String Dim startCol As Long Dim j As Long Dim digit As String Dim integerPart As String Dim decimalPart As String Dim numLength As Long Dim decimalLeng…
Sub 分解して展開する() ’条件:A列の数値⇒1億はG列~展開とする 'lastRow → lastR: 最終行の変数名を lastR に短縮。'num → n: 数値を格納する変数を n に短縮。'startCol → sCol: 開始列を指す変数名を sCol に短縮。'digit → dgt: 各桁を格納する変数名を …
'//自作関数版 =MID(SplitText1(A1), 1, 1) Function SplitText1(inputStr As String) As String Dim cleanStr As String ' 数字の場合は小数点を除去 If IsNumeric(inputStr) Then cleanStr = Replace(inputStr, ".", "") ' 小数点を除去 Else cleanStr = i…
Sub セル値の分解() Dim str As String Dim i As Integer Dim lastRow As Long Dim rowNum As Long Dim cleanStr As String ' A列の最終行を取得 lastRow = Cells(Rows.Count, 1).End(xlUp).Row ' A列の各セルを処理 For rowNum = 1 To lastRow ' A列のセル…
Sub Oracle ODBCドライバの接続() ' 変数の宣言 Dim conn As Object Dim connectionString As String Dim userID As String Dim password As String Dim tnsService As String Dim odbcDriver As String ' シートからユーザーID とパスワードを読み取る(例:…
Sub日付による条件判断() Dim cell As Range Dim referenceDate As Date Dim oneWeekBefore As Date Dim targetRange As Range Dim searchRange As Range Dim foundCell As Range ' 検索する範囲(例:B1:B10)を指定 Set searchRange = Range("B1:B10") ' …
Sub ExcelからAccess抽出() Dim accessApp As Object Dim accessDbPath As String Dim queryName As String Dim conn As Object Dim connectionString As String Dim userName As String Dim password As String Dim odbcDSN As String ' Excelシートからユ…
Sub text復号化() Dim fs As Object Dim textFile As Object Dim encryptedText As String Dim plainText As String ' 暗号化されたファイルを読み込む Set fs = CreateObject("Scripting.FileSystemObject") Set textFile = fs.OpenTextFile("C:\path\to\yo…
Sub text暗号化() Dim fs As Object Dim textFile As Object Dim plainText As String Dim encryptedText As String ' ユーザー名とパスワード Dim OracleUsername As String Dim OraclePassword As String OracleUsername = "yourUsername" OraclePassword …
Sub Z列に各行の最後の非空セルの値を転記() Dim rng As Range Dim C, cell As Range Dim lastNonEmptyCell As Range Dim inputRange As Range ' ユーザーにセル範囲を指定させるための InputBox を表示 On Error Resume Next Set inputRange = Application.…
Sub 検索結果を指定したひな形に転記してデスクトップに保存2() ' Zフォルダにある対象のExcelファイル(ブック)を開く Dim sourceFolder As String sourceFolder = "C:\Users\YourUsername\Documents\Zフォルダ\" ' Zフォルダのパスを指定 Dim sourceWorkb…
Sub 検索結果を指定のひな形に転記して保存() '配列バージョン ' Zフォルダにある対象のExcelファイル(ブック)を開く Dim sourceFolder As String sourceFolder = "C:\Users\YourUsername\Documents\Zフォルダ\" ' Zフォルダのパスを指定 Dim sourceWorkbo…
For j = 2 To lastRowTarget ' 指定ブックのA列を検索(ヘッダー行を除く) If wsTarget.Rows(j).Hidden = False Then ' 非表示の行を無視 If wsTarget.Cells(j, 1).Value = searchKey Then foundRow = j Exit For ' 一致した行が見つかったのでループを抜け…
Sub 罫線() Dim ws As Worksheet Dim LastRow As Long Dim LastCol As Long Dim i As Long Dim RangeToFormat As Range ' シートを指定(例: シート1) Set ws = ThisWorkbook.Sheets("Sheet1") ' 最終行(データがある最後の行)を取得 LastRow = ws.Cells(…
Sub リストの罫線() Dim ws As Worksheet Dim LastRow As Long Dim LastCol As Long Dim i As Long Dim RangeToFormat As Range ' シートを指定(例: シート1) Set ws = ThisWorkbook.Sheets("Sheet1") ' 最終行(データがある最後の行)を取得 LastRow = w…
Sub SkipErrorCells() Dim ws As Worksheet Dim cell As Range Dim result As Variant ' シートの指定 (ここではActiveSheetを使っています) Set ws = ActiveSheet ' 範囲を指定 (例えばA1からA10まで) For Each cell In ws.Range("A1:A10") On Error Resume…
VBAの用途別、適切なデータ型種類ガイドライン 参考に。。。 整数型: Integer: 小さな範囲の整数(-32,768 ~ 32,767)。 Long: 大きな整数(-2,147,483,648 ~ 2,147,483,647)。 浮動小数点型: Single: 浮動小数点数(精度が少し低い)。 Double: 高精度の…
Sub 転記() Dim folderPath As String Dim fileName As String Dim wb As Workbook Dim isOpen As Boolean Dim tempWb As Workbook Dim wsSource As Worksheet Dim wsTarget As Worksheet Dim lastRowSource As Long Dim lastRowTarget As Long Dim key As S…
Sub 転記() Dim folderPath As String Dim fileName As String Dim wb As Workbook Dim isOpen As Boolean Dim tempWb As Workbook Dim wsSource As Worksheet Dim wsTarget As Worksheet Dim lastRowSource As Long Dim lastRowTarget As Long Dim key As S…
Sub 転記() Dim folderPath As String Dim fileName As String Dim wbSource As Workbook ' アクティブブック Dim wsSource As Worksheet ' アクティブブックのシート Dim wbTarget As Workbook ' 指定したブック Dim wsTarget As Worksheet ' 指定したシー…
Sub 配列による転記() Dim folderPath As String Dim fileName As String Dim wbSource As Workbook ' アクティブブック Dim wsSource As Worksheet ' アクティブブックのシート Dim wbTarget As Workbook ' 指定したブック Dim wsTarget As Worksheet ' 指…
Sub データ更新() Dim folderPath As String Dim fileName As String Dim wb As Workbook Dim ws As Worksheet Dim targetWs As Worksheet Dim tempWs As Worksheet Dim lastRow As Long Dim targetRow As Long Dim i As Long Dim j As Long Dim conditionCo…
’~実例コード~ ' フィルターがかかっている場合、フィルターを解除 If wsData.AutoFilterMode Then wsData.ShowAllData ' フィルター解除 End If
Sub 特定の送信者からのメールtxt保存() Dim olApp As Outlook.Application Dim olNs As Outlook.Namespace Dim olFolder As Outlook.MAPIFolder Dim senderEmail As String Dim savePath As String Dim count As Integer Dim specifiedDate As Date Dim dat…
Sub 日付指定してDataから対象を抽出2() Dim wsSearch As Worksheet Dim wsResult As Worksheet Dim searchDate As Date Dim resultArray() As Variant Dim matchCount As Long Dim folderPath1 As String Dim folderPath2 As String Dim fileName As String…
Sub S日付指定してDataから対象を抽出() Dim wsData As Worksheet Dim wsSearch As Worksheet Dim wsResult As Worksheet Dim searchDate As Date Dim dataArray As Variant Dim resultArray() As Variant Dim i As Long, resultRow As Long Dim lastRow As …
Sub ExtractDataToSheet1AndCompare() Dim folderPath As String Dim wb As Workbook Dim ws1 As Worksheet Dim sampleWs As Worksheet Dim compatibilityWs As Worksheet Dim lastRow As Long Dim i As Long Dim regex As Object Dim compatLastRow As Long…
Sub TransferDataToOracleAndExportToExcel() Dim conn As Object Dim accessDb As DAO.Database Dim accessRs As DAO.Recordset Dim excelApp As Object Dim excelWorkbook As Object Dim excelWorksheet As Object Dim savePath As String Dim fileName As…
' CommonModuleOption Explicit Public folderPath As StringPublic saveFileName As StringPublic dateTimeStamp As String Sub フォルダ内条件による抽出() Dim fileName As String Dim wb As Workbook Dim ws As Worksheet Dim newWb As Workbook Dim new…
Sub フォルダ内bookシートの条件抽出() Dim folderPath As String Dim fileName As String Dim wb As Workbook Dim ws As Worksheet Dim newWb As Workbook Dim newWs As Worksheet Dim lastRow As Long Dim newRow As Long Dim i As Long Dim searchTextC A…