練習問題13（文字列関数の練習) メモ
Sub Practice13()
Dim i As Long
Dim strPref As String
Dim strCity As String
Range("G2:I8").ClearContents
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If InStr(Cells(i, 1), "(") > 0 Then
strPref = Left(Cells(i, 1), InStr(Cells(i, 1), "(") - 1)
Else
strPref = Cells(i, 1)
End If
If InStr(Cells(i, 2), "(") > 0 Then
strCity = Left(Cells(i, 2), InStr(Cells(i, 2), "(") - 1)
Else
strCity = Cells(i, 2)
End If
If Left(strPref, Len(strPref) - 1) = strCity Then
Cells(i, 3) = strPref
Else
Cells(i, 3) = strPref & "(" & strCity & ")"
End If
Next
End Sub
メモ:
型:
整数を入れる変数 → 長整数型(Long) 文字列を入れる変数 → 文字列型(String)
練習問題12（日付関数の練習）） メモ
Sub Pra12()
Dim i As Long
Dim intW As Integer
Range("G2:I8").ClearContents
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
intW = Weekday(DateSerial(Cells(i, 1), Cells(i, 2), Cells(i, 3)), vbMonday)
Cells(intW + 1, 7) = Cells(intW + 1, 7) + Cells(i, 4)
Cells(intW + 1, 8) = Cells(intW + 1, 8) + 1
Next
For i = 1 To 7
Cells(i + 1, 9) = Cells(i + 1, 7) / Cells(i + 1, 8)
Next
End Sub
メモ:
*intW → 変数の名前
*ClearContents→ 数式と文字を削除する
*DateSerial→ (西暦, 月, 日付)
For i = 1 To 7
Cells(i+1,7) = Cells(i+1,7) / Cells(i+1,8)
→ 仮に月曜日だとすれば, Cells(月曜日の+ 平均 売上) = Cells(月曜日の+ 売上合計) / Cells(月曜日の+日数) と覚える
練習問題11（色々なコピーの練習） メモ
Sub 練習問題11()
Range("A1:B6").Copy Range("D1")
Range("A1:B6").Copy
Range("G1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub
練習問題10（罫線の練習）メモ
Sub 練習問題10()
Dim i As Long
Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastRow
Cells(i, 4) = Cells(i, 2) / Cells(i, 3)
Next
Range(Cells(2, 4), Cells(lastRow, 4)).NumberFormatLocal = "#,##0.00"
Range(Cells(1, 1), Cells(lastRow, 4)).Borders.LineStyle = xlContinuous
Range(Cells(1, 1), Cells(lastRow, 4)).Borders.Weight = xlHairline
Range(Cells(1, 1), Cells(lastRow, 4)).BorderAround Weight:=xlMedium
Range(Cells(2, 1), Cells(2, 4)).Borders(xlEdgeTop).Weight = xlThin
Range(Cells(1, 2), Cells(lastRow, 2)).Borders(xlEdgeLeft).Weight = xlThin
End Sub
練習問題9（文字色・背景色の練習） メモ
Sub Practice9()
Dim i As Long
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Cells(i, 4) = Cells(i, 3) / Cells(i, 2)
Select Case Cells(i, 4)
Case Is >= 1.05
Cells(i, 4).Interior.Color = vbBlue
Cells(i, 4).Font.Color = vbWhite
Case Is >= 1
Cells(i, 4).Font.Color = vbBlue
Case Is >= 0.95
Cells(i, 4).Font.Color = vbBlack
Case Is >= 0.9
Cells(i, 4).Font.Color = vbRed
Case Else
Cells(i, 4).Interior.Color = vbRed
Cells(i, 4).Font.Color = vbBlack
End Select
Next
End Sub
