Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 12th, 2005, 04:17 AM
Friend of Wrox
Points: 8,019, Level: 38
Points: 8,019, Level: 38 Points: 8,019, Level: 38 Points: 8,019, Level: 38
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2005
Location: Mauchline, East Ayrshire, Scotland
Posts: 1,525
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to crmpicco Send a message via AIM to crmpicco Send a message via MSN to crmpicco Send a message via Yahoo to crmpicco
Default check for lower case (LCase)?

I have this looping structure:

Code:
For iCol = 1 To iTotalCols

                bFoundCellCol = False

                sRange = getColumnLetter(iCol) & iRow
                Range(sRange).Select

                '... if the any cells are bold then remove the formatting from them
                Selection.Font.Bold = False

                iCurrentCellCol = Range(sRange).Interior.ColorIndex

                '... if the colour of the current cell is not WHITE
                If iCurrentCellCol <> gbFLUSH_COLOUR Then

                    For j = LBound(gaRulesColour) To UBound(gaRulesColour)

                        iPos = InStr(gaRulesColour(j), "/")
                        iArrayColour = Mid(gaRulesColour(j), iPos + 1, Len(gaRulesColour(j)) - 1)

                        If iCurrentCellCol = iArrayColour Then

                            If the_Heading <> "" Then
                                rulesXML = rulesXML & "<fare_rule_lines>"
                                rulesXML = rulesXML & "<heading>" & fix_characters(fix_ampersand(the_Heading)) & "</heading>"
                                rulesXML = rulesXML & "<content>" & fix_characters(Trim(the_Content)) & "</content>"
                                rulesXML = rulesXML & "</fare_rule_lines>"
                            End If

                            the_Heading = fix_ampersand(Trim(Range(sRange).Text))
                            the_Content = "&lt;table&gt;"

                        End If

                        iPos = InStr(gaContentColour(j), "/")
                        iArrayContentColour = Mid(gaContentColour(j), iPos + 1, Len(gaContentColour(j)) - 1)

                        '... take the start of the content and the totalrows and total columns
                        content_range = Left(gaContentColour(j), 2)
                        content_range = content_range & ":" & getColumnLetter(iTotalCols) & iTotalRows

                        '... delete all empty cells in the content range then
                        '... shift them all left
                        'Range(content_range).Select
                        'Selection.SpecialCells(xlCellTypeBlanks).Select
                        'Selection.Delete Shift:=xlToLeft

                        iCurrentCellColour = Range(sRange).Interior.ColorIndex

                        If iCurrentCellColour <> 2 Then

                            '... if the current cell is the same as the colour of the content in the template
                            If iCurrentCellColour = iArrayContentColour Then

                                iFlag = 0

                                While flag <> True

                                    sRightRange = getColumnLetter(iCol + iFlag + 1) & iRow

                                    '... if the cells are merged then split them up
                                    With Range(sRightRange)
                                        If .MergeCells Then
                                            .MergeArea.UnMerge
                                        End If
                                    End With

                                    'sRange = getColumnLetter(iCol + iFlag) & iRow

                                    '... if it is not empty then write another <td>
                                    If iFlag = 0 And Trim(Range(sRightRange).Text) <> "" Then
                                        the_Content = the_Content & "&lt;tr&gt;"
                                        the_Content = the_Content & "&lt;td&gt;"
                                        the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(getColumnLetter(iCol + iFlag) & iRow).Text))
                                        the_Content = the_Content & "&lt;/td&gt;"
                                    ElseIf iFlag = 0 And Trim(Range(sRightRange).Text) = "" Then
                                        the_Content = the_Content & "&lt;tr&gt;"
                                        the_Content = the_Content & "&lt;td&gt;"
                                        the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(getColumnLetter(iCol + iFlag) & iRow).Text))
                                        the_Content = the_Content & "&lt;/td&gt;"
                                        the_Content = the_Content & "&lt;/tr&gt;"
                                        flag = True
                                    ElseIf iFlag <> 0 And Trim(Range(sRightRange).Text) = "" Then
                                        the_Content = the_Content & "&lt;td&gt;"
                                        the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(getColumnLetter(iCol + iFlag) & iRow).Text))
                                        the_Content = the_Content & "&lt;/td&gt;"
                                        the_Content = the_Content & "&lt;/tr&gt;"
                                        flag = True
                                    ElseIf iFlag <> 0 And Trim(Range(sRightRange).Text) <> "" Then
                                        the_Content = the_Content & "&lt;td&gt;"
                                        the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(getColumnLetter(iCol + iFlag) & iRow).Text))
                                        the_Content = the_Content & "&lt;/td&gt;"

                                        '... if the cell is empty
                                    End If

                                    iFlag = iFlag + 1

                                    '... while flag <> true
                                Wend

                                '... if the cell is the same as the colour in the template
                            End If

                        End If

                    Next j

                End If

                '... loop through each column
            Next iCol
i am looking for code that when it finds a 'heading' in the specified range it checks one row DOWN and if it is LOWER CASE then it adds it to the heading it has found.

TIA.

Picco

Is this possible?

www.crmpicco.co.uk
www.crmpicco.co.uk.tt
www.milklemonadechocolate.uk.tt
www.griswolds.uk.tt
www.piccosmini.co.uk.tt
www.morton.uk.tt
__________________
_______________________
Ayrshire Minis - a Mini E-Community
http://www.ayrshireminis.com
http://www.crmpicco.co.uk
 
Old May 12th, 2005, 01:09 PM
Authorized User
 
Join Date: Feb 2004
Location: , , Denmark.
Posts: 44
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi
You are asking many Q.. But in your own profile you tell that you are a programmer. In what? I have always liked this forum because there have been a lot of good discussions on a level which suits a P2P forum, but many of your Q does not seems to be real. If you are a programmer why this Q.
eg. what is... in the Beginning applescript forum.
Please be less productive, and more serious in the future.

Regards
Karsten

 
Old May 12th, 2005, 03:42 PM
Authorized User
 
Join Date: Feb 2005
Location: , , Netherlands.
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hi crmpicco,

im confused by the amount of code. What exactly are you trying to solve? if you could clarify
the problem just a bit more, i might be able to help you..
---------------------------------
and slgknjn, before giving so much comment on someone else, you could check your grammar before
commenting other people ("many of your Q does not seems to be real"). The correct phrase would be
"Many of your questions do not seem to be "real" " (whatever you mean by that). Also, you could try not
using "chat language" like your "Q".




Similar Threads
Thread Thread Starter Forum Replies Last Post
lower case kgoldvas XSLT 3 February 28th, 2007 07:43 AM
how to make lower case in dataformatstring hardik Classic ASP Databases 1 February 14th, 2006 05:45 AM
search string either Upper case or lower case rylemer Beginning VB 6 3 March 24th, 2004 04:23 PM
Checking for upper,lower case, number and symbol dumbdumb SQL Server 2000 1 March 18th, 2004 03:51 AM
Check Case in a Case-Insensitive DB nbryson SQL Language 1 January 23rd, 2004 07:36 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.