excel - Count lines of text in a cell -

-

i have excel spreadsheet of data work need split in vba. couple of columns have multiple lines of text , others not. i've figured out how split multiple lines of text, problem taking column single line of text , copying down. example:

company_name     drug_1      phase_2        usa                  drug_2      discontinued                   drug_3      phase_1        europe                  drug_4      discontinued

below code using split columns b & c , can handle d manually, need column copy down rows 2-4. there's on 600 rows otherwise manually. (note: i'm putting column b below, , column c c)

sub splitter()     dim iptr1 integer     dim iptr2 integer     dim ibreak integer     dim myvar integer     dim strtemp string     dim irow integer  'column loop     irow = 0     iptr1 = 1 cells(rows.count, 1).end(xlup).row         strtemp = cells(iptr1, 1)         ibreak = instr(strtemp, vblf)         range("c1").value = ibreak             until ibreak = 0             if len(trim(left(strtemp, ibreak - 1))) > 0                 irow = irow + 1                 cells(irow, 2) = left(strtemp, ibreak - 1)             end if             strtemp = mid(strtemp, ibreak + 1)             ibreak = instr(strtemp, vblf)         loop         if len(trim(strtemp)) > 0             irow = irow + 1             cells(irow, 2) = strtemp         end if     next iptr1  'column c loop     irow = 0     iptr2 = 1 cells(rows.count, 3).end(xlup).row         strtemp = cells(iptr2, 3)         ibreak = instr(strtemp, vblf)         until ibreak = 0             if len(trim(left(strtemp, ibreak - 1))) > 0                 irow = irow + 1                 cells(irow, 4) = left(strtemp, ibreak - 1)             end if             strtemp = mid(strtemp, ibreak + 1)             ibreak = instr(strtemp, vblf)         loop         if len(trim(strtemp)) > 0             irow = irow + 1             cells(irow, 4) = strtemp         end if     next iptr2  end sub

there bit of code call "waterfall fill" this. if can build range of cells fill (i.e. set rng_in), it. works on number of columns nice feature. can feed range of a:d , polish off blanks.

sub fillvaluedown()      dim rng_in range     set rng_in = range("b:b")      on error resume next          dim rng_cell range         each rng_cell in rng_in.specialcells(xlcelltypeblanks)             rng_cell = rng_cell.end(xlup)         next rng_cell      on error goto 0  end sub

before , after, shows code filling down.

enter image description here enter image description here

how works

this code works getting range of blank cells. default specialcells looks usedrange because of quirk xlcelltypeblanks. there sets value of blank cell equal closest cell on top of using end(xlup). error handling in place because xlcelltypeblanks return error if nothing found. if whole column blank row @ top though (like picture), error never triggered.


Comments

Post a Comment

Popular posts from this blog

Java 3D LWJGL collision -

-
i making 3d java game lwjgl library, , wondering how add collision detection, player not go through models. i using obj models. here objloader class, loads models: package renderengine; import java.io.bufferedreader; import java.io.file; import java.io.filenotfoundexception; import java.io.filereader; import java.util.arraylist; import java.util.list; import models.rawmodel; import org.lwjgl.util.vector.vector2f; import org.lwjgl.util.vector.vector3f; public class objloader { public static rawmodel loadobjmodel(string filename, loader loader){ filereader fr = null; try { fr = new filereader(new file("res/"+filename+".obj")); } catch (filenotfoundexception e) { system.err.println("couldn't load file!"); e.printstacktrace(); } bufferedreader reader = new bufferedreader(fr); string line; list<vector3f> vertices = new arraylist<vector3f...
Read more

spring - SubProtocolWebSocketHandler - No handlers -

-
i have ugly error during deploying spring app on jboss. 18:11:16,025 error [org.apache.catalina.core.containerbase.[jboss.web].[default-host].[/consumer]] (msc service thread 1-7) exception sending context initialized event listener instance of class org.springframework.web.context.contextloaderlistener: org.springframework.context.applicationcontextexception: failed start bean 'subprotocolwebsockethandler'; nested exception java.lang.illegalargumentexception: no handlers @ org.springframework.context.support.defaultlifecycleprocessor.dostart(defaultlifecycleprocessor.java:176) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.springframework.context.support.defaultlifecycleprocessor.access$200(defaultlifecycleprocessor.java:51) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.springframework.context.support.defaultlifecycleprocessor$lifecyclegroup.start(defaultlifecycleprocessor.java:346) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.s...
Read more

methods - python can't use function in submodule -

-
my folder structure this: pythonstuff/ program.py modulea/ __init__.py foo.py bar.py here's code bar.py : def hello(): print("hello, world!") here's code program.py : #!/usr/bin/env python3 modulea import bar bar.hello() i run $ python3 program.py somehow error: file "program.py", line 3, in <module> bar.hello() attributeerror: 'module' object has no attribute 'hello' edit: __init__.py file empty. edit2: after trying realized had bar.py in root directory contained hello() method. bar.py in modulea/ directory empty. add it import os __all__ = [] module in os.listdir(os.path.dirname(__file__)): if module != '__init__.py' , module[-3:] == '.py': __all__.append(module[:-3]) the init .py files required make python treat directories containing packages; done prevent directories common name, such string, unintentionally hiding v...
Read more