i'm not real @ macros/vb - i've had experience amounts struggling through writing line while searching on google examples.
i'm trying write macro function assign 12 items array , pass array parent sub (the main) (so can put each array item cell in different worksheet). values found looking "markers" in first column (will "m1" through "m6" , adjacent down rows, not have 6 markers - there less). i'm trying grab 2 values in second , third columns next each marker , plug each value item in array.
what i'm down @ point finding "m1", resizing range around include 6 rows total. plan search within 6 rows see whether left character begins "m" , if grab each of 2 adjacent columns' values , plug them 2 array items - , if not assign "n/a" remaining array items.
not sure if that's best way, or if describes well, that's i'm stuck - don't know how in cell see if left character "m" , copy 2 adjacent columns' value array. here's code far:
function getmarkerarray() string() 'grabs marker values present ' csv or sets "n/a" if not , passes along main sub use. dim markerarray(11) string 'this hold marker values dim c integer 'create quick counter variable c = 1 'initiate counter dim integer 'create quick array counter variable = 0 'zero out array counter dim markercell range 'this holds location of "m1" marker found dim markrow integer 'this holds row of each marker dim cell range 'quick range value lookup activesheet.range("a:a") 'doing things inside active sheet of csv file '==================================================================================== 'find location of "m1" (the first marker) exapand range, , store it. range used later 'to find marker values instances , assign each value next entry in array or 'assign "n/a" remaining array items w/o markers. set markercell = range("a:a").find(what:="m1", lookin:=xlvalues, _ lookat:=xlpart, searchorder:=xlbyrows, searchdirection:=xlnext, _ matchcase:=false, searchformat:=false).resize(6) '==================== 'temp - testing msgbox markercell.address '==================================================================================== markrow = markercell.row 'set row "m1" found in end c = 1 markercell.rows.count 'code: if left letter = "m" 'markerarray(a) = cells(markercell,2).value 'a = + 1 'markerarray(a) = cells(markercell,3).value 'a = + 1 'code: else loop through remaining array items , set "n/a" next c getmarkerarray = markerarray end function any ideas big or small appreciated.
before going details of code , explanation, function in vb/vba called left(string, len) looking for. eg:
if left(cells(c,1), 1) = "m" i further more details, if not enough.
update 1:
if understand asked correctly below code works marker data.
and here few changes made code,
sub macro1() ' ' macro1 macro ' ' keyboard shortcut: ctrl+shift+l ' dim marker() string dim res string marker = getmarkerarray = lbound(marker) ubound(marker) res = res & marker(i) & ", " if mod 2 = 1 res = res & vbcrlf next msgbox "array values: " & vbcrlf & vbcrlf & res end sub function getmarkerarray() string() 'grabs marker values present ' csv or sets "n/a" if not , passes along main sub use. dim markerarray(11) string 'this hold marker values dim c integer 'create quick counter variable c = 1 'initiate counter dim integer 'create quick array counter variable = 0 'zero out array counter dim markercell range 'this holds location of "m1" marker found dim markrow integer 'this holds row of each marker dim cell range 'quick range value lookup dim markerfinished boolean activesheet.range("a:a") 'doing things inside active sheet of csv file '==================================================================================== 'find location of "m1" (the first marker) exapand range, , store it. range used later 'to find marker values instances , assign each value next entry in array or 'assign "n/a" remaining array items w/o markers. set markercell = range("a:a").find(what:="m1", lookin:=xlvalues, _ lookat:=xlpart, searchorder:=xlbyrows, searchdirection:=xlnext, _ matchcase:=false, searchformat:=false).resize(6) '==================== 'temp - testing msgbox markercell.address '==================================================================================== markrow = markercell.row 'set row "m1" found in end markerfinished = false c = 1 markercell.rows.count 'code: if left letter = "m" if left(ucase(trim(markercell(c, 1))), 1) = "m" , not markerfinished 'markerarray(a) = cells(markercell,2).value markerarray(a) = markercell(c, 2) = + 1 markerarray(a) = markercell(c, 3) = + 1 else markerfinished = true markerarray(a) = "n/a" = + 1 markerarray(a) = "n/a" = + 1 end if 'code: else loop through remaining array items , set "n/a" next c getmarkerarray = markerarray end function update 2:
answer question in comment is, find function fails range when m1 not found. but, statement goes further "no range", call resize(6) function throws error. below bit fine tuned code address that,
function getmarkerarray() string() 'grabs marker values present ' csv or sets "n/a" if not , passes along main sub use. dim markerarray(11) string 'this hold marker values dim c integer 'create quick counter variable c = 1 'initiate counter dim integer 'create quick array counter variable = 0 'zero out array counter dim markercell range 'this holds location of "m1" marker found dim markrow integer 'this holds row of each marker dim cell range 'quick range value lookup dim markerfinished boolean set markercell = activesheet.range("a:a").find(what:="m1", lookin:=xlvalues, _ lookat:=xlpart, searchorder:=xlbyrows, searchdirection:=xlnext, _ matchcase:=true, searchformat:=false) if markercell nothing msgbox "no markers found" c = 0 11 markerarray(c) = "n/a" next c else set markercell = markercell.resize(6) markerfinished = false c = 1 markercell.rows.count if left(ucase(trim(markercell(c, 1))), 1) = "m" , not markerfinished markerarray(a) = markercell(c, 2) = + 1 markerarray(a) = markercell(c, 3) = + 1 else markerfinished = true markerarray(a) = "n/a" = + 1 markerarray(a) = "n/a" = + 1 end if next c end if getmarkerarray = markerarray end function 
Comments
Post a Comment