VB code help, ignore blanks/specific

**ad9051** · 04-06-2011, 03:23 PM

Hello all firstly many thanks for taking the time to look at my problem, I have had some help in making this macro, which is a search records macro, to pull/import the records from one sheet to another based on a list of cells. It also tells me if a piece of record is not found. This is very useful! Because I can’t then add these records, however it also displays the same message on encountering both blanks and titles. This causes many false positives with regards to needing to enter new records. I have included an example spreadsheet with macro.

So i would like to be able to add exceptions (blanks/"specific text") to the already supplied macro.

Any help would be greatly appreciated.

Best regards

Alan

**ad9051** · 04-06-2011, 03:24 PM

i think i failed to attatch the relevent spreadsheet

**TMS** · 04-06-2011, 03:57 PM

Try:

Please Login or Register  to view this content.

Regards

**ad9051** · 04-07-2011, 11:40 AM

This looks like it should work however i am not sure where i should insert it within the macro. This is what i have tried so far?

Please Login or Register  to view this content.

**TMS** · 04-07-2011, 05:47 PM

It replaces:

Please Login or Register  to view this content.

in your existing code.

Regards

**ad9051** · 04-07-2011, 08:33 PM

Hmmm i did try that, i replaced and entered the name of the title and it didnt work, as shown above. I still get pop ups for both the titles and the blanks. :S

Any ideas guys?

All the best

Alan

**TMS** · 04-08-2011, 03:51 AM

I'm not sure why it wasn't/isn't working for you. It seemed fine to me, ignoring "name". It didn't seem to generate a message for a blank entry but I have put a specific test in for this now, just to be on the safe side.

The full code is now:

Please Login or Register  to view this content.

For some reason, the macro code wasn't linked to the button. You may need to right click on the button, select "Assign Macro..." and select "GetData from the list.

Regards

**ad9051** · 04-08-2011, 01:50 PM

Unfortunately it still doesn't work, it's ignoring the blanks but not the word "Title"

Please Login or Register  to view this content.

Any ideas/fix's for this would be much appreciated.
All the best

Alan

**TMS** · 04-08-2011, 04:16 PM

I don't recall there being a "Title" in the example, nor was it mentioned previously ... is this application being defined on the fly?

For *your* modification to work, change this:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

The LCase does what it says on the tin, converts to lower case ... so you need to compare to lower case text, in this case, "title"

Regards

**nimrod** · 04-08-2011, 04:38 PM

Suggestion for future: You can also make text comparisons case-insensitive by adding the line "Option Compare Text" at the very top of a module (i.e. outside of macro) . Then the macro's don't care if text is upper, lower or proper