excel - VBA passing variable to function -


final goal: trying edit existing text in column upper case proper (upper case)- exception whereby if word follows hyphen will-remain lower case. outputting 2 columns across.

my first sub needs pass active cell function applies formatting (function tested , works).

i don't know how make work.

option explicit  dim txt string dim long dim strtest string dim strarray() string dim lcaseon boolean dim firstrow long, startit long dim thiscell range dim lastrow long  sub throughcols()  dim thiscell range  datarange startit = firstrow + 1  = startit lastrow     ' no idea how pass cell picking through function     thiscell = sheets("names").select: range("b" & i).select     arraymanip (thiscell)     'i need pass activecell arraymanip- not sure how next  end sub  '====================================================================  function arraymanip(thiscell)      'hard coded source cell testing     ' trying itterate through column      ' clear out data     erase strarray     txt = ""      'set default case     lcaseon = false      ' string array using " " separator     strtest = worksheetfunction.proper(activecell.value)     strtest = replace(strtest, "-", " - ")     strarray = split(strtest, " ")      ' itterate through array looking make text foll     = lbound(strarray) ubound(strarray)         if strarray(i) = "-"             lcaseon = true             goto nextiteration         end if          if lcaseon             strarray(i) = lcase(strarray(i))             lcaseon = false nextiteration:         end if          ' loop through array , build text string output message box         txt = txt & strarray(i) & " "          ' remove space         txt = trim(replace(txt, " - ", "-"))         activecell.offset(0, 2).select: activecell.value = txt     next  ' msgbox txt  end function  '====================================================================  sub datarange()  sheets("names").columns("b")     if worksheetfunction.counta(.cells) = 0 '<--| if no data whatever         msgbox "sorry: no data"     else         .specialcells(xlcelltypeconstants) '<--| reference cells constant (i.e, not derived formulas) values)             firstrow = .areas(1).row             lastrow = .areas(.areas.count).cells(.areas(.areas.count).rows.count).row         end         ' msgbox "the first row " & firstrow         ' msgbox "last row " & lastrow     end if end  end sub

instead of:

thiscell = sheets("names").select: range("b" & i).select

do:

'## use set keyword assign object variable set thiscell = sheets("names").range("b" & i)

also, instead of relying on activecell in function body, change to:

strtest = worksheetfunction.proper(thiscell.value)

then call function:

call arraymanip(thiscell)

if function doesn't return value caller, should sub instead. change sub , above call statement should still work.

see also:

how make excel vba variables available multiple macros?


Comments

Post a Comment

Popular posts from this blog

inversion of control - Autofac named registration constructor injection -

does autofac support specifying registration name in components' constructors? example: ninject's namedattribute . you need use autofac.extras.attributed package on top achieve this so let's have 1 interface , 2 classes: public interface ihello { string sayhello(); } public class englishhello : ihello { public string sayhello() { return "hello"; } } public class frenchhello : ihello { public string sayhello() { return "bonjour"; } } then have consumer class, in want select instance injected: public class helloconsumer { private readonly ihello helloservice; public helloconsumer([withkey("en")] ihello helloservice) { if (helloservice == null) { throw new argumentnullexception("helloservice"); } this.helloservice = helloservice; } public string sayhello() { return this.helloservice.sayhello()...
Read more

verilog - Systemverilog dynamic casting issues -

i've code snippet following in testbench function void write_to_port( my_data_type_base data ); my_data_type_extended data_ext; if(!$cast(data_ext, data)); `uvm_error(get_type_name(), "failed cast"); `uvm_info(get_name(), $psprintf("data_ext :\n%s", data_ext.sprint()), uvm_medium) // write data_ext out port.... endfunction when run it, i'm getting uvm_error "failed cast." i'm not quire sure why $cast not returning 1. can see, i'm printing out extended class data item after casting uvm_info. can see it's being cast properly. if don't use $cast if condition, don't runtime error. isn't coding practice use if dynamic cast check if $cast returning 1 ? what might reason behind cast not returning 1 in above case? i think semicolon on line 'if' not belong? i think consumes if statement, , uvm_error executes regardless of how if evaluates: if(!$cast(data_ext, data)); <- no semico...
Read more

ios - Change Storyboard View using Seague -

i have following code checks connection server, , depending on want change views if no connection found. nsstring *connect = [nsstring stringwithcontentsofurl:[nsurl urlwithstring:@"http://myserver.com"] encoding:nsutf8stringencoding error:nil]; if (connect == null) { [self performseguewithidentifier:@"network_failure" sender:self]; } else { // other code here } my problem not change views. have added storyboard segue between 2 views identifier of network_failure . not have navigation controller or that, problem? thanks help! no not problem. first of all, in objectivec exists nil object oriented null . must use that. so: if(connect == nil) or better: if(!connect) so due fact nil not same of null , probably code does't enter in if scope . you have been able discover breakpoint. anyway: be sure set trigger segue (line) 2 view controller , , name network_failure (that not best name. should instead view1toview2 ) as, m...
Read more