Excel VBA - Working with a worksheet and FormulaR1C1


I have an existing worksheet where I need to convert values within the range to a percentage format. The values are read into the worksheet from a file, therefore I do not know what the range is.

I have tried the following piece of code, however, I am getting a compile error, stating that "Argument not optional" the word range is highlighted in the line: Sum(.Range.Cells / 40 * 100).

Would anyone have any ideas how to correct this error?

Sub convertRawMarks()
  Dim R As Range
    With CSVsheet
        Set R = .Range(.Range(RawDataSearch), .Cells.SpecialCells(xlCellTypeLastCell))
        .Range.Cells.FormulaR1C1 = Sum(.Range.Cells / 40 * 100)
        '.Cells.FormulaR1C1 "=SUM(.Cells/40*100)"
    End With
    Set R = Nothing
End Sub

many thanks for help or suggestions in advance

Who is Participating?
Tommy KinardConnect With a Mentor Commented:
Suggest you change sheets if you import another file, or when you clear the sheet reformat the % to general data. But of course the sheet will have to be reformatted with the % where required.
Tommy KinardCommented:
.Range is not defined. R is the Range. You are also working with the undefined .Range not R.

Do you at least know what column(s) are effected?
Also you may want to use cells(?,?).value just to make sure you are not trying to get a sum of "AB".
NatalieVanceAuthor Commented:
Hi DragonTooth,

Thaks for the comments, The file is read into the worksheet, I have no way of knowing what columns, rows,etc will be effected.  I have the following code working however, if a file of results has been read into the worksheet previously and the current file that is read in, is smaller than the previous file.  Some of the cells which previously contained numeric values are instantiated to 0.00%.

Sub ConvertRawMarks_1()
    Dim R As Range, Cell As Range
    'CSVsheet.Cells.Clear 'delete contents on targetsheet

    With CSVsheet
        Set R = .Range(.Range(RawDataSearch), .Cells.SpecialCells(xlCellTypeLastCell))
        'format the entire found range to percentage format. Non numeric cells will not be affected
        R.NumberFormat = "0.00%"
        'go through each cell of the range
        For Each Cell In R
            'if cell holds a numeric value, divide by 40. No need to multiply with 100 for the correct format: percentage format already does that
            If IsNumeric(Cell.Value) Then Cell.Value = Cell.Value / 40
    End With
    Set R = Nothing: Set Cell = Nothing
End Sub

Many thanks for your help and suggestions.
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Post your closing recommendations!  No comment means you don't care.
All Courses

From novice to tech pro — start learning today.