excel - How to Return Values Using Rows and Columns as Variables (i, j)? -

i trying redesign table ease analysis , use of formulas in it. have list of customers , column each state. if customer has document state, cell filled in expiration date of document, otherwise cell remains empty. new table want create must bring list of documents customer , state. instead of having 50 columns 50 states of us, have column named "state" , column returning "expiration date" of document. new table ignore blank cells first table, means customer not have required document. built macro, runs no errors no changes. nothing happens. doing wrong?

sub allcertificates()  ' define variables     dim database range         set database = sheets("database").range("a2:ba10000")     dim databaseh range         set databaseh = sheets("database").range("a1:ba1")     dim nstates integer         nstates = database.columns.count     dim ncustomers long         ncustomers = database.rows.count     dim ncerts long         ncerts = application.worksheetfunction _             .count(sheets("database").range("j2:ba10000"))     dim ndata long         ndata = application.worksheetfunction _             .count(sheets("all certificates").columns(1)) + 1     dim long         = 1 ncustomers     dim j long         j = 6 nstates  ' transfer data "all certificates"     if database.cells(i, j).value = "*"         ' returns "customer id"         sheets("all certificates").cells(ndata, 1).value = database.cells(i, 1).value         ' returns "customer name"         sheets("all certificates").cells(ndata, 2).value = database.cells(i, 2).value         ' returns "state"         sheets("all certificates").cells(ndata, 3).value = databaseh.cells(1, j).value         ' returns "expiration date"         sheets("all certificates").cells(ndata, 4).value = database.cells(i, j).value         end if          next j         next  end sub 

see below should working version of code. corrections include:

1.) application.worksheetfunction.count counts numerical values. need use application.worksheetfunction.counta

2.) * character literal when used inside vba string, not wildcard. instead, use value <> "".

3.) need increment ndata value. it's not going recalculate original assignment unless place inside loop, it's computationally quicker increment n = n + 1.

4.) minor formatting things. pay special attention formatting loops , conditional statements or you'll wind typing wrong.

sub allcertificates() 'define variables dim database range     set database = sheets("database").range("a2:ba10000") dim databaseh range     set databaseh = sheets("database").range("a1:ba1") dim nstates integer     nstates = database.columns.count dim ncustomers long     ncustomers = database.rows.count dim ncerts long     ncerts = application.worksheetfunction.counta(sheets("database").range("c2:ba10000")) dim ndata long     ndata = application.worksheetfunction _         .counta(sheets("all certificates").columns(1)) + 1 dim long dim j long     = 1 ncustomers         j = 6 nstates         ' transfer data "all certificates"             if database.cells(i, j).value <> ""                 ' returns "customer id"                 sheets("all certificates").cells(ndata, 1).value = database.cells(i, 1).value                 ' returns "customer name"                 sheets("all certificates").cells(ndata, 2).value = database.cells(i, 2).value                 ' returns "state"                 sheets("all certificates").cells(ndata, 3).value = databaseh.cells(1, j).value                 ' returns "expiration date"                 sheets("all certificates").cells(ndata, 4).value = database.cells(i, j).value                 ndata = ndata + 1             end if         next j     next end sub 

---

note: question relatively specific. stackoverflow question supposed address issues many people can learn from, not one-off solution. think biggest lesson learn question how debug. little use of breakpoints, step-into (f8), , variable watching , have been able macro running on own no problem.

debugging (msdn)
breakpoints (wiseowl)

you may want consider editing original question and/or adding debug keyword since information needed general debugging. also, may want edit included problems fixes well. hope helps.