Excel VBA - Working with a worksheet and FormulaR1C1

Posted on 2003-03-10
Medium Priority
Last Modified: 2007-12-19

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

Question by:NatalieVance
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 14

Expert Comment

by:Tommy Kinard
ID: 8111877
.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".

Author Comment

ID: 8118098
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.
LVL 14

Accepted Solution

Tommy Kinard earned 120 total points
ID: 8131273
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.

Expert Comment

ID: 9447228
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.

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Six Sigma Control Plans

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question