G
Guest
Ok, the following macro ran fine with one exception: Any cell references
containing a zero within a formula came back without the zero.
An example:
Correct: =IF(C639="","",IF(G639="","",C639)) [at line 639]
Incorrect: =IF(C64="","",IF(G64="","",C64)) [at line 640]
As you can see, line 640's formula is missing the 0, therefore making it
refer to line 64. This is the same result for all cell references containg a
zero (at the very least, every ten rows).
What can be done to aleviate this problem?
Thanks again.
containing a zero within a formula came back without the zero.
An example:
Correct: =IF(C639="","",IF(G639="","",C639)) [at line 639]
Incorrect: =IF(C64="","",IF(G64="","",C64)) [at line 640]
As you can see, line 640's formula is missing the 0, therefore making it
refer to line 64. This is the same result for all cell references containg a
zero (at the very least, every ten rows).
What can be done to aleviate this problem?
Thanks again.
Jello said:Hi,
When you are serching for blanks spaces, the code that is doing this is the
replace function via:
Cells.Replace What:=" ", Replacement:="",
so if you want to do it for zeros too I would just add in another similar
line for the zero value so each of your your blocks of code would now be e.g
Range("D16").Select
Selection.Copy
Application.CutCopyMode = False
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False
Cells.Replace What:="0", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False
Notice it is doing the same relecement separately for the cells D16, E16 and
B16.
Me said:I recently acquired an Excel 2003 spreadsheet that searches the entire sheet
for blank spaces. This execution is done with the help of a macro.
My supervisor now wants me to add more code to the macro that will now
include a search for occurrences of zero (0).
I don't really have any coding experience, so I need some assistance. I'm
thinking this isn't anything too difficult for you guys to figure out, and
I'm at a loss as where to turn for help.
-------------------
Sub Truequoteformat()
'
' Truequoteformat Macro
' Macro recorded 12/1/2003 by Licensed User
'
Dim WorkingRange As String
Dim LastRow As Double
'
With Selection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
End With
Columns("A:A").EntireColumn.AutoFit
Range("A1:A2").Select
Selection.EntireRow.Insert
Range("A1").Select
ActiveCell.FormulaR1C1 = "Truequote"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=TODAY()-1"
Rows("1:2").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Range("K4").Select
ActiveCell.FormulaR1C1 = "Last Bid"
Range("L4").Select
ActiveCell.FormulaR1C1 = "Last Offer"
Range("M4").Select
ActiveCell.FormulaR1C1 = "Average"
Range("K5").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-8]="""","""",IF(RC[-4]="""","""",RC[-8]))"
Range("K5").Select
Selection.AutoFill Destination:=Range("K5:L5"), Type:=xlFillDefault
Range("K5:L5").Select
Range("L5").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-9]="""","""",IF(RC[-5]="""","""",RC[-5]))"
Range("M5").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]="""","""",AVERAGE(RC[-2],RC[-1]))"
'Find last row of data
Cells(65536, 1).Select
Selection.End(xlUp).Select
LastRow = ActiveCell.Row
'Fill columns to last row
Range("K5:M5").Select
WorkingRange = "K5:M" & LastRow
Selection.AutoFill Destination:=Range(WorkingRange),
Type:=xlFillDefault
Range("K5:M" & LastRow).Select
Range("D16").Select
Selection.Copy
Application.CutCopyMode = False
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False
Selection.Copy
Application.CutCopyMode = False
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False
Range("E16").Select
Selection.Copy
Application.CutCopyMode = False
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False
Range("B16").Select
Selection.Copy
Application.CutCopyMode = False
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False
ActiveWindow.SmallScroll ToRight:=2
Range("K4:M4").Select
Selection.Font.Bold = True
Range("K3:M3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("K4:M" & LastRow).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Columns("B:J").Select
Range("J1").Activate
Selection.EntireColumn.Hidden = True
Range("N8").Select
End Sub