How to set a block variable in excel vba -

i getting run-time error: 91 "object variable or block variable not set

here code, don't did wrong

with sheet1 lastrowcell = range("b" & rows.count).end(xlup).row pr_high = 14 n = pr_high lastrowcell  max = worksheetfunction.max(range(cells(n - pr_high_1, 6), cells(n, 6)))  if max > 0     rownum = .columns(6).find(what:=max, after:=.cells(n - period_high, 6), lookin:=xlformulas, lookat:=xlwhole, searchorder:=xlbyrows, searchdirection:=xlnext, matchcase:=false).row     '>>>>at above line i'm getting error.      range(cells(n - pr_high_1, 8)).formula = "=count(" & range(cells(n - pr_high_1, 6), cells(rownum, 6)).address(false, false) & ")" end if  next 

thanks in advance.

edited after op's full code posting

i think found 2 incorrect lines in function calculate_high_low, namely

1) in "count maximum high" block

rownum = .columns(6).find(what:=max, after:=.cells(n - period_high, 6), lookin:=xlformulas, lookat:=xlwhole, searchorder:=xlbyrows, searchdirection:=xlnext, matchcase:=false).row 

should be

rownum = .columns(6).find(what:=max, after:=.cells(n - period_high, 6), lookin:=xlvalues, lookat:=xlwhole, searchorder:=xlbyrows, searchdirection:=xlnext, matchcase:=false).row 

since looking @ cell formula rather @ content , wouldn't catch value looking for

2) in "count minium low" block

rownum2 = .columns(6).find(what:=mymin, after:=.cells(n - period_high, 6), lookin:=xlformulas, lookat:=xlwhole, searchorder:=xlbyrows, searchdirection:=xlnext, matchcase:=false).row 

should be

rownum2 = .columns(6).find(what:=min, after:=.cells(n - period_low, 6), lookin:=xlvalues, lookat:=xlwhole, searchorder:=xlbyrows, searchdirection:=xlnext, matchcase:=false).row 

since looking @ cell formula rather @ content , wouldn't catch value looking for

moreover using period_high instead of period_low.

but other (i'd more), that, you'd better:

• use option explicit statement @ top of module

  it'll force work , define variables , type make both debugging , code maintenance easier. instance it'll have variable names errors pop out, both pr_high vs pr_high_1 , (maybe) period_high vs period_high

• use qualified reference ranges, worksheet , workbook ones sure you're point @ right worksheet of right workbook, otherwise they'd default active sheet , workbook

so here follows function calculate_high_low refactored above only:

public function calculate_high_low(com_tick_ask string, rng variant)  dim lastrowcell long, rownum long, rownum2 long dim pr_high long, period_high long, period_low long, pr_low long, pr_high_1 long, pr_low_1 long, n long dim max double, min double  dim calcsht worksheet       range("a:a,e:e,f:f,h:h").copy '<=it's referring active sheet which, it's called sub "commandbutton1_click" above, sheet last opened ".csv" file shows     worksheets.add      set calcsht = activesheet 'it's added new sheet      calcsht          .range("a1").pastespecial xlpastevaluesandnumberformats         application.cutcopymode = false         'range("c1").value = "formula high"         'range("d1").value = "count high"         'range("e1").value = "formula low"         'range("f1").value = "count low"          .range("1:1").font.fontstyle = "bold italic"          lastrowcell = .range("b1", .range("b1").end(xldown)).count         'lastrowcell = .range("b1:b" & .cells(.rows.count, 2).end(xldown).row).count          'count average of high , low         .range("f2:f" & lastrowcell).formula = "=average(" & .range("b2:c2").address(false, false) & ")"          'long         period_high = high_low.period_1.text         period_low = high_low.period_2.text          pr_high = period_high + 1         pr_low = period_low + 1          pr_high_1 = period_high - 1         pr_low_1 = period_low - 1           'count maximum high         n = pr_high lastrowcell             max = worksheetfunction.max(range(.cells(n - pr_high_1, 6), .cells(n, 6)))              if max > 0                rownum = .columns(6).find(what:=max, after:=.cells(n - period_high, 6), lookin:=xlvalues, lookat:=xlwhole, searchorder:=xlbyrows, searchdirection:=xlnext, matchcase:=false).row             'rownum = worksheetfunction.max(range(cells(n - period_high, 6), cells(n, 6)))                range(.cells(n - pr_high_1, 8), .cells(lastrowcell, 8)).formula = "=count(" & range(.cells(n - pr_high_1, 6), .cells(rownum, 6)).address(false, false) & ")"             end if         next            'count minium low         n = pr_low lastrowcell             min = worksheetfunction.min(range(.cells(n - pr_low_1, 6), .cells(n, 6)))              if min > 0 '                rownum2 = .columns(6).find(what:=mymin, after:=.cells(n - period_high, 6), lookin:=xlformulas, lookat:=xlwhole, searchorder:=xlbyrows, searchdirection:=xlnext, matchcase:=false).row                 rownum2 = .columns(6).find(what:=min, after:=.cells(n - period_low, 6), lookin:=xlvalues, lookat:=xlwhole, searchorder:=xlbyrows, searchdirection:=xlnext, matchcase:=false).row                 'rownum2 = worksheetfunction.min(range(cells(n - period_high, 6), cells(n, 6)))                 range(.cells(n - pr_low_1, 10), .cells(lastrowcell, 10)).formula = "=count(" & range(.cells(n - pr_low_1, 6), .cells(rownum2, 6)).address(false, false) & ")"             end if          next           'calculate formula high & low          'for high         .range("g2:g" & lastrowcell).value = "=(" & period_high & "-" & .range("h2").address(false, false) & ")/" & period_high          'for low         .range("i2:i" & lastrowcell).value = "=(" & period_low & "-" & .range("j2").address(false, false) & ")/" & period_low        end     high_low.hide  end function 

as said, should starting point you, since whole code posted lack coding pattern , fail step right after one, should above fix latter altogether.

but if follow proposed pattern should more catch possible issues, logic 1 since clear code can have concentrate on algorithm has develope