94 expression True False expression FalseMSDN IsNumber WorksheetFunctionIsNumberexpression expression True Office support.office.com/ja-jp/ S

Size: px

Start display at page:

Download "94 expression True False expression FalseMSDN IsNumber WorksheetFunctionIsNumberexpression expression True Office support.office.com/ja-jp/ S"

ためひとひでやま
5 years ago
Views:

1 Excel VBA a Excel VBA VBA IsNumeric IsNumber SpecialCells SpecialCells MSDNMicrosoft Developer NetworkIsNumeric IsNumber SpecialCells IsNumeric VBA IsNumericexpression SpecialCells

2 94 expression True False expression FalseMSDN IsNumber WorksheetFunctionIsNumberexpression expression True Office support.office.com/ja-jp/ SpecialCells Range SpecialCellsxlCellTypeConstantsxlNumbers IsNumeric IsNumber expression Range Range IsNumeric IsNumber SpecialCells IsNumeric IsNumber True CDbl Double SpecialCells IsNumeric IsNumber IsNumeric MSDN Value SpecialCells SpecialCellsxlCellTypeFormulasxlNumbers

3 Excel VBA 95 IsNumeric TypeName Value Text IsNumber SpecialCells Double String String Double Currency Empty String String String String String FALSE Boolean Boolean TRUE Boolean Boolean Date Boolean Text Boolean IsNumeric Value Text IsNumber IsNumeric IsNumber IsNumetric IsNumeric IsNumber Excel VBA IsNumeric

4 96 IsNumber Excel VBA Text IsNumeric Text IsNumeric a Function F_InputF_TableF_Check Range Data Data Table a

5 Excel VBA 97 If k Then mymsg mybtn MsgBoxmyMsgvbYesNomyTitle TableSpecialCellsxlCellTypeBlanksInteriorColorIndex If mybtn vbyes Then TableSpecialCellsxlCellTypeBlanksValue End If End If Range Table Function F_Input Range Data Set Function F_Table F_Table DataRowsCountData DataColumnsCountData DataValueTableValue ForNext k ColorIndex a Excel VBA Count a Range DataRows Range Data Range Count a Set Table Data Set Table Data Data Table Data

6 98 k MsgBox vbyes Excel VBA SpecialCells TableSpecialCellsxlCellTypeBlannks Table Range Range SpecialCells ForNext k IsNumeric TableiText Boolean cisnumeric c True Text IsNumber TableiValue Boolean cisnumber c True IsNumber k For i To TableCount c IsNumericTableiText c WorksheetFunctionIsNumberTableiValue If Not c Then TableiValue SpecialCells Range

7 Excel VBA 99 If c AndNot c Then If TypeNameTableiValue CurrencyThen GoTo Label TableiInteriorColorIndex mycolor k k If k Then Set r Tablei Else Set r UnionrTablei End If End If Label Next i IsNumeric Not c c AndNot c Data Table SpecialCells xlcelltypeblannks c False amsdn RangeValue Variant Value PrefixCharacter Moug

8 100 SpecialCells SpecialCells Union k Range r Union r r Range k MsgBox vbyes For Each Next CDbl If k Then mymsg mybtn MsgBoxmyMsgvbYesNomyTitle If mybtn vbno Then Exit Sub If mybtn vbyes Then rinteriorcolorindex For Each r In r rvalue CDblrValue Next r End If End If Application Range

9 Excel VBA 101 BEBC G Table Excel

10 102 Dim Data As Range Dim Table As Range Dim n As LongData Dim c As Longr As LongData cr Dim n As Long Dim mycut As Long Dim iend As Long Function F_Input Data SpecialCells nspecialcells c Or r InputBox mycutiend iend n mycut If n Mod mycut Then iend iend Excel

11 Excel VBA 103 nmod Visual Basic afunction F_Check Excel VBA Range index index index Range Function F_Table TableiEndmyCut Table n Data Table S_Format Sub S_FormatSource As RangeTarget As Range Source Target DimiAsLongk As Long k For i To SourceCount If SourceiValue Then TargetkValue SourceiValue k k End If Next i End Sub Mod

12 104 aspecialcells Range Range Range SpecialCellsTypeValue Type xlcelltypeblanks Table.SpecialCellsxlCellTypeBlanksInteriorColorIndex Table.SpecialCellsxlCellTypeBlanksValue xlcelltypeconstants xlcelltypeformulas Type xlcelltypeconstants xlnumbers n DataSpecialCellsxlConstantsxlNumbersCount SpecialCells Excel VBA SpecialCellsxlCellTypeBlanks SpecialCells xlconstants Range Range MSDN RangeSpecialCells Moug

