I want to run a FOR inside an IF statment to concatenate using & in vba excel -


this first time using vba. generating data in grid of cells number of rows , fixed 4 columns. please note rows not fixed, example data may in grid b17 e20 (4x4 grid) or change b17 e25 (4x9 grid).

screenshot

now, not entries in grid contain data, remain blank.

so problem want list of entries in grid in 1 cell on excel separated ";"

i think need use logic statements omit blank cells, not how use them strings , because number of rows change, need put of inside loop. yet , try generate content 1 excel using & operator, content gets overwritten.

please see code below full macro , attached screenshot of data need enter in grey cells make work.

my post referring "images in website gallery" section...

sub combo() ' ' combo colours sizes '      maxcolours = range("f3")     maxsizes = range("h3")     rowoffset = 3     col1 = 1     col2 = 7     col3 = 9     col4 = 10     col5 = 12     col6 = 13     currow = 4  '   generate simple images colours , sizes      = 1 maxcolours         j = 1 maxsizes              cells(currow, col3).value = cells(rowoffset + i, col1).value             cells(currow, col4).value = cells(rowoffset + j, col2).value             cells(currow, col5) = range("f1") & "-" & cells(currow, col3).value & "-" & cells(currow, col4).value              uppercolour = cells(rowoffset + i, col1)             lowercolour = lcase(uppercolour)                  k = 0 3                     cells(currow, col6 + k) = "/" & lowercolour & "c.jpg"                 next k              currow = currow + 1         next j     next      cells(currow, col5).value = range("f1")     cells(currow + 1, col5).value = range("f1") & "-m"      currowtwo = 4  '   generate available images      n = 1 maxcolours         o = 1 4              colour = cells(rowoffset + n, col1)             image = cells(rowoffset + n, col1 + o)             if image <> ""             cells(currowtwo + n + 12, col1 + o).value = "/" & lcase(colour) & lcase(image) & ".jpg"             else             cells(currowtwo + n + 12, col1 + o).value = ""             end if          next o     next n  '   website config '   images in first 3 columns      l = 1 3         cells(currow, col5 + l) = "/" & range("h17")     next l  '   images in website media gallery      currowtwo = 4              cells(currow, col5 + 4).value = cells(currowtwo + maxcolours + 9, col1 + 1) & "; " & cells(currowtwo + maxcolours + 9, col1 + 2) & "; " & cells(currowtwo + maxcolours + 9, col1 + 3) & "; " & cells(currowtwo + maxcolours + 9, col1 + 4) & "; " & cells(currowtwo + maxcolours + 10, col1 + 1) & "; " & cells(currowtwo + maxcolours + 10, col1 + 2) & "; " & cells(currowtwo + maxcolours + 10, col1 + 3) & "; " & cells(currowtwo + maxcolours + 10, col1 + 4) & "; " & cells(currowtwo + maxcolours + 11, col1 + 4) & "; " & cells(currowtwo + maxcolours + 11, col1 + 4) & "; " & cells(currowtwo + maxcolours + 11, col1 + 4) & "; " & cells(currowtwo + maxcolours + 11, col1 + 4)   '   marketplace config '   images in first 3 columns      m = 1 3         cells(currow + 1, col5 + m) = "/" & range("h19")     next m  end sub

i'm not quite sure if trying achieve but, understood, here small snippet should trick.

'define grid area firstrowofgrid = 17 lastrowofgrid = 29 numberofcols = 4 firstcolletter = "b"  cellvalue = ""                  'this 1 hold cell value concatenatedstring = ""         'this concatenated string  firstcolletter = asc(firstcolletter)    'convert letter number  j = firstcolletter firstcolletter + numberofcols     'loop through columns     = firstrowofgrid lastrowofgrid                 'loop through rows         cellvalue = range(chr(j) & i).value                 'get current cell value         if (cellvalue)                                 'check if current cell has value             'the new value of concatenated string concatenated string followed semi-colon , current             'cell value             concatenatedstring = concatenatedstring & ";" & cellvalue         end if     next next j  'remove first semi-colon concatenatedstring = mid(concatenatedstring, 2, len(concatenatedstring)) 'do string (currently displaying in msgbox) msgbox concatenatedstring

this code loop through fields b17 e29 , add cell value (if cell not empty) concatenated string can use later.


Comments

Post a Comment

Popular posts from this blog

jquery - How can I dynamically add a browser tab? -

since using iframes house external pages are, accounts "not preferred method" , disallowed sites (flickr, one), want dynamically generate browser tabs. e.g., when user submits form, want dynamically generate tab on browser in response submitted. e.g., might want add new tab url like: http://www.bigsurgarrapata.com/contact how in jquery? you want form target attribute action attribute. http://www.w3schools.com/tags/att_form_target.asp browsers have ultimate control on result of target attribute open new tab value of _blank. <form action="contact_submit.php" target="_blank" method="post">
Read more

keyboard - C++ GetAsyncKeyState alternative -

i using windows.h library's getasynckeystate function define key pushed on keyboard . here program returns int value of pushed key example. #include <iostream> #include <windows.h> using namespace std; int main (){ char i; for(i=8;i<190;i++){ if(getasynckeystate(i)== -32767){ cout<<int (i)<<endl; } } return 0; } but trying make opengl simple game , function appeared slow . there function not need cycle 180 times , if statement . thanks you're trying make opengl game, you're going use glfw. if that's case can away using glfwgetkey() such: if (glfwgetkey(glfw_key_up) == glfw_press) {...} if (glfwgetkey(glfw_key_down) == glfw_press) {...} that method nicely explained in tutorial 6 opengl-tutorial.org . should start first tutorial of beginners tutorials , later intermediate tutorials . if you're going make win32 window can listen wm_keydown or wm_keyup messages , virtua...
Read more

android - java.net.UnknownHostException(Unable to resolve host “URL”: No address associated with hostname) -

i developing app testing asp.net webservice in android.for simple webservice adding 2 numbers used.where numbers passed parameters.the result in dialogue.the webserver works in local. when application runs following result exception.i provided internet permission in manifest. java.net.unknownhostexception(unable resolve host “ http://url.com/ ”: no address associated hostname) my code given below main activity public static string rslt=""; /** called when activity first created. */ @override public void oncreate(bundle savedinstancestate) { super.oncreate(savedinstancestate); setcontentview(r.layout.activity_main); button b1=(button)findviewbyid(r.id.button1); final alertdialog ad=new alertdialog.builder(this).create(); b1.setonclicklistener(new onclicklistener() { @override public void onclick(view arg0) { // todo auto-generated method stub try { ...
Read more