Thread: How to trim a character from a cell ?
View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.programming
Rowan Drummond[_2_] Rowan Drummond[_2_] is offline
external usenet poster
  
Posts: 18
Default How to trim a character from a cell ?
I have assumed you want to add the conditional formatting to column A so
that if the number in column A (without the Q) is greater than the
number in Column B (again without the Q) then column A will take on the
formatting. Try:

Sub caveenLair()
Dim newSht As Worksheet
Dim msg As Shape

Set newSht = Sheets.Add
With newSht
With .Range("A1:F1")
.Value = Array("BIN", "Part#", "Quantity", _
"SAP Qty", "Unit Price", "Total")
.HorizontalAlignment = xlCenter
End With
.Range("D2:D207").FormulaR1C1 = "=VLOOKUP(RC[-2],stock,3,0)"
.Range("E2:E207").FormulaR1C1 = "=VLOOKUP(RC[-3],stock,2,0)"
.Range("F2:F207").FormulaR1C1 = "=SUM(RC[-2]*RC[-1])"
.Range("D1:F1").Interior.ColorIndex = 6
.Columns("B:B").ColumnWidth = 17.57
.Columns("E:E").ColumnWidth = 10.71
.Columns("D:D").ColumnWidth = 11.29
.Columns("F:F").ColumnWidth = 12.43
Set msg = .Shapes.AddTextEffect(msoTextEffect1, _
"Click smiley icon" & Chr(13) & "" & Chr(10) & "to start" _
, "Arial Black", 12#, msoFalse, msoFalse, 385.25, 3#)
msg.Fill.ForeColor.SchemeColor = 8

With .Range("A2:A207")
.Range("A1").Activate
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF(AND(LEFT(A2,1)=""Q"",LEFT(B2,1)=""Q"")," _
& "MID(A2,2,255)MID(B2,2,255),FALSE)"
With .FormatConditions(1)
.Font.Bold = True
.Borders(xlLeft).Weight = xlThin
.Borders(xlTop).Weight = xlThin
.Borders(xlRight).Weight = xlThin
.Borders(xlBottom).Weight = xlThin
.Interior.ColorIndex = 15
End With
End With
End With
End Sub

Hope this helps
Rowan

dex wrote:
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