Thread: How to trim a character from a cell ?
View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
dex dex is offline
external usenet poster
  
Posts: 5
Default How to trim a character from a cell ?
Here is the code I have so far in my macro module: Where would I put
the additional code you had stated earlier? Because I used a
conditional format to accomplish what we've been talking about but the
thing is that the character "Q" is causing the problem. And the
conditional format is not recorded on this macro. I have to set it up
after I run the macro.
Sub caveenLair()
'
' caveenLair Macro
' Macro recorded 11/24/2005 by Owner
'

'

Sheets.Add
ActiveCell.FormulaR1C1 = "Bin"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Part#"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Quantity"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Unit Price"
Range("D1").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "Sap Qty"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Unit Price"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Total"
Columns("F:F").Select
Selection.ColumnWidth = 12.43
Columns("B:B").Select
Selection.ColumnWidth = 17.57
Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],stock,3,0)"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],stock,2,0)"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]*RC[-1])"
Range("A1").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreaterEqual _
, Formula1:="1"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
End With
With Selection.FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.FormatConditions(1).Interior.ColorIndex = 15
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("B1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("C1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("D1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("E1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("F1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Range("E1").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Range("D1").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Range("F1").Select
Columns("E:E").ColumnWidth = 9.86
Columns("D:D").Select
Selection.ColumnWidth = 10.14
Selection.ColumnWidth = 11.29
Columns("E:E").Select
Selection.ColumnWidth = 10.71
Columns("F:F").Select
Selection.ColumnWidth = 15.29
Columns("F:F").Select
Selection.ColumnWidth = 12.43
Range("D2:F2").Select
Selection.AutoFill Destination:=Range("D2:F207"),
Type:=xlFillDefault
Range("D2:F207").Select
Range("L194").Select
ActiveWindow.ScrollRow = 176
ActiveWindow.ScrollRow = 174
ActiveWindow.ScrollRow = 173
ActiveWindow.ScrollRow = 171
ActiveWindow.ScrollRow = 170
ActiveWindow.ScrollRow = 168
ActiveWindow.ScrollRow = 166
ActiveWindow.ScrollRow = 163
ActiveWindow.ScrollRow = 161
ActiveWindow.ScrollRow = 159
ActiveWindow.ScrollRow = 155
ActiveWindow.ScrollRow = 152
ActiveWindow.ScrollRow = 150
ActiveWindow.ScrollRow = 146
ActiveWindow.ScrollRow = 144
ActiveWindow.ScrollRow = 140
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 134
ActiveWindow.ScrollRow = 131
ActiveWindow.ScrollRow = 127
ActiveWindow.ScrollRow = 123
ActiveWindow.ScrollRow = 119
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 109
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 99
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 88
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
ActiveSheet.Shapes.AddTextEffect(msoTextEffect1, "Click smilye
icon" & Chr(13) & "" & Chr(10) & "to start" _
, "Arial Black", 12#, msoFalse, msoFalse, 311.25, 192#).Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 8
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.RGB = RGB(0, 0, 0)
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.TextEffect.Text = "Click smiley icon" &
Chr(13) & "" & Chr(10) & "to start"
Application.CommandBars("WordArt").Visible = False
Selection.ShapeRange.IncrementLeft 84#
Selection.ShapeRange.IncrementTop -189#
Range("B1").Select
End Sub

Reply With QuoteReply With Quote