Hi, I have a macro that compares two numbers from when i scan a
quantity on a part number. And if the first cell is greater than the
second cell, the first cell should highlight a color, which is working
with no problems. But the problem is that everytime I scan a part
number the amount always begins with a "Q" example:Q000003. And it will
always give me the wrong results. The cell will still highlight even
though the first cell is less than the second cell, because of the "Q".

I know this because I've tried putting two numbers without the Q
manually and I always get the right results. Basically, I don't know
the syntax to use the trim method or mid method in vb6. I just need a
method to remove the character "Q" but not the numbers. And would I put
this syntax in my module where I have my macro?


Thanks.

Maybe:

Sub Macro1()

Dim Num1 As Long
Dim Num2 As Long

Num1 = Mid(Range("A1").Value, 2, 255)
Num2 = Mid(Range("B1").Value, 2, 255)

If Num1 Num2 Then Range("A1").Interior.ColorIndex = 36

End Sub

Hope this helps
Rowan

dex wrote:
Hi, I have a macro that compares two numbers from when i scan a
quantity on a part number. And if the first cell is greater than the
second cell, the first cell should highlight a color, which is working
with no problems. But the problem is that everytime I scan a part
number the amount always begins with a "Q" example:Q000003. And it will
always give me the wrong results. The cell will still highlight even
though the first cell is less than the second cell, because of the "Q".

I know this because I've tried putting two numbers without the Q
manually and I always get the right results. Basically, I don't know
the syntax to use the trim method or mid method in vb6. I just need a
method to remove the character "Q" but not the numbers. And would I put
this syntax in my module where I have my macro?


Thanks.

Would I include this code in the macro I already have or in a different
module?

Probably include it in the macro you already have but it's hard to say
without seeing that. Maybe post the code you have so far...

Regards
Rowan

dex wrote:
Would I include this code in the macro I already have or in a different
module?

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

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

Hi Rowan do you know the syntax to use the to upper for characters in
VBA. For example if the user enters a capitol P or a lower case p the
form will still run without any runtime errors. Here is my code I have
for my form. Everything works fine, I just want to know how to write
the code so that if the user enters a lowercase character it would
still work.

thanks,

r = Application.CountA(Range("A:A"))

If Left(parttxt.Text, 1) = "P" And Left(qtytxt.Text, 1) = "Q" Then
Range("A1").Offset(r + 1, 0) = Me.parttxt.Value
Range("B1").Offset(r + 1, 0) = Me.qtytxt.Value
Me.parttxt.Value = ""
Me.qtytxt.Value = ""
Me.parttxt.SetFocus
Else: MsgBox "Wrong Part or Qty Scanned", vbCritical
Me.parttxt.Value = ""
Me.qtytxt.Value = ""
Me.parttxt.SetFocus

End If
End Sub

The command is UCASE so try:

If UCase(Left(parttxt.Text, 1)) = "P"

Hopet this helps
Rowan

dex wrote:
Hi Rowan do you know the syntax to use the to upper for characters in
VBA. For example if the user enters a capitol P or a lower case p the
form will still run without any runtime errors. Here is my code I have
for my form. Everything works fine, I just want to know how to write
the code so that if the user enters a lowercase character it would
still work.

thanks,

r = Application.CountA(Range("A:A"))

If Left(parttxt.Text, 1) = "P" And Left(qtytxt.Text, 1) = "Q" Then
Range("A1").Offset(r + 1, 0) = Me.parttxt.Value
Range("B1").Offset(r + 1, 0) = Me.qtytxt.Value
Me.parttxt.Value = ""
Me.qtytxt.Value = ""
Me.parttxt.SetFocus
Else: MsgBox "Wrong Part or Qty Scanned", vbCritical
Me.parttxt.Value = ""
Me.qtytxt.Value = ""
Me.parttxt.SetFocus

End If
End Sub