Microsoft Excel - .Value = .Value? - Asked By Shaoxin on 02-Jan-11 03:35 PM

Hi
Here is part of the code. I don't understand .Value = .Value
What does it mean?

   ' Fill in the Outline view in column A
    ' Look for last row in column B since many rows
    ' in column A are blank
    FinalReportRow = WSR.Cells(Rows.Count, 2).End(xlUp).Row
    With Range("A3").Resize(FinalReportRow - 2, 1)
      With .SpecialCells(xlCellTypeBlanks)
        .FormulaR1C1 = "=R[-1]C"
      End With
      .Value = .Value
    End With

Hi Shaoxin:

again, it would help if you were trying to explain what you are trying to accomplish. The particular statement you are having trouble with I also find troubling, because the Resize property of a range typically is a multi-cell range for which the Value property really isn't defined.

In addition, I am having trouble seeing a reference to the SpecialCells(xlCellTypeBlanks) property of a range without a preceding On Error Resume Next error trapping statement. The reason: if there no blank cells found in the specified range referring to this property will return an error. The safer way to do this would be along the following lines:


Hope this helped,
Rolf

Hi Rolf,

The code is to create a Report Showing Revenue by Product. Thank you very much for your help!

Hi Shaoxin

For your question "I don't understand .Value = .Value What does it mean?"

For data similar to the below; the macro would fill the blank cells in ColA with the data above..for example cell A2 will be applied with a formula =A1...and once the formula is applied   .Value = .Value converts the formula to its actual value.....Run the below macro and chekc out the difference with and without the line .Value =.Value

Col A	Col B
1001	1
	2
1002	1
	2
	3
1003	1
	2
	3
1004	1
	2
	3

Sub Macro()

Set wsr = ActiveSheet

    FinalReportRow = wsr.Cells(Rows.Count, 2).End(xlUp).Row
    With Range("A3").Resize(FinalReportRow - 2, 1)
    With .SpecialCells(xlCellTypeBlanks)
      .FormulaR1C1 = "=R[-1]C"
    End With
    .Value = .Value
    End With
End Sub