Code to Search a table for a specific Cell and fill it

**JRidge** · 06-26-2014, 09:45 AM

Hi All,

I need a VBA code to work with my Userform:

The code needs to search a sheet "Tabelle2" for a specific Column in Row 1 and a specific Row in Column A and where they cros Fill the Cell with Specific Data.

So if i had dates in Column A and Names in Row 1 it would search for the date and name and where they cross would insert a text from the userform.

Any help would be appreciated.

Regards

Jeremy

**xladept** · 06-26-2014, 02:48 PM

Hi Jeremy ,

What are the specs?

**JRidge** · 06-27-2014, 03:06 AM

Hi xladept,

Basically i have a UserForm with text boxes that the user fills out, When he presses "Save" the code should search for the key words in Row1 and then go down the coloumns to the appropiate dates (Dates all in ColumnA, Where the dates and Keywords cross in the Table it will insert what ever is in the "Tatigkeits" Textbox, i.e "IB" for example.

There could be multiple Dates in the Userform because the user selects a start and end Date, So all dates between start and end date need to be filled aswell. There could be Multiple Keywords also in Row1. So all the cells that meet this criteria need to be filled.

Boxes in userform are:

Kunde (Combobox) - will always be a Keyword in Row1
VonDatum (Textbox) - Start date always in ColumnA
ZuDatum (Textbox) - End date always in ColumnA
Mitarbeiter1 (Combobox) - Will always be a keyword in Row1
Mitarbeiter2 (Combobox) - Will always be a keyword in Row1
Mitarbeiter3 (Combobox) - Will always be a Keyword in Row1
Tatikeit (Textbox) - This needs to be inserted in all cells that meet the criteria above.

Phew...

Does that make sense...

Regards

Jeremy

**xladept** · 06-27-2014, 03:42 PM

Hi Jeremy,

You'd best post a sample workbook as your chances on getting help will increase dramatically.

**JRidge** · 06-30-2014, 04:14 AM

So this works for search one of the 1 of the criteria:

Searching for Kunde i Row and singel Date in Column:

Please Login or Register  to view this content.

It put the name in "Tatigkeit" in the correct cell:

As soon as i say search what is in "VonDatum" it no longer puts anything in the cell, "VonDatum" is the textbox on the userform when the date is inputed above...

Please Login or Register  to view this content.

Cheers for any help

Jeremy

**JRidge** · 06-30-2014, 06:03 AM

Slowly getting there...

I can now use the text boxes to search and it fills the 2 relevent Cells.... From Date and To Date:

Please Login or Register  to view this content.

Is there away to join these together so it fills the start date box, the end date box and all dates in the middle... (Do i need to use looping??)

And what would it look like?

Any Help would be greatly appreciated.

regards

Jeremy

**xladept** · 06-30-2014, 03:13 PM

Probably:

Please Login or Register  to view this content.

**JRidge** · 07-01-2014, 09:55 AM

Hi xladept,

Thank you for your help, I have come up with this that works (Of cause with help from other members etc, ty Norie)

Please Login or Register  to view this content.

I need to now work out how to also change the background color of the affected cells at the same time?

Any ideas??

Regards

Jeremy

**xladept** · 07-01-2014, 02:55 PM

Maybe:

Please Login or Register  to view this content.

**JRidge** · 07-02-2014, 08:12 AM

Originally Posted by xladept

Maybe:

Please Login or Register  to view this content.

Is there anyway to use the background color of a textbox in a userform instead of selecting the color in the code.

Example,

TextBox is called TextBox1, When i click save it uses the color that is currently in the textbox as background color?

Thought something like

Please Login or Register  to view this content.

But that doesn't work,

Any Ideas

Regards

Jeremy

**xladept** · 07-02-2014, 01:10 PM

How is the textbox color assigned? Is it conditional formatting or rgb or what - because apparently it isn't colorindex?

**JRidge** · 07-03-2014, 05:17 AM

Hi xladept,

Currently it is :

Please Login or Register  to view this content.

And looks like:

Text Box.PNG

You click the color you want and it fill the text box

Dont know if this is the best way of doing it.

Regards

Jeremy

**xladept** · 07-03-2014, 02:45 PM

Maybe:

Please Login or Register  to view this content.

**JRidge** · 07-04-2014, 01:27 AM

Thanks xladept,

Does just what i need.

Will check it prooerly when i am in the office in a bit, and let you know.

Regards

Jeremy

**JRidge** · 07-04-2014, 06:37 AM

Hi xladept,

Thanks for your help on this works perfectly,

I have opened a new thread in this forum, could you have a look please

HTML Code:

Regards

Jeremy

**xladept** · 07-04-2014, 01:45 PM

You're welcome and thanks for the rep!

*I'll take a look.

Code to Search a table for a specific Cell and fill it

LinkBack

Thread Tools

Rate This Thread

Display

Code to Search a table for a specific Cell and fill it

Re: Code to Search a table for a specific Cell and fill it

Re: Code to Search a table for a specific Cell and fill it

Re: Code to Search a table for a specific Cell and fill it

Re: Code to Search a table for a specific Cell and fill it

Re: Code to Search a table for a specific Cell and fill it

Re: Code to Search a table for a specific Cell and fill it

Re: Code to Search a table for a specific Cell and fill it

Re: Code to Search a table for a specific Cell and fill it

Re: Code to Search a table for a specific Cell and fill it

Re: Code to Search a table for a specific Cell and fill it

Re: Code to Search a table for a specific Cell and fill it

Re: Code to Search a table for a specific Cell and fill it

Re: Code to Search a table for a specific Cell and fill it

Re: Code to Search a table for a specific Cell and fill it

Re: Code to Search a table for a specific Cell and fill it

Thread Information

Users Browsing this Thread

Similar Threads

Help with VBA code to find bottom row in pivot table and fill table cell borders

Search for specific characters in specific positions in a cell then return a value

VBA code to search a list for specific value

Formula or Macro to fill the other table by search, copy and paste

[SOLVED] Search for a number in a table and return data of a specific cell

Bookmarks

Bookmarks

Posting Permissions