13 Excel VBA 105 SpecialCellsxlCellTypeBlanks SpecialCells SpecialCells Range Range Sub Sample SpecialCells Dim r As Range Dim n As Long Dim n As Long Dim n As Long WorksheetsAdd RangeBValue RangeCValue Set r RangeBC RangeE RangeEDeleteE rborderslinestyle xlcontinuous rinteriorcolorindex LastCell RangeASpecialCellsxlCellTypeLastCell InteriorColorIndex n rcount n rspecialcellsxlcelltypeconstantsxlnumberscount

14 106 n rspecialcellsxlcelltypeblankscount rborderaround Weight xlmedium MsgBox & n & & vbcrlf & & n & & vbcrlf & & n & RangeBESpecialCellsxlCellTypeBlanksSelect End Sub SpecialCellsxlCellTypeBlanks SampleBC LastCell C AC BC Range r SpecialcellsxlCellTypeLastCellLastCell WorksheetUsedRange Range

15 Excel VBA 107 AC BC Specialcells AC SpecialCellsxlCellTypeBlanks E LastCell E AE BC SpecialCellsxlCellTypeBlanks BE AE BE

16 108 SpecialCells LastCell LastCell CellsValue LastCell SpecialCells SpecialCells SpecialCellsxlCellTypeBlanks SpecialCells SpecialCells SpecialCells SpecialCells Sample Sub Sample SpecialCells Cancel Dim r As Range Dim mytitle As String Dim mymsg As String WorksheetsAdd CellsRowsCountColumnsCount LastCell

17 Excel VBA RangeBValue RangeCValue Label mytitle SpecialCells mymsg Set r F_InputmyMsgmyTitle If r Is Nothing Then Exit Sub Set r rspecialcellsxlcelltypeconstants Set r rspecialcellsxlcelltypeblanks rselect MsgBox rcount GoTo Label End Sub 109 Sample AC a Function F_Input SpecialCells xlcelltypeblanksrange

18 110 SpecialCells xlcelltypeconstants B C SpecialCells InputBox Excel VBA SpecialCells SpecialCells LastCell SpecialCells Range ApplicationIntersect Sample SpecialCells Range

19 Excel VBA 111 SpecialCellsxlCellTypeConstants S_Format Sub S_FormatSource As RangeTarget As Range SpecialCellsxlCellTypeConstants Dim r As Range Dim r As Range DimiAsLong Dim k As Long Set r SourceSpecialCellsxlCellTypeConstants k For Each r In r TargetkValue rvalue k k Next r End Sub SpecialCellsxlCellTypeConstants r For EachNext Call S_FormatDataTable

20 112 BB D Range index

21 Excel VBA 113 SpecialCellsxlCellTypeConstants Range BC SpecialCellsxlCellTypeConstants BC SpecialCells

22 114 a Excel VBA IsNumeric IsNumber Excel VBA SpecialCells SpecialCells Range SpecialCells Excel VBA a Excel VBA ExcelVBA Excel VBA Excel VBA Excel VBA Excel VBA Office TANAKA Moug MSDNMicrosoft Developer Network

Excel VBA 115 Sub Sample Dim i As Integer RangeBFClear For i To With Cellsi CellsiValue TypeNameValue If IsNumericValueThen CellsiValue CDblValue End If If IsNumericTextThen

23 Excel VBA 115 Sub Sample Dim i As Integer RangeBFClear For i To With Cellsi CellsiValue TypeNameValue If IsNumericValueThen CellsiValue CDblValue End If If IsNumericTextThen CellsiValue CDblText End If If WorksheetFunctionIsNumberValueThen CellsiValue CDblValue End If IfVarTypeValueAndVarTypeValueThen CellsiValue CDblValue End If End With Next i End Sub

24 116 Sub DimDataAsRange Dim Table As Range Dim i As Long Dim k As Long Dim c As Boolean Dim c As Boolean Dim r As Range Dim r As Range Dim mytitle As String Dim mymsg As String Dim mybtn As Integer Const mycolor As Integer mytitle mymsg Set Data F_InputmyMsgmyTitle If Data Is Nothing Then Exit Sub Set Table F_TablemyTitleDataRowsCountDataColumnsCount If Table Is Nothing Then Exit Sub TableValue DataValue TableWorksheetSelect TableSelect k For i To TableCount If TableiValue Then TableiInteriorColorIndex mycolor

25 Excel VBA 117 k k End If Next i If k Then mymsg mybtn MsgBoxmyMsgvbYesNomyTitle TableSpecialCellsxlCellTypeBlanksInteriorColorIndex If mybtn vbyes Then TableSpecialCellsxlCellTypeBlanksValue End If End If k For i To TableCount c IsNumericTableiText c WorksheetFunctionIsNumberTableiValue If Not c Then TableiValue If c AndNot cthen If TypeNameTableiValue CurrencyThen GoTo Label TableiInteriorColorIndex mycolor k k If k Then Set r Tablei Else Set r UnionrTablei End If End If Label Next i If k Then mymsg mybtn MsgBoxmyMsgvbYesNomyTitle If mybtn vbno Then Exit Sub If mybtn vbyes Then rinteriorcolorindex For Each r In r rvalue CDblrValue Next r End If End If End Sub

26 118 Sub DimDataAsRange Dim Table As Range Dim n As LongData Dim c As Longr As LongData cr Dim n As Long Dim mycut As Long DimiEndAsLong Dim mytitle As String Dim mymsg As String Dim mybtn As Integer Dim i As Longj As Longk As Long Dim v As Variant On Error GoTo myerror mytitle mymsg Set Data F_InputmyMsgmyTitle If Data Is Nothing Then Exit Sub DataSelect On Error Resume Next n DataSpecialCellsxlConstantsxlNumbersCount If ErrNumber Then MsgBox Exit Sub End If On Error GoTo Data n DataCount

27 Excel VBA 119 c DataColumnsCount r DataRowsCount MsgBox & n&& vbcrlf & & n & If c Or r Then Label mymsg CancelExit v ApplicationInputBoxmyMsgmyTitleType If TypeNamev BooleanThen Exit Sub mycut v If mycut n Or mycut Then MsgBox GoTo Label End If iend n mycut If n Mod mycut Then iend iend End If mymsg mybtn MsgBoxmyMsgvbYesNomyTitle If mybtn vbyes Then WorksheetsAdd If c Or r Then Set Table F_TablemyTitleiEndmyCut Else Set Table F_TablemyTitlen End If If Table Is Nothing Then Exit Sub Call S_FormatDataTable Exit Sub myerror MsgBox & ErrNumber & vbcrlf & & ErrDescriptionvbExclamation End Sub

28 120 Sub S_FormatSource As RangeTarget As Range SourceTarget Dim i As Longk As Long k For i To SourceCount If SourceiValue Then TargetkValue SourceiValue k k End If Next i End Sub

29 Excel VBA 121 Sub Sample SpecialCells Cancel Dim r As Range Dim r As Range Dim mytitle As String Dim mymsg As String WorksheetsAdd RangeBValue RangeCValue LastCell CellsValue LastCell Label mytitle SpecialCells mymsg Set r F_InputmyMsgmyTitle If r Is Nothing Then Exit Sub Set r rspecialcellsxlcelltypeconstants Set r rspecialcellsxlcelltypeblanks Intersect Set r Intersectrr rselect MsgBox rcount GoTo Label End Sub

30 122 Sub Sample An n SpecialCells n An n Dim r As RangeData Dim r As RangeSpecialCells Dim r As RangeFor EachNext Dim r As Range Dim s As StringFunction Combination Dim r As Variant Split r Dim r As Variant Split Dim i As Longj As Long Dim n As Longn n Dim n As Long Dim l As Long n n Dim mymsg As String Dim k As Long Dim k As Long Dim k As Long Dim mybtn As Long CellsClear n Data Set r CellsResizenn n WorksheetFunctionCombinn nintn n / Set r F_Tablenn n k k For l To n n s Combinationn nlfunction Combination

31 Excel VBA 123 r SplitsvbCrLf Split Variant For i To UBoundr rcleardata r Splitri Split For j To UBoundr rrj rjdata Next j SpecialCells Set r rspecialcellsxlcelltypeconstants mymsg k mybtn SpecialCells mybtn For Each r In r mymsg mymsg & rtext & If rvalue k Then k rvalue Else mybtn End If Next r ri lvalue mymsg k k If mybtn Then k k ri linteriorcolorindex End If Next i Next l rentirecolumnautofit RangeAValue RangeCValue k RangeAValue RangeCValue k End Sub Function CombinationMax As LongCount As LongOptional Min As Long Optional Preceding As String As String

32 124 Combination Dim i As LongBuffer As String If Max Min Count Then Combination Error ElseIf Count Then Count Combination Preceding & vbcrlf ElseIf Max Min Count Then Buffer Preceding For i MinToMax Buffer Buffer & & i Next i Combination Buffer & vbcrlf Else Buffer For i MinToMaxCount Buffer Buffer & CombinationMaxCount i Preceding & & i Next i Combination Buffer End If End Function Sub MsgBox Combination End Sub

ExcelVBA Excel VBA Microsoft Excel VBA Visual Basic for Applications Excel Excel VBA ExcelVBA Excel ExcelVBA Excel VBA Excel ExcelVBA ExcelVBA VBA Ran

ExcelVBA Excel VBA Microsoft Excel VBA Visual Basic for Applications Excel Excel VBA ExcelVBA Excel ExcelVBA Excel VBA Excel ExcelVBA ExcelVBA VBA Ran Excel VBA Microsoft Excel VBA Visual Basic for ApplicationsExcel Excel VBA Excel ExcelVBA Excel VBA Excel ExcelVBA VBA Range Excel 174 VBA VisualBasicforApplications Microsoft ExcelWordAccessPowerPoint