excel - Find a string within a cell using VBA -


i have been driving myself mad day, searched high , low, , trying cute totally stuck.

i trying run simple if then

if cell contains "%" i'd 1 thing, , if not another. reasons don't understand can't work out. i've taken couple ideas elsewhere still can't work.

complicating factors- don't want run on whole column, table, embedded in larger sub using lots or relative activecells. never know in column going run "% change" range has variable. want vba/vbe different when comes upon cell "%" in it.

here raw data looks 

initial value (6/30/06)  value (12/31/06)  net additions (9/30/07)  withdrawal (12/07)  value (12/31/07)  withdrawal (2008)  value (12/31/08)  addition (8/26/09)  value (12/31/09)  value (12/31/10)  value (12/30/11)  value (3/31/12)  % change 1st quarter  % change since inception

but when run following gets stuck in bad loop should have pulled out "if then" opposed "else" part of sub.

sub iftest()  'this should split information in table cells  dim splitter() string  dim lenvalue integer     'gives number of characters in date string  dim leftvalue integer    'one less lenvalue drop ")"  dim rng range, cell range  set rng = activecell  while activecell.value <> empty     if instr(rng, "%") = true         activecell.offset(0, 0).select         splitter = split(activecell.value, "% change")         activecell.offset(0, 10).select         activecell.value = splitter(1)         activecell.offset(0, -1).select         activecell.value = "% change"         activecell.offset(1, -9).select     else         activecell.offset(0, 0).select         splitter = split(activecell.value, "(")         activecell.offset(0, 9).select         activecell.value = splitter(0)         activecell.offset(0, 1).select         lenvalue = len(splitter(1))         leftvalue = lenvalue - 1         activecell.value = left(splitter(1), leftvalue)         activecell.offset(1, -10).select     end if loop end sub

all appreciated, thank you!

i simplified code isolate test "%" being in cell. once work, can add in rest of code.

try this:

option explicit   sub doihavepercentsymbol()    dim rng range     set rng = activecell     while rng.value <> empty         if instr(rng.value, "%") = 0             msgbox "i know nothing percentages!"             set rng = rng.offset(1)             rng.select         else             msgbox "i contain % symbol!"             set rng = rng.offset(1)             rng.select         end if    loop  end sub

instr return number of times search text appears in string. changed if test check no matches first.

the message boxes , .selects there see happening while stepping through code. take them out once working.


Comments

Post a Comment

Popular posts from this blog

jquery - Invalid Assignment Left-Hand Side -

i trying assign class tag via jquery, resulting in error through firebug says "invalid assignment left-hand side" on fourth line below. <cfoutput> $('.pngfix a').each(function(index) { var hreflink = $(this).attr("href"); if (hreflink.tolowercase() = '../audio.cfm') { $(this).addclass('audioimg'); } }); </cfoutput> looking around online, looks syntax problem, don't seem see it... any tips? thanks if (hreflink.tolowercase() = '../audio.cfm') that attempts assign value, rather check equality. change = operator == .
Read more

java - Play! framework 2.0: How to display multiple image? -

i need display gallery of photos. here template: @(photos: list[photo]) @title = { <bold>gallery</bold> } @main(title,"photo"){ <ul class="thumbnails"> @for(photo <- photos) { <li class="span3"> <a href="#" class="thumbnail"> <img src="@photo.path" alt=""> </a> </li> } </ul> } and here controller method: public static result getphotos() { return ok(views.html.photo.gallery.render(photo.get())); } and here photo bean : @entity public class photo extends model { @id public long id; @required public string label; public string path; public photo(string path, string label) { this.path = path; this.label = label; } private static finder<long, photo> find = new finder<long, photo>( long.class, photo.class); public static list<phot...
Read more

gmail - Is there any documentation for read-only access to the Google Contacts API? -

i want read-only access user's google contacts. there stackoverflow member wanted this, , received following answer : yes, there is, use https://www.googleapis.com/auth/contacts.readonly scope , "view contacts". this exchange reference read-only access google contacts can find using google. simply using access token obtained attempt read usual google contacts api (i.e. https://www.google.com/m8/feeds ) fails insufficient permissions. similarly, using google contacts api on new target fails 404 errors (for example https://www.googleapis.com/auth/contacts.readonly/default/full ). is there documentation anywhere on how use api, or permissible way access google contacts api full read-write access?
Read more