Free Microsoft Excel 2013 Quick Reference

How do i ignore a character in cell Results

I'm comparing numbers between two cells and I used the conditional
format to do this. But now I need to remove a character from the first
cell in order to get the right results. The first
cell would have something like this Q000003 which would be the quantity
and the second cell would be 2. And my condition is if first cell is
greater than second cell than the first cell would change colors. But I
need to ignore the Q in order to get the right results. How do I fix
this?

For example: 156 SQFT x $10.00. Where 156 SQFT is all in a single cell.
Is there a way to ignore the alpha characters and only look at the numerical
characters in the formula? (Excell 2000)

Thanks,

Hi,
I have a list of over 2000 product descriptions, and due to the structure of the source, each one is pre filled with a random number of spaces. I want to just pull out the text from each cell and ignore the spaces.

eg data

------------rum
--------Whisky
-----Gin
-------------Wine
--------Milk

- = space

I want to see

rum
Whisky
Gin
Wine
Milk

How do i do this? Find, Search, Match all seem to not help.

Thnaks

::Hi,
I have a spreadsheet with circa 11,000 rows, approx. 6 columns.
Some of the data MAY contain values that begin with a blank space,
e.g. the cell may look like (ignore quotes)

" Fred"

when it should be

"Fred"

Is there an easy way to change the cell colour to Red for all cells in
a spreadsheet where the first character is blank ?

Thanks in advance for your kind help.

Matt::

--
bvinternet
------------------------------------------------------------------------
bvinternet's Profile: http://www.excelforum.com/member.php...o&userid=25525
View this thread: http://www.excelforum.com/showthread...hreadid=389620

I have a table of data in which every cell has the suffix "mm" in it. How can
I create a chart with this data and get excel to ignore the letters. I
thought it would do it automatically but it doesnt seem to work.

Any ideas gratefully recieved!!!

Thanks in advance

Steve

it doesn't appear to be a simple text to columns function. here's what i want
to do:

i have cell that contains :02110610 and I want to reduce it to 010. is there
a formula, or the like, that will strip out the the first four characters
(:021) and the last three characters (610) and then add a zero, so i end up
with 010? can i do this in a column on one row and then drag it down +/- 2300
rows of data? thanks a multitude, in advance, for your help, folks.
-chuck

p.s. pls forgive my ignorance of the proper language for this stuff.

Hi,
I have a spreadsheet with circa 11,000 rows, approx. 6 columns.
Some of the data MAY contain values that begin with a blank space,
e.g. the cell may look like (ignore quotes)

" Fred"

when it should be

"Fred"

Is there an easy way to change the cell colour to Red for all cells in a spreadsheet where the first character is blank ?

Thanks in advance for your kind help.

Matt

The text file that i am trying to import looks like this:

Field 1 <tab> Field 2 <tab> Field 3.1(CR)Field 3.2(CR)Field 3.3<tab>Field 4

where <tab> is Tab, and (CR) is carriage return. When importing into Excel, i want all 3 lines in Field 3 (Field 3.1, Field 3.2 and Field 3.3) be in one cell, but multiline. So basically i want excel to ignore carriage return. How do i do that???
I have some control on which characters to use to delimit the text file (e.g. i can change <tab> and (CR) to whatever i want. here is what i have tried so far:

changed (CR) character to carriage return (Ascii 13)
changed (CR) character to Line Feed (Ascii 10)
changed (CR) character to carriage return + Line Feed (Ascii 13 + Ascii 10)
Tried enclosing Field 3.1(CR)Field 3.2(CR)Field 3.3 into single or double quotes.

none of these seem to work: I get this in output:
Field 1 Field 2 Field 3.1
Field 3.2
Field 3.3 Field 4

does anyone know of a solution. it would be greatly appreciated

Hi this is a 2 part question. Again - im new to this so please be
patient.

1 - Would anyone be able to explain to me how to get excel to ignore
the "$" symbol if it is entered in a text box? The situation is:
there is a userform with many boxes one of them is "Salary" text box
(tbxSal)..
The user enters the salary and this is then used in a calculation.
I anticipate that it will be very common for users to include a $.
Therefore I dont want to use an error message rather I would just
prefer excel to just ignore that character and continue with the calc.
How would I do this?

2 - And send up an error message if a letter or other character is used
rather than the "do you wish to debug" thing. If a letter eg "a" is
entered it throws this up, I assume because Im using the IsNumeric
thing.

here is the relevant code.
Thanks in advance.
B

Private Sub tbxSal_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'Calcs Income Protection Benefits and premium and "DEATH ONLY/TPD + IP"
total benefits
' and recalculates if salary changed.

If tbxSal <> "" Then
If IsNumeric(tbxSal) = True Then
Dim BenN As Double
Dim BenS As Double

maxben = Range("Admin!B10")
MaxSal = Round((maxben / (((Range("Admin!C10") +
Range("Admin!D10")) / 100))), 2)
BenN = Round(((tbxSal * ((Range("Admin!C10") / 100))) / 52),
2)
BenS = Round(((tbxSal * ((Range("Admin!D10") / 100))) / 52),
2)

If tbxSal > MaxSal Then
BenN = Round(((MaxSal * ((Range("Admin!C10") / 100))) / 52),
2)
BenS = Round(((MaxSal * ((Range("Admin!D10") / 100))) / 52),
2)
End If

tbxWkBenN = FormatCurrency(BenN, 2)
tbxWkBenS = FormatCurrency(BenS, 2)
tbxWkBenT = FormatCurrency((BenN + BenS), 2)
End If

End If

If IsNumeric(tbxSal) = True Then

If cbxOcCat <> "" Then
Dim Col As String 'Premium Rate Column
Dim Row As String 'Age row
Dim PR As String 'Cell location containing Premium Rate

Row = cbxAge - 11

If cbxSex = "Male" Then
Select Case cbxOcCat
Case Is = ("White Collar")
Col = "B"
Case Is = ("Light Blue Collar")
Col = "D"
Case Else
Col = "F"
End Select

ElseIf cbxSex = "Female" Then
Select Case cbxOcCat
Case Is = ("White Collar")
Col = "C"
Case Is = ("Light Blue Collar")
Col = "E"
Case Else
Col = "G"
End Select

End If

Hello - I've been trying to figure out how to do this for a while now and while I have found some results on the web, they don't seem to be working for me. I'm trying to take the data that is exported from a work app and remove certain trailing characters. This particular application appends sequential line items to the end of the description for each line in the Excel output. This is an example:

Catalyst 3750X 48 Port PoE IP Base switch-1
Cisco StackWise 50CM Stacking Cable-2
AC Power Cord for Catalyst 3K-X (North America)-3
Catalyst 3K-X 1G Network Module option PID-4
Catalyst 3K-X 715W AC Secondary Power Supply-5

What I'm trying to do is just get rid of the "-xxx" at the end of the description via a macro that I can apply to an entire column. Depending on the number of rows it could be -1 or -999. It also doesn't necessarily occur on ever line so I need it to just ignore anything that doesn't have "-xxx" a the end of the cell. Meaning if its doesn't apply I don't want it to remove pertinent text just because there is a "-" in the description. That may be asking a lot I know...

The closest that I've found is a TRIM function, but I haven't figured out how to convert that to VBA so I can insert it into an existing macro. It also requires that the result go into an adjacent cell rather than replacing the existing data, which is what I would prefer. This is the formula I found:

=TRIM(LEFT(A1,FIND("$",SUBSTITUTE(A1,"-","$",LEN(SUBSTITUTE(A1,"-","%%"))-LEN(A1)))-1))

Any help would be greatly appreciated!

Thanks,
Ric

Hi,

I hope that someone can help me out with this. I have a daily log file (in.txt format) that I need to import into my workbook, & then strip some extraneous characters. The log file data looks like this:

Code:
"N30109, chrisl, 11/12/2007"
"N30110, chrisl, 11/12/2007"
"N30111, chrisl, 11/12/2007"
I need my finished data to look like this (please ignore the formulas, I used those to get the data to look as I need it):

******** ******************** ************************************************************************>Microsoft Excel - WB JN log.txt___Running: xl2002 XP : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF2G2H2F3G3H3F4G4H4=
FGHIJK1      2N30109chrisl11/12/2007   3N30110chrisl11/12/2007   4N30111chrisl11/12/2007   5      6      7      8      WB JN log 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I assume that I will have to import the text as an array (?), but I'm not certain. Also not sure how best to strip the unwanted quotes & commas. Can anyone help?

Regards,

Mark

Hi,

Edited to add Code tags - sorry for the omission.

This is my first time using Excel 2003 VBA. I have picked up Walkenbach's Excel 2003 Power Programming, but am having trouble applying it to my needs.

I am working with a spreadsheet that is organized in such a way that it makes sorting difficult (it is imported from another program), so want to use VBA to extract the data.

1. I want to build a master list from the data. To do this, I propose a loop that will go down and collect a list of items. I also want to count the number of items in the master list.

This is what I am trying to count the items, but I get an error at the If itemtype:

 For
rowcounter = 3 To 500
       itemtype = Cells(rowcounter, 4).Value
       If itemtype = 1 Then itemcount = itemcount + 1

        End If
        
 Next rowcounter
What is the correct syntax?

2. How do I then create a loop that will count sub-items until it hits a blank cell?

I am thinking of this command: ActiveCell.Offset(1, 3)

But how do I use this so it loops until it hits a blank cell?

3. How do I test for leading characters, then ignore the cell if they are present? I want to see if the cell begins with "Total", and if does, then ignore it?

Are there any other books or on-line resources that have lots of sample code? I have searched, but not found anything applicable. I have found the Walkenbach code samples to be limited.

Thanks

Hi all,

Long time fan, first time post. I'm a [BIG emphasis] complete beginner [/BIG emphasis] to VBA so I'd like to apologize in advanced for my ignorance. I hope this is a very simply issue. It is probaby out there in the interwebs, but I just haven't been able to find an answer... then again, I don't know exactly what I'm looking for, or how to phrase it.

I'm (probably recreating the wheel) trying to set up code to format mailing/property address information. But I'm having difficulties with the ZIP code portion. This is what I have so far:

Sub format_address()
    Call Step1_VerifyColumns
    Call Step2_FormatZIPCode
End Sub
Private Sub Step1_VerifyColumns()
    MsgBox "Please make sure you have added three (3) empty" & _
    vbNewLine & "columns to the right of the address range." & _
    vbNewLine & vbNewLine & "" & _
    "Do you wish to continue?", vbYesNo + vbQuestion, "Column Verification"
    If response = vbNo Then
        Exit Sub
    End If
    If response = vbYes Then
        Call Step2_FormatZIPCode
    End If
End Sub
Private Sub Step2_FormatZIPCode()
    Dim cell As Range
    For Each cell In Selection
        cell.Offset(0, 1).FormulaR1C1 = Right(cell, 5)
            If IsNumeric(cell.Offset(0, 1)) Then
            cell.Value = Replace(cell.Value, cell.Offset(0, 1).Value, _
            "", 1, 1, vbTextCompare)
            Else
            cell.Offset(0, 1).Clear
            End If
    Next
End Sub
This is more or less what I'm trying to do:
Sub Step2_Expanded()
    Dim cell As Range
    For Each cell In Selection
        cell.Offset(0, 1).FormulaR1C1 = Right(cell, 5)
            If IsNumeric(cell.Offset(0, 1)) Then
            Else
            cell.Offset(0, 1).Clear
            End If
            'If (cell.Offset(0, 1)) is 5 numbers/characters long Then
            'Else
            'cell.Offset(0, 1).Clear
            'End If
            If IsNumeric(cell.Offset(0, 1)) Then
            cell.Value = Replace(cell.Value, cell.Offset(0, 1).Value, _
            "", 1, 1, vbTextCompare)
            End If
    Next
End Sub
I'm basically trying to run the Step2 Macro only when the transposed cell values are 5 numbers.

Hopefully this makes sense! Again, I want to apologize for my ignorance.

Thanks,

-H-

I have some data that has an attempt a footnotes consisting of * and superscript numbers after the data and at the bottom of the table I have a notes section. Some of my data also has < . I want to graph this ignoring the notes and < but still using the data in these cells. is there an easy way to do this or do I have to create a duplicate the table and remove these characters each time I want a new graph (the data is always being updated)

Example of my data
Column A
1/1/02
4/2/02
7/1/02
11/7/02*
2/3/03
6/15/03

Column B
1200
456
<0.05
364*
<0.05
34**

some of the column be have super script number but I don't know how to show that here

I am pasting e-mail addresses into column A. What I want to do is to check those e-mail addresses and remove all unnecessary characters like !#$%^&*()_+, etc. After special characters are stripped, I would like to check for e-mail validity. In other words I want to make sure that e-mail consists of name@domain.xxx. I wrote a function that takes value from each cell in column A and strips special characters in column B, but I would like that to occur in the same column where e-mail addressees are pasted, ie column A. I know that there is a function Worksheet_Change that would do what I need, but I can't seem to figure out how to combine two of them. Can anybody help?

Below is the function that strips special characters from string in the cell. Also below is sub that checks for e-mail address validity. So what I want is:

1. Be able to paste list of e-mail addresses in column A
2. Remove special characters from those e-mal addresses
3. Check for e-mail validity (color cell red if e-mail address is invalid and green if e-mail address is valid)

1. Public Function RemoveSpaces(strInput As String)
' Removes all spaces from a string of text
Test:
If InStr(strInput, " ") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, " ") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, " "))
GoTo Test
End If
If InStr(strInput, "~") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, "~") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, "~"))
GoTo Test
End If
If InStr(strInput, "`") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, "`") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, "`"))
GoTo Test
End If
If InStr(strInput, "!") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, "!") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, "!"))
GoTo Test
End If
If InStr(strInput, "#") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, "#") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, "#"))
GoTo Test
End If
If InStr(strInput, "$") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, "$") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, "$"))
GoTo Test
End If
If InStr(strInput, "%") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, "%") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, "%"))
GoTo Test
End If
If InStr(strInput, "^") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, "^") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, "^"))
GoTo Test
End If
If InStr(strInput, "&") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, "&") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, "&"))
GoTo Test
End If
If InStr(strInput, "*") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, "*") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, "*"))
GoTo Test
End If
If InStr(strInput, "(") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, "(") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, "("))
GoTo Test
End If
If InStr(strInput, ")") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, ")") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, ")"))
GoTo Test
End If
If InStr(strInput, "-") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, "-") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, "-"))
GoTo Test
End If
If InStr(strInput, "+") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, "+") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, "+"))
GoTo Test
End If
If InStr(strInput, "=") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, "=") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, "="))
GoTo Test
End If
If InStr(strInput, "{") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, "{") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, "{"))
GoTo Test
End If
If InStr(strInput, "}") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, "}") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, "}"))
GoTo Test
End If
If InStr(strInput, "[") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, "[") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, "["))
GoTo Test
End If
If InStr(strInput, "]") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, "]") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, "]"))
GoTo Test
End If
If InStr(strInput, "|") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, "|") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, "|"))
GoTo Test
End If
If InStr(strInput, "") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, "") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, ""))
GoTo Test
End If
If InStr(strInput, ":") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, ":") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, ":"))
GoTo Test
End If
If InStr(strInput, ";") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, ";") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, ";"))
GoTo Test
End If
If InStr(strInput, "'") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, "'") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, "'"))
GoTo Test
End If
If InStr(strInput, "") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, ">") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, ">"))
GoTo Test
End If
If InStr(strInput, ",") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, ",") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, ","))
GoTo Test
End If
If InStr(strInput, "?") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, "?") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, "?"))
GoTo Test
End If
If InStr(strInput, "?") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, "?") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, "?"))
GoTo Test
End If
If InStr(strInput, "/") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, "/") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, "/"))
GoTo Test
End If
If InStr(strInput, """") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, """") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, """"))
GoTo Test
End If

End Function

2. Option Explicit
Sub RegExp_Early_Test_3()
'Reference must be set to Microsoft VbScript Regular Expression 5.5
'Dimension the RegExp objects
Dim RegEx As VBScript_RegExp_55.RegExp
Dim Myrange As Range, C As Range

' create the RegExp Object with early binding
' set the Excel range to parse the ActiveSheet B3:B12
Set RegEx = New VBScript_RegExp_55.RegExp
Set Myrange = ActiveSheet.Range("B3:B12")

' set the RegExp parameters
With RegEx
.MultiLine = False
'looking at whole string so Global is irrelevant
.Global = False
'ignoring case will simplify pattern to [a-z] as it removes the need for using [a-zA-Z]
.IgnoreCase = True
'The test must match the entire string (set by the ^ at the start and the $ at the finish)
'There must be one @ with at least one subsequent "."
'The email must end in an alphabetic string
' Dave's original pattern was "^([a-z0-9_.-]+)@(([a-z0-9-]+.)+)([a-z]+)$"
.Pattern = [Example3PatternA]

End With

For Each C In Myrange
' test for TRUE or FALSE
If RegEx.Test(C) = True Then
C.Offset(0, 1) = "Valid Email"
C.Offset(0, 0).Interior.ColorIndex = 4
C.Offset(0, 2) = RegEx.Replace(C, "$1")
C.Offset(0, 3) = RegEx.Replace(C, "$2")
' The third submatch contains the last portion of the middle domain - not wanted
C.Offset(0, 4) = RegEx.Replace(C, "$4")
Else
C.Offset(0, 1) = "Invalid Email"
C.Offset(0, 0).Interior.ColorIndex = 3
End If
Next

Set Myrange = Nothing
Set RegEx = Nothing

End Sub

First the problem- I'm trying to modify a large database with fields such as this "+12/+7/+2" by removing +'s (which are unnecessary for my purposes) and leaving the /'s. The 'Find and Replace' function should do this just fine- except it won't. Whenever I remove the +'s, what's left becomes a date. The cells start off formatted as text, but as soon as I 'find and replace' anything, they become date fields.

How in the heck can I get Excel to stop doing this?!?!?!

I've been googling advice on this subject for almost an hour. Almost all the similar solutions to my problem have included- "just format the cells as TEXT and it should work just fine" or "add an apostrophe(or a space) before your numbers and that should do it."

My problem with those "solutions" are twofold- 1) my cells supposedly ARE formatted as text, but apparently Find and Replace can ignore that and change it to whatever it wants, and 2) why should I have to add in unnecessary characters just to trick Excel into not using a "feature" I don't want it to use in the first place, especially since my whole point in doing this is to get rid of some unecessary characters!

So, is there a way to stop Find and Replace from applying the format it "thinks" you want to use instead of the one you actually told it to use?

A permanent solution to this problem would be greatly appreciated- and by more than just me I'm sure.
-Dave

Excel 2003: I have found this problem with an embedded chart into
which I copy a map. I then use the Chart Events to track the mouse
cursor and 'drop' waypoints onto the map (making this work is another
topic on it's own!). However, if the chart is wider than the screen
window then as it is scrolled left a point is reached where these
Autoshapes disappear. By trial and error, I have found that they can
be made to re-appear by first selecting a sheet cell then selecting
the chart following every scroll. The problem seems to be linked to
the 'original' RHS boundary of the Window, and any shapes right of
this line will disappear as the window scrolls left. I have also found
that the Chart ToolTips (the tags that say Chart Area or Plot Area
etc. also vanish at around this point.

The problem is repeatable in that it happens on different PCs under
different OS (even in Excel for Mac under OS X). If you want to try
it, then open a Workbook and put a chart on the sheet (any chart with
any data or even no data at all will do). Now select the chart and
draw some shapes on it and evenly distribute them across the chart.
Make sure that you have at least one on the far RHS. To make sure that
the shapes are on the Chart and not the Sheet, grab them and try and
drag them off the Chart - if they are correctly placed you will not be
able to move them past the Chart edges. Now grab the chart RHS handle
and drag it until it is wider than the Window. Use the scrollbars to
move around and you will see that some shapes on the RH side of the
chart are gone. Exactly which ones depends on the width of your Chart
and Window. To see them again, click in a cell and then select the
Chart. Scroll again and it's gone. Repeat until bored.

An extensive search of archive Forums shows that this effect has been
seen by others, as far back as xL97. However, the answers show that
the readers have almost always misunderstood the problem. The most
frequent responses are to a) ensure the Autoshapes are on the Chart
not the Sheet, and b) to group all the shapes. Neither of these will
work, nor will any attempts to change the drawing Order.

I can offer a solution. It's not a real solution derived from
understanding the problem, but more of an engineers work-around (and I
am an Engineer, though in aerospace not software). The approach I have
adopted is to try and scroll using a procedure or event into which
code can be embedded to emulate manually re-selecting the sheet and
chart.

Having tried several methods involving the cursor that all failed, the
one I have made work uses the OnKey procedure. API calls could
probably be used too, but I want this code to run on both a PC and a
Mac.

The code below only deals with a Left scroll but you can probably
figure out how to do Right, Up and Down.

In the Worksheet module as a Worksheet Activate event

'activate the Application Object Window - must be first
else scrollbars will not clear every time
Windows(ActiveWorkbook.Name).Activate
'hide the cursor scrollbars
ActiveWindow.DisplayVerticalScrollBar = False
ActiveWindow.DisplayHorizontalScrollBar = False
're-define the left arrow key to cause a small scroll
Application.OnKey "{LEFT}", "LeftShift" LeftShift code in
a Module
'activate the chart ready for use or bypass step if no
chart on sheet
On Error Resume Next
ActiveSheet.ChartObjects(1).Activate

In the Worksheet module as a Worksheet Deactivate event

'activate the Application Object Window
Windows(ActiveWorkbook.Name).Activate
'put back the cursor scrollbars
ActiveWindow.DisplayVerticalScrollBar = True
ActiveWindow.DisplayHorizontalScrollBar = True
'put the left arrow key back as it was
Application.OnKey "{LEFT}"

Then in a module as a Procedure

Sub LeftShift()

'inhibit screen updating (does not make much difference)
Application.ScreenUpdating = False
'set Application Window active before scrolling
Windows(ActiveWorkbook.Name).Activate
'move stuff left by a little
ActiveWindow.SmallScroll ToRight:=-1
'activate first chart (one chart per sheet expected) ignore
if no chart found on sheet
On Error Resume Next
ActiveSheet.ChartObjects(1).Activate
'reactivate screen updating
Application.ScreenUpdating = True

End Sub

When you select this Sheet only, the action of the arrow key will
change. It will now cause scrolling and the required calls to ensure
that the Shapes are still visible after the scroll. As a clue, it will
get rid of the normal scrollbars so the key is the only way to scroll.
When you select another Sheet, all these changes are put back so that
the scrollbars re-appear and the arrow key behaves normally again.

Limitation: After scrolling, it is always Chart1 on the sheet that is
left activated.

Tip: Use OnKey for ALT+Left to call a procedure that moves 10 or more
cells at a time for faster scrolling emulating a Page Left.
Tip: .For uniform scrolling, set the underlying cells in the sheet to
be small and square. If you are not using the worksheet cells for
data, then the following code added to the WorkSheet Activate code
will do it: The Chart has to be unlocked from the cells first if it is
not to be distorted by this action.

Dim RowNo As Integer, ColNo As Integer
'If there is a Chart 1 then unlock it from cells
On Error Resume Next
ActiveSheet.ChartObjects(1).Placement = xlFreeFloating
'disable screen updating so that it happens faster and
without screen flicker
Application.ScreenUpdating = False
'fix the rows and columns to be equal size
For RowNo = 1 To 255 'max 65538 but 255 is Ok for an
A3 portrait chart
Rows(RowNo).RowHeight = 7.5 'ht in points
Next RowNo
'Columns are in character width, smallest unit =1 is approx
7.5 points
For ColNo = 1 To 255 'max 255 which is Ok for an A3
Landscape chart
Columns(ColNo).ColumnWidth = 1
Next ColNo
Application.ScreenUpdating = True

If anyone can provide a better fix for this problem I would be glad to
use it to replace my efforts.

V8R

Suggestions are both for Word and Excel where applicable. These suggestions
are mostly based on office 2000.
1. Word count should be in Excel too and should give such information like
- How many characters are there in a cell,
E.g. How many “a”, “A”, “b”, “B” Etc. are there in a cell
- How many symbols are there in a cell,
E.G. How many “!” “+” “=” Etc. are there in a cell.
- How many Upper case characters
- Lower
- Vowels
- Vyanjan
- Spaces
- Size in KB
- Is any formula used in the cell? If yes then how many sub formulas are
there in a main formula?

In Microsoft word above mentioned points should be given as a result word
count.

2. There should more divider sticks while we split the work sheet (both
vertical and horizontal) we can select that how many divider sticks we want,
both vertical and horizontal

3. Hide and Unhide actions should also be repeated if we press F4 or there
should another button that repeats the action of Hide – Unhide.
4. When we cut, then there should a facility of Paste Special same as like
“Copy”.
5. Freeze pan action should be used more than one time at a time both
horizontal and vertical. In short Freeze Pan should also be used for 2 or
more times.
6. Transpose should have an extra facility to transpose the selected cells
from down to up and up to down.
Like,
1
2
3
4
5
Can be transposed as
5
4
3
2
1

Dream. The programming made in excel should be converted in to C, C++, VB,
JAVA & All other languages. Excel Should be the common platform for all other
languages.
7. Suppose some rows and columns are hidden. In this situation there should
an option in which we can copy only visual cells and paste them anywhere.
Same way Paste Special should have an option to paste on only visual cells.
8. Paste Special should have an option to write only value of formula like,
=a1+c3, =if(g14,g2,h4) etc.

9. There should an option that allows not to save in selected cell
10. There should an option of Unhide from this to that. (This line to that
line)
11. Remove Border in Different Shape: We can draw borders in excel. There
should an option to remove border in the same manner we draw. Like we can
draw border in square shape then removing that border in square shape should
also be made available.
12. Formula For making any number two digits, three etc digits by putting 0
in the left side of a number. Like if we want to make 1 in 4 digits, 0001
should be the formula result

13. When formula is long, and the formula bar is full then the formula
display begins in the second line and therefore we cannot see row name A, B,
C, D then it becomes difficult to check the formula. There should something
helpful.

14. When we press f2 to see the formula then the next few cells in the right
side are becomes invisible to see the box. There should be something trick to
see those cells.
15. Unhide should be done from both upper and lower side. (One by one and
from “this to that” option should be available.)
16. Formula- write the text from a cell till “ “(Two Spaces come) or Write
the sentence till “&” not come.
17. What changes have made within the worksheet that should be maintained.
We can also recover our work from that point. So there should two kind of
“SAVE” 1. Internal Save that can help us not to loose any entered data. If
electricity gone or any thing else then entered data will be lost. 2.
External Save that we are already using as “SAVE”.
18. Replace Option should have a facility to replace more than one thing
together at one time. Like if selected criteria contains a5=1,a5=2,a5=3 if we
want to replace 1 to 2, 2 to 3 and 3 to 1, then we have to replace 3 times.
If we replace 1 to 3 or 3 to 1 at first time then there will be problem that
we can not replace 3 to 1 or 1 to 3 otherwise all 1s will be 3s or all 3s
will be 1s. So if there is a option like
Replace 1
Replace 2
Replace 3
And more in a single selection at a time, then there will be no problem.
19. Find, Replace, Goto windows should be displayed on the top of the screen
when used. Because of that window, we cannot see data near the displayed area
because there is a window of Find, Replace or Goto. We have to move that
window. If from the beginning that window is displayed on the top or the
bottom of the screen, because the word, which we have to find, is displayed
in the middle of the screen.
Or there is another way that the found data should be displayed from the top.
In short the found data (result) should not behind the window.

20. There should an option in the popup menu of the right click (Spelling or
Grammar Mistake – Red or Green Underline)
Ignore All and > Cut
Copy
So we don’t have to copy or cut the word again after ignoring the
underline.

21. Increase Indent and Decrease indent should have a selection to skip
minimum space (as one space).
22. There should an option to make bold or differ the words those are
grammatically false or having spelling mistakes.
E.g. we select a paragraph, when we click the right mouse button then in the
pop up menu there should an option to differ the words those are under lined.
There should two options, one for green and one for red. So that if we copy
the text at any other place like in paint brush or compose e-mail box or any
other place, we can differentiate those words.
23. Drawing toll bar should have another button like “Square” by which we
draw a square.But the square about which I am telling should have an extra
facility to show the height and length of the square exactly connected with
that square. We can also change the parameters like inches, foot, and
centimeters etc in compressed form. Whichever parameter we want to use should
make available to us.At least it should provide an approximate figure of the
square.
24. In Microsoft word there should be a facility to give Name or Identity to
any selected Paragraph, line, or any character or criteria.

Example. Here is given the paragraph.

I believe in hearing great ideas of an ordinary man who just knows what he
wants to do. He doesn’t know the proper words or style what he wants to say.
He always tries to explain the thing what he wants to do, in any style or
format. It is the duty of the authority to respond him in a proper manner.

Step – by – step.
1. Select the paragraph.
2. Right click on the selection.

3. Here I want to suggest a button in the pop up menu named “GIVE IDENTY” or
anything like that.
4. While clicking on that button word will ask us to give a name or identity
to that selected paragraph. (or line or any character, number, criteria etc.)
5. There should be a place in which the name is displayed while the cursor
is moving on from that paragraph which has given a name. Just as like the
Column no, Page No. is displayed on line.

Assume that we have given a name or identity to above paragraph as “Book
Name – Reference”.
Now when the cursor is on that paragraph, “Book Name – Reference” will be
displayed on the screen at the lower right part of the screen.

25. The word “well wisher” in Microsoft word contains mistake while
indicates the grammar check (Green) line. While we right click on the green
underlined word “well wisher”, the MS Word suggests “well-wisher”. Again the
word “well-wisher” becomes green underlined word!! And asks for check the
grammar. When we again make right click on “well-wisher”, it again asks for
“well wisher”!
26. If we type “I” instead of “in”, “I” becomes an uppercase character. It
is necessary. But when we use backspace to type “in” the “I” does not become
in lowercase character. It may also necessary. I just want to say that if it
is possible to convert the uppercase “I” into lowercase “I” while
backspacing, then it will help a lot to the users.
If again we give space after “I”, it should become an uppercase “I”.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

First the problem- I'm trying to modify a large database with fields
such as this "+12/+7/+2" by removing +'s (which are unnecessary for my
purposes) and leaving the /'s. The 'Find and Replace' function should
do this just fine- except it won't. Whenever I remove the +'s, what's
left becomes a date. The cells start off formatted as text, but as soon
as I 'find and replace' anything, they become date fields.

How in the heck can I get Excel to stop doing this?!?!?!

I've been googling advice on this subject for almost an hour. Almost
all the similar solutions to my problem have included- "just format the
cells as TEXT and it should work just fine" or "add an apostrophe(or a
space) before your numbers and that should do it."

My problem with those "solutions" are twofold- 1) my cells supposedly
ARE formatted as text, but apparently Find and Replace can ignore that
and change it to whatever it wants, and 2) why should I have to add in
unnecessary characters just to trick Excel into not using a "feature" I
don't want it to use in the first place, especially since my whole point
in doing this is to get rid of some unecessary characters!

So, is there a way to stop Find and Replace from applying the format it
"thinks" you want to use instead of the one you actually told it to
use?

A permanent solution to this problem would be greatly appreciated- and
by more than just me I'm sure.
-Dave

--
DavidB

Hi,

I'm trying to add to an if statement, saying that if a cell is equal to a certain value, then to ignore it. I'm new to visual basic and I just dont know how to declare that value. I have in bold what I have tried to do.

If ActiveCell.Characters.Count > 13 Or Not only_text(ActiveCell) Or ActiveCell.Value("RM UCONN SFA") Then

Thanks for the help,

-Steve


No luck finding an answer? You could always try Google.