Macro for genearating warning message if a particular cell value is empty

[Nithya1987]

Hi,

I am trying to write a macro, which will check the value of D1 value in sheet1 worksheet(i have multiple sheets, and I will call this macro in all the sheets for getting the value from sheet1), if the value is NULL I am displaying a error message, if the feild has a value I am calling the main batch script.But when I am executing the below macro I am getting subscript out of range error. Please let me know what is wrong in the script.

Please Login or Register  to view this content.

Thanks,
Nithya

[Medpack]

I'm not sure exactly what you mean by null - Your code indicates that you mean there is no value in the cell, so you could use the following if this is what you want;

*fixed proper row, column reference

Please Login or Register  to view this content.

Please note you also do not indicate where your code haults (I'm assuming not during your other sub, BAISB?).

[TMS]

@Medpack: cells(4, 1) ... ?

[Medpack]

Whoops, backwards, 1,4 - thanks for checking =)

[Nithya1987]

HI ,

Thanks for the help.

I am able to generate a warning message with the below code.But when I entered space and executed it is not generating any warning messages, it is directly calling my sub program. I tried with the trim of the cell.value but still the macro is not generating any warning.Is it possible to generate warning for spaces, or to keep some additional check like, if the first character in the D1 should be always a alphabet B.. some thing from preventing the subprogram to fire if D1 is null or if it contains spaces.

Please Login or Register  to view this content.

Thanks,
Nithya

[Medpack]

Yes, you can check the first part and see if it's = to a space by either referring to " " or to chr$(32), which is the character code for space.

Please Login or Register  to view this content.

**Edited thanks to the kind macro guru's advice
If you want to check for alphabetical characters (both upper and lower case) you could use the following;

Please Login or Register  to view this content.

[TMS]

@Medpack: rather than loop through the alphabet (times 2), can't you just check >= 65 and <= 90?

[Medpack]

Wow, that's pretty cool - didn't realize you could refer to a character in a string and it would return a numerical value that can be compared against another. Thanks!

[TMS]

@Medpack: you're welcome. Thanks for the rep.

[Nithya1987]

Thanks Medpack.

Regards,
Nithya