codign several small things in VBA

[Armitage2k]

Hello!

I am sorry for this rather unclear title, but I would have some rather small things which I usually do via functions in excel, but now need to accomplish in VBA. Hope someone could advise me solutions for the following issues:

1. How do I strip text from a cell content in VBA? eg. the cell content is 15-Jun-11, I need to strip everything left and right from the "-" in order to get Jun as the value.

2. how can i list all sheets in my current workbook (except the sheet called "summary") in an inputbox? Logic is, I have sheets ranging from Jan to Dec and want the user to pick from a dropdown list. Do i need a custom userform for this?

3. how can I check if a certain cell has a #value error and then connect it with an If statemment? eg. if A2 = #value error then ... else ...

4. how can i address all sheets in my workbook with exceptions? Eg. select all worksheets in active workbook except 1, 2, 3, etc.

Thanks a lot!
A2k

[p24leclerc]

1. How do I strip text from a cell content in VBA? eg. the cell content is 15-Jun-11, I need to strip everything left and right from the "-" in order to get Jun as the value.

Are you sure it is a text data and not a date format?
if it is a text then you can extract it with this formula (I supposed you always have 3 letters for the month":

=MID(J29;FIND("-";J29;1)+1;3)

[mikerickson]

1.How do I strip text from a cell content in VBA? eg. the cell content is 15-Jun-11, I need to strip everything left and right from the "-" in order to get Jun as the value.

If the cell holds an excel serial date and not just text, you could use

Please Login or Register  to view this content.

If it is a string

Please Login or Register  to view this content.

4. how can i address all sheets in my workbook with exceptions? Eg. select all worksheets in active workbook except 1, 2, 3, etc.

Please Login or Register  to view this content.

returns a Worksheets object that contains the three sheets.