セル内で使われている数式を抜き出す。
シート１枚ずつ。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Sub test() Set s1 = ActiveSheet Sheets.Add after:=s1: Set s2 = ActiveSheet For r = 1 To s1.UsedRange.Rows.Count For c = 1 To s1.UsedRange.Columns.Count If InStr(s1.Cells(r, c).Formula, "=") > 0 Then s2.Cells(r, c).Value = "'" & s1.Cells(r, c).Formula Next c Next r End Sub |
全てのシート。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Sub test() Sheets.Add after:=ActiveSheet Set d = ActiveSheet i = 1 For Each w In Sheets If w.Name <> d.Name Then For r = 1 To w.UsedRange.Rows.Count For c = 1 To w.UsedRange.Columns.Count If InStr(w.Cells(r, c).Formula, "=") > 0 Then d.Cells(i, 1).Value = "'" & w.Cells(r, c).Formula d.Cells(i, 2).Value = "'" & w.Name i = i + 1 End If Next c Next r End If Next End Sub |