VBA 必要な行にだけ関数を設定

何行データがあるかわからないシートに、データのある行にだけ、関数をセットしたいことはよくある。

簡単なのはあらかじめ適用な行数分、関数をセットしておくことですが、ちゃんと必要な行数分関数がセットされているか確認しないと漏れてしまう。

それをマクロで自動設定する。

'最終行を取得
checkcol = 4
lastrow = Cells(Rows.Count , checkcol ).End(xlUp).row

'先頭行のセルに関数をセット
startrow = 3
setcol = 3
Cells(startrow,setcol).Value = "row() - " & startcol + 1

'関数を必要な行数分コピー
Cells(startrow,setcol).Copy
Range(Cells(startrow,setcol),Cells(lastrow,setcol).Select
ActiveSheet.Paste

VBA 最後の行取得とループ処理

値の入っている表に対して、値の入っている一番最後の行を取得して処理を行う。

・値の入っている最後の行 
例）B列に値が設定されている一番最後の行番号
'値が入っていることを評価する列番号 ２＝B列
col = 2 
lastrow = Cells(Rows.Count , col ).End(xlUp).row

・最後の行までループ処理
例）３行目からD列に値が設定されている一番最後の行までループして、A列に番号をセットする

startrow = 3
checkcol = 4
rownum = 1
lastrow = Cells(Rows.Count , checkcol ).End(xlUp).row

for row = startrow to lastrow
    Cells(row , 1).Value = rownum
    rownum = rownum + 1

Next row

Netezza(ネティーザ) 項目一覧

IBMのDB、Netezzaで、Oracleの user_tab_columns のように、テーブル一覧を取得する。

・_V_RELATION_COLUMN
OWNER　：　オーナー
NAME　：　テーブル名
ATTNUM　：　項目番号
ATTNAME　：　項目名
FORMAT_TYPE　：　項目型（桁、小数桁）
ATTCOLLENG　：　項目長
ATTNOTNULL　：　Null（'t'=NotNull / 'f'=Null）

・_V_DOTNET_PRIMARYKEYS1
TABLE_SCHEMA　：　テーブルスキーマ
TABLE_NAME　：　テーブル名
COLUMN_NAME　：　項目名
KEY_SEQ　：　プライマリーキー項目順
PK_NAME 　：　プライマリーキー名

＊　プライマリーキーの情報がどこに格納されているか不明だったが、とりあえずデータを見つけられたので採用。他環境でも同様に取得できるかは不明。

select
A.OWNER
,A.NAME
,A.ATTNUM
,A.ATTNAME
,A.FORMAT_TYPE
,A.ATTCOLLENG
,A.ATTNOTNULL
,B.KEY_SEQ
 from _V_RELATION_COLUMN A
left outer join _V_DOTNET_PRIMARYKEYS1 B
on A.NAME = B.TABLE_NAME
and A.ATTNAME = B.COLUMN_NAME
;

このままでは、桁と小数桁が項目型に混じってしまっているため切りだす。

select
A.OWNER
,A.NAME
,A.ATTNUM
,A.ATTNAME
,case
when position('(' in A.FORMAT_TYPE) != 0 
  then substr(A.FORMAT_TYPE,1,position('(' in A.FORMAT_TYPE)-1)
 else A.FORMAT_TYPE
end as ATTFORMAT  
,case 
when substr(A.FORMAT_TYPE,1,4) = 'NUME'
  then to_number(translate(substr(A.FORMAT_TYPE,9,2),',',''),'99')
else A.ATTCOLLENG
end as ATTLENGTH
,case 
when substr(A.FORMAT_TYPE,1,4) = 'NUME'
  then to_number(substr(A.FORMAT_TYPE,position(',' in A.FORMAT_TYPE) + 1 ,1),'9')
else 0
end as ATTSCALE
,case
when A.ATTNOTNULL = 't' then 'NotNull'
else 'Null'
end as ATTNULL
,B.KEY_SEQ
 from _V_RELATION_COLUMN A
left outer join _V_DOTNET_PRIMARYKEYS1 B
on A.NAME = B.TABLE_NAME
and A.ATTNAME = B.COLUMN_NAME
;

Netezza(ネティーザ) テーブル一覧

IBMのDB、Netezzaで、Oracleの user_tables のように、テーブル一覧を取得する。

・_v_table
OWNER　：　オーナー
TABLENAME　：　テーブル名
CREATEDATE　：　作成日時

select
OWNER
,TABLENAME
,to_char(cast(CREATEDATE as timestamp),'YYYY/MM/DD')
from
_v_table
;

補足）
・Createされた日しか取得できません。Alterされた日はもっていませんでした。
・Createされた日をCREATEDATEに持っていますが、この項目はABSTIMEという特殊なタイムスタンプ型の項目で、to_char関数が使えません。
一度、cast関数で"TIMESTAMP"型に変換する必要があります。

VBA 基本編

・シートの選択
Sheets(“シート名”).Select

・１つのセルの選択
Sheets(“シート名”).Range(“A2”).Select
Sheets(“シート名”).Cells(2,1).Select
↑　２つの指定方法は同じセルを選択している

・Cellsの使い方
Cells(行数 , 列数)

・１つのセルを選択する場合のRangeとCellsの使い分け
Range は、excelのシート上で、指定するセルの場所がわかっている場合、使いやすいCellsは、行や列のループ処理や計算式でセルの位置を指定する場合に使いやすい

・Cellsを使う時の行数確認方法
Excelは通常、列は行と区別するため、アルファベット表示で使っているが、列の表示を数字に変更する事ができる。


・古いExcel
オプション　＞　"全般"タブ　＞　"R1C1参照形式を使用する"
　チェックなし(デフォルト)　：　A1形式で表示
　チェックあり　　　　　　　：　11形式で表示


 ・新しいExcel

チェックをつけた状態だと、セルの参照方法が、相対参照になってしまうので、確認が終わったら、チェックを外すことをおすすめします。