excel vba code to replace empty cell with text based on adjacent cells number range -

-

please me, i'm frustrated excel. because i'm not it. i've been tasked following. take blank cells in column c, reference value in adjacent cell in column b, , print statement in cell.

i tried function no avail.

=if(and(c3 = "", 0 < b3 < 5000), "order more", "don't order")

i got circular error. thought must need use vba.

here snippets of code have far:

worksheets("sheet1").activate  each rw in sheet1.rows if rw.columns("c") = "" , range.rw.columns("b")

i'm not sure how make work. don't know if that's correct. i've been trying use internet it's more confusing.

posting response since

  • it cumbersome explain on comment

  • it tested , works excel 2010.

    sub test()  dim lfirstblank long, llastrow long dim rrange range  llastrow = cells(rows.count, 3).end(xlup).row lfirstblank = _ range("c3:c" & llastrow).specialcells(xlcelltypeblanks).cells(1, 1).row  set rrange = range("c" & lfirstblank & ":c" & _     llastrow).specialcells(xlcelltypeblanks)  rrange.formula = _ "=if(and(b" & lfirstblank & ">0,b" & lfirstblank & "<5000)," & chr(34) & _ "order more" & chr(34) & "," & chr(34) & "don't order" & chr(34) & ")"  rrange.value = rrange.value   end sub

the problem need find first blank cell , enter formula there reference relative cell (first blank), understand latest response. instance, if first blank cell c5, formula should refer c5 , b5, not c3. above code works expected, assuming sheet looking @ activesheet (i.e., activated).

edit

if want remove dependency on column b, can add line

rrange.value = rrange.value

this remove formula dependencies , keep values (like pasting values).

the reasons why did not write formula 

=if(and(c3 = "", 0 < b3, b3 < 5000), "order more", "don't order")

are that

  • it going give circular reference error, because using value of c3 determine value of c3.
  • even if avoid (or ignore) circular reference, not seem want exactly: want to apply logic 0 < b3 < 5000 blank cells mention in question, , return either "order more" or "don't order". understanding non-blank cells should not touched, whereas above formula overwrite whatever written on non-blank cells , write there "don't order".

i hope helps!


Comments

Post a Comment

Popular posts from this blog

database - VFP Grid + SQL server 2008 - grid not showing correctly -

-
i having problem, have grid called "gridbasica" , this: thisform.gridbase.recordsource = "sgviemp" sgviemp cursor made executing select sqlserver command sqlexec. when want modify registry, go form , the update there. so, when come form grid, grid isnt grid anymore, big white rectangle. reading around said: delete recordsource , asingt again. thisform.gridbase.recordsource = "" *--i sqlexe here thisform.gridbase.recordsource = "sgviemp" i , works, but, when click button modify again cursos updated modify form doesnt load fields did first time. could me out that? thanks in advance. --------------- edit ------------------ i have been doing recommended: lnselect = select(0) n = sqlexec(thisform.conexion,"select emp_ccodigo codigo, emp_cnombre 'nombre o razon social', emp_cnumrif 'r.i.f' sgviempr","sgviemp1") set echo on suspend select sgviemp zap in sgviemp *-- browse *--zaps correct
Read more

jquery - Set jPicker field to empty value -

-
im using jpicker color selector in form. have clear value set window/icon 'no color selected' state. i've tried following: $(':input[name="color"]').val(''); //access value directly. (does not affect jpicker) 2: $.jpicker.list[0].color.active.val('hex', '', this); //set color black , input value 000000, instead of empty string 3: $.jpicker.list[0].color.active.val(null, '', this); //does nothing none of 3 methods seems behave properly. in advance. this works me :) $.jpicker.list[0].color.active.val('v', null); here example: http://jsfiddle.net/3up7n/
Read more

.htaccess - htaccess convert request to clean url and add slash at the end of the url -

-
ok , searched more 4 days , more 20 posts here in stach or in websites without kind of success first here htaccess file rewriteengine on rewriterule ^([a-za-z0-9_]+)/?$ index.php?page=$1 [nc] rewriterule ^([a-za-z0-9_]+)\/([a-za-z0-9_]+)\/?$ index.php?page=$1&action=$2 [nc] i tried many codes 1 worked me can call request , works me www.example.com/pagename => www.example.com/index.php?page=pagename here questions , please provide me information "in detail" understand code well 1- how convert request automatically www.example.com/index.php?page=pagename www.example.com/pagename 2- if tried add slash @ end shows me page without images or styling mean www.example.com/pagename => works www.example.com/pagename/ => doesn't work that's :) for first part need 301 rule this: rewritecond %{the_request} /index\.php\?page=([^\s&]+) [nc] rewriterule ^ /%1? [r=301,l] for second part use absolute path in css, js, images files r
Read more