| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Question Part II
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. "Jello" wrote: 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" wrote: 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 ------------------- Thanks in advance for your help. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Question Part II
Change xlPart to Xl whole
Cells.Replace What:="0", Replacement:="", LookAt:=xlPart, SearchOrder to Cells.Replace What:="0", Replacement:="", LookAt:=xlWhole, SearchOrder HTH "Me" wrote: 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. "Jello" wrote: 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" wrote: 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 ------------------- Thanks in advance for your help. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Question Part II
Perfect!
Thanks again. "Jim Thomlinson" wrote: Change xlPart to Xl whole Cells.Replace What:="0", Replacement:="", LookAt:=xlPart, SearchOrder to Cells.Replace What:="0", Replacement:="", LookAt:=xlWhole, SearchOrder HTH "Me" wrote: 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. "Jello" wrote: 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" wrote: 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 ------------------- Thanks in advance for your help. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 2 part lookup question | Excel Worksheet Functions | |||
| Excel If Then Question - Part 2 | Excel Discussion (Misc queries) | |||
| 2 part macro question (sequence & order) | Excel Worksheet Functions | |||
| 2 part question - macro / command button | Excel Discussion (Misc queries) | |||
| 2 Part Question | Excel Programming | |||
Linear Mode
