Find values in cell and return adress

[cvelle89]

Hello,

I have a problem.

When I look for single word in cell, system gives me a wrong location! Cell is merged one, and its address is A14. But it gives me (I've put in msgBox) A$9$..

Other part of the code is writing some values next to that cell..but it writes it in B9, instead in B14..

Here is the code:

Please Login or Register  to view this content.

Maybe merged cell makes problem?

Many thanks!

[kadeo]

A merged cell will return the top-left most value as it's address. The formatting of your merged cell could be to display the contents at the bottom or center of the cell and so the contents appear to be in a different place, but the cell address is fixed to the top-left regardless.

[cvelle89]

I found some code that helped me to get row number.
I think it is a good solution for me. I want to insert below automatically split (txtLanguages, vbcrlf),and to add those separetd multilines in each row..Please see the photo.. I will post it later today whole code..it will be interesting

Please Login or Register  to view this content.

[kadeo]

Please upload files to the forum instead of providing links to external sites.

[cvelle89]

Here it is a whole file.

I am struggling with logic to locate cells such as Languages, Literacy etc, and below to insert in each row each line from txtLang, txtLiteracy and so on.. because, one candidate will have more lines, and the other will have less..

I do not succeeding in to write e.g. locate Languages -> ActiveCell.Offset(1,0).Value and below insert as many lines as there is in txtLanguage...

Other fields follow this logic

Any help would be awesome

Thanks!

[kadeo]

Taking Language as an example, each candidate has the list of languages they know entered into a single textbox and presumably each language goes on a new line. The problem here is that the contents of txtLang is a single string, no matter how many lines it covers. To have each language displayed on a separate row in the worksheet, you'd need to come up with a way of splitting the string. Assuming you are just using the enter key to go to the next line in the text box you could use vbCrLf as the delimiter and do something like:

Please Login or Register  to view this content.

[cvelle89]

And if I want to add "bullet" symbol in every new line before lines?

What shall I do with Work experience? As you can see Work experience, contains multiple fields for one insertion.

Do you by any chance know how to dynamically on click button add more text boxes, refering to work experince (candidte will have more than one or two or x experiences..)


P.S. I would give u reputation, but I cant.. as always, you are rocking :D

[kadeo]

And if I want to add "bullet" symbol in every new line before lines?...

To add a bullet you'd just change these two lines

Please Login or Register  to view this content.

To something like

Please Login or Register  to view this content.

...What shall I do with Work experience? As you can see Work experience, contains multiple fields for one insertion.

Do you by any chance know how to dynamically on click button add more text boxes, refering to work experince (candidte will have more than one or two or x experiences..)...

I'll give your workbook another look and get back to you about the rest.

[cvelle89]

Great kadeo!

But I need a little bigger bullet e.g. 26. I've declared Dim dot as String, also, dot = "·", now I am google for property such as .Font.Size..but this is property for a range..

Thanks again on your effort!

[kadeo]

...What shall I do with Work experience? As you can see Work experience, contains multiple fields for one insertion....

Ok, the source data in the userform for Work Experience is still just a single string. The output area for work experience has several rows and the rows consist of different sized merged cells. It seems like you intend certain parts of the work experience field to go to certain parts of the output area. Since I don't know what parts of the field you want in each cell it's difficult but as long as you follow simple rules for getting the data (like the name of the company is always the first line, the time served is always the second line, the responsibilities always start on the third line etc...) it would just be a case of placing each part where you want it before going into the loop that adds rows. When you do add rows, you just need to check that the next row is the 'Trainings' header and only then insert a new row if it is.

Do you by any chance know how to dynamically on click button add more text boxes, refering to work experince (candidte will have more than one or two or x experiences..)...

This one is a separate issue from the rest of the thread and I think you would benefit from raising this question in it's own thread in the forum. It's not just as simple as "add new field", you have to figure out how you're going to design your userform to 'grow' with each new Work Experience field to be added. That said, it might be of use to have Work Experiences on it's own page in the multipage so you can just add a new field below the existing fields instead of having to move the likes of Trainings, Languages, etc around the form. If there is more than one work experience field on the page then you just need to loop through them all adding them to the output sheet with the same code.

...But I need a little bigger bullet e.g. 26. I've declared Dim dot as String, also, dot = "·", now I am google for property such as .Font.Size..but this is property for a range..

You can use the size property to affect just part of the cell if you specify to which characters the property should apply by using the "Characters" property of the range. The problem here though is that even though the bullet is small, it is still font size 10 and takes up the same height as font size 10. When you increase it to font size 26 it will take up the height of font size 26 so your cell height will increase to fit, even though the rest of the cell is font size 10. I noticed you used "•" instead of "·" next to MS Office, Corel Draw, so I changed the code to include that one instead. You can still change the size of it if you wish, but maybe just making it bold will do.

Please Login or Register  to view this content.

[cvelle89]

Kadeo,

I am speechless! This is awesome code!

I have some trouble time with some form, I cant move it..I've searched on google, and they say to erase some files

but I cant still drag 'em.

Please could you see why now program wont to insert new lines for all except for education? I've just copied code and editied rng, names etc..


I will do start another topic for dynamic creaton of extra fields..that would be awesome!

Thanks again x1000 times!

Editied: Why did you put : .Resize(1, 19).Merge
19 is the last row on the template, but it wont be as soon system insert one or more rows..?

Maybe we should try usedLastRow?

[kadeo]

...Why did you put : .Resize(1, 19).Merge
19 is the last row on the template, but it wont be as soon system insert one or more rows..?

Maybe we should try usedLastRow?

I'll have a look at the rest of the issues you mentioned in this post in a little while when I have a bit more time but for now, when you refer to cells (as in Cells(x,y) or .Offset(x,y) or .Resize(x,y) etc) the row comes first then the column. So .Resize(1,19).merge is telling Excel to apply a merge to cells 1 row tall and 19 columns wide. In this case it's merging the cells from column A to column S on the row that's just been inserted. It's just coincidence that 19 is also the last row!

[kadeo]

...I have some trouble time with some form, I cant move it..I've searched on google, and they say to erase some files

but I cant still drag 'em...

I'm afraid I don't know why this is happening. The sample you uploaded seems to let me drag any of the controls on the form I tested. It all works as expected on my machine. Was there a specific control that wouldn't drag or was it all of them?

...Please could you see why now program wont to insert new lines for all except for education? I've just copied code and editied rng, names etc...

When I ran your code it threw up a few errors with the variables. For instance, you declared

Please Login or Register  to view this content.

but were using variables such as intLanguages, intTrainings, intComputerLiteracy, intXpert. The "Total" part of the variable name was missing. I corrected the variables in the code and ran again and it worked as expected, that is, all areas of the form updated and inserted new lines where it was supposed to.

Excel allows you to create new variables "on the fly" in the code without having to declare them first. So while you declared the variables at the top of the procedure and then used a different variable name in the code, instead of giving you an error because the variable hadn't been declared already, Excel just created a new variable. Since that variable name is different from the variable you were incrementing, the variable never reached the value the code was expecting.

A way to avoid this is to type "Option Explicit" at the top of each module. This forces Excel to only use declared variables. It has a number of uses: This kind of thing will be avoided and it stops you from duplicating variables by mistake. Excel will not compile the code if there are any errors like this and will warn you something's wrong.

I always use Option Explicit. I find it's one of the best features for debugging my code. You can tell VBA to automatically put Option Explicit at the top of every new module by going to Tools-->Options in the VB editor and selecting "Require Variable Declaration".

require-variable-declaration.png

This will only add Option Explicit to NEW modules. But you just need to type it yourself it at the top of any existing modules you want to use it on. Try it on this code before you make any changes to the variables I mentioned above. Make sure it is the very first line of code and run it again. It will give you errors at all the places where the variable names haven't been declared.

[cvelle89]

Kadeo,
Is not that you are an expert in development although you are excellent teacher! Thank you, I learnt today something new!

I've just created a topic about dynamic creation of forms, here is the link

I've pick a brand new excel workbook, just to be more clear for guys to better understand what I want to develop.

Logic for dynamic insertion of new set for workexperince would be through frames maybe. I just need to create template in one frame, and that frame with all its textboxes to copy below, and just to edit (Name) of each textbox.

Kadeo, thanks mate for everything.

P.S. I've just put Option Explicit and voila! OptionExplicit.png

[kadeo]

You make me blush! Lol. Thanks for the compliment. I've received so much help from this forum myself that I see imparting my own knowledge as a way of paying it forward. Glad I could help.

[cvelle89]

Kadeo,

Here is my "almost done" template form.
Kyle123 from the post that I ve linked above, helped me a lot for tab work experince. I like it his logic for listbox. I need just to read values from array and to import in particular cell.

Thanks guys!

[cvelle89]

The newest one!

I just cant put it company from the Listbox1 because of its merged cells.

And I have tough time to insert a photo from ImageBox.. it gives me some numerical value?

Thanks!