Microsoft ExcelでVBAを使用してカスタム数値書式でフォーマットデータ

Option Explicit

Sub Formatting()

'Declaring variables

Dim wks As Worksheet

Dim intRow As Long

Dim strCol As String

Dim txt As String

'Initializing the variables

Set wks = Worksheets("Format")

intRow = 4

'Looping in 16th column until empty value is found

Do Until IsEmpty(wks.Cells(intRow, 16))



'Assigning the value of custom number format

txt = wks.Cells(intRow, 17)



'Looping and finding all the column number separated by comma(,)

Do Until InStr(txt, ",") = 0



'Getting the column number

strCol = Left(txt, InStr(txt, ",") - 1)

'Assigning the number format

Columns(CInt(strCol)).NumberFormat = wks.Cells(intRow, 16).Value

'Truncating the string for finding the next column number after the comma(,)

txt = Right(txt, Len(txt) - InStr(txt, ","))

Loop



'Assigning the number format

Columns(CInt(txt)).NumberFormat = wks.Cells(intRow, 16).Value

intRow = intRow + 1

Loop

End Sub