PDA

View Full Version : vba find text paste text in message box next cell



martinrusten
03-14-2019, 03:54 PM
hello, my skills is not enough to finnich my project.
i got a work task sheet, and i need the finniched work to register on the correct cell in the correct month sheet

i need a vba to look for the same date as in cell J6 - J46 (the selected row/cell in that range)
look for the same text as in L6 - L 46 (the selected row in that range)
vba need to find the text and date in the sheets: Januar Februar Mars April Mai Juni Juli August September Oktober November
dates is lokated in range D3 - DU3
text is located in range B6 - DU17

when the cell with the same text is found i need the vba to input user spessifyed text in message box in the collomn to the right for the text that was found.

example:
J6 contains date 13/3/19 (dd-mm-yy)
L6 contains text hello
vba find this date in sheet Mars AX3
vba find this text in sheet Mars AX6

(this is to end work task and user need to input what is done)
vba ask user for what is done (in text box)
vba then paste the users text in the cell to the right, AY6
vba ask user what date work was done and paste this date in 2'nd cell to the right AZ6 (dd-mm-yy)
vba ask user for name and paste the name in the 3'nd cell to the right BA6
all this would be nice to have in the same text box


when name is entered in BA6 the task is not longer present in the Dagplan sheet (its done by formula no vba needed)
i can not change anything bechause its all connected to other workbooks and the tasks come from other workwooks, but work done is registred in this workbook.

hopefully someone could help with this

thank you

martinrusten
03-14-2019, 03:55 PM
file is to big to be uploaded 1,3mb (stupid low limit?)

martinrusten
03-14-2019, 04:39 PM
2390423905

here is a few pictures, read description and its clear what i need help for

martinrusten
03-14-2019, 05:04 PM
here is the file

martinrusten
03-14-2019, 10:31 PM
turns out that its not so easy to understand only by text so i had to translate and simplyfy the sheet. here it is

大灰狼1976
03-14-2019, 11:22 PM
Hi martinrusten!
Please refer to the attachment.

大灰狼1976
03-14-2019, 11:25 PM
change
TextBox2 = Format(Dat, "d-m-yy")
into
TextBox2 = Format(.Cells(mch.Row, rng.Column + 2), "d/m/yy")

Fluff
03-15-2019, 07:05 AM
Cross posted on multiple sites
Please see http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3

martinrusten
03-15-2019, 03:50 PM
Cross posted on multiple sites


yes i put this up to several forums bechause i would like to solve it quickly, and it do not seems like its a lot of peoples out there that can solve this :(
so i was upping the ods.




here is the forum links:
https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1215996-vba-find-text-paste-text-in-message-box-next-cell
https://chandoo.org/forum/threads/vba-find-text-paste-text-in-message-box-next-cell.41201/#post-245746
https://www.mrexcel.com/forum/excel-questions/1090871-vba-find-text-paste-text-message-box-next-cell.html


if this is solved i wil post the link to the forum that solved it and also mark post as solved

martinrusten
03-15-2019, 03:51 PM
its also listed here: https://www.excelforum.com/excel-programming-vba-macros/1268485-vba-find-text-paste-text-in-message-box-next-cell.html?

not allowed to post more than 3 links in one post

martinrusten
03-15-2019, 04:19 PM
change
TextBox2 = Format(Dat, "d-m-yy")
into
TextBox2 = Format(.Cells(mch.Row, rng.Column + 2), "d/m/yy")

hello

thank you, did you look at the sheet Mars? i put another description there BF15

im sorry but i do not have enough knowlege to make something like this, i can frankenstein a macro together somehow, but this is totally new ground to me.

the button Cancel do not do anything (should close the window)

the button Ok do something but i dont know what, didnt find the text anywhere
the Ok button should compare the date in the "Date registred" collumn for the clicked row with all dates in the mounth sheets.

when date is found vba need to find the same machine name as the one in clicked row under the newly found date. look at the Mars sheet and date 15/3-19 and you wil see what i mean

then the user specifyed text neet to be written in the "what is done" collumn
same goes for the day of repair (should be date of repair, my bad)
and the same goes for "Sign" witch is user's name initials

martinrusten
03-16-2019, 03:42 PM
i mannaged to make a few changes, it work where is should and vba ends when it need to, and added todays date on textbox2 (user can change it if nessesarly hopefully it works)
also added error message for the end user to see.


and the vba can not find machine name and the date.

i was playing around with this and was googling all sort of functions but i can not make it work, so have to ask you proffessionales again

here is the edeted file

23914

martinrusten
03-16-2019, 09:13 PM
23915 this is what the vba should do, but it only turn itself off :(

martinrusten
03-17-2019, 03:58 PM
SOLVED



big thanks to Christofer Weber that made this work
at https://superuser.com/questions/1414850/vba-find-date-and-enter-userform-text-box/1414900#1414900


and big thanks to 大灰狼1976 that made all the work
at this forum, my hat off for you sir!!




if someone need to download the complete file, its attached here


thank you all

大灰狼1976
03-17-2019, 05:58 PM
You're welcome. :friends: