| |
こんにちわ。
下記はあるエクセルファイルを整形するマクロなのですが
エラー242 オブジェクトが必要ですというエラーが
でたりでなかったりでうまく動作しません。エラーがでる行
は15行目の2つ目の「If c.Value Like "*" & fndkey2 & "*" Then Rows(c.Row).Delete Shift:=xlUp」です。
どなたかご教授お願いいたします。
Sub test()
Dim c As Variant
Dim rng As Range
Dim myadd As String
Workbooks.Open Filename:="book1.xls"
'特定の文字列を含む行を削除
'削除する文字を指定
fndkey1 = "名前"
fndkey2 = "日付"
fndkey3 = "vssver.scc"
For Each c In Sheets(1).Range("a1:a6000")
'行削除
If c.Value Like "*" & fndkey1 & "*" Then Rows(c.Row).Delete Shift:=xlUp
If c.Value Like "*" & fndkey2 & "*" Then Rows(c.Row).Delete Shift:=xlUp
If c.Value Like "*" & fndkey3 & "*" Then Rows(c.Row).Delete Shift:=xlUp
Next
'ファイル名以外を削除する
'ファイル名以前を削除
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
myadd = rng.Address
sushiki = "=if(iserror(FIND(""AB""," & myadd & "))," & _
"if(" & myadd & "="""",""""," & myadd & ")," & _
"MID(" & myadd & ",FIND(""AB""," & myadd & "),LEN(" & myadd & ")))"
' ファイル名以降を削除
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
myadd = rng.Address
sushiki = "=if(iserror(FIND(""Date""," & myadd & "))," & _
"if(" & myadd & "="""",""""," & myadd & ")," & _
"substitute(" & myadd & _
",MID(" & myadd & ",FIND(""Date""," & myadd & "),LEN(" & myadd & ")),""""))"
Debug.Print sushiki '数式を確認して下さい
rng.Value = Evaluate(sushiki)
ActiveWorkbook.Save
End Sub
|
|
![[未読]](./data/excel/image/read_icon.gif){kind=icon}
