Using formulas with imported

[mo0ki]

Hi Everybody,

Basically i'm maintaining a spreadsheet which has a data connection to an SQL database. It imports customer submitted ratings for our employees. There are 4 columns for each employee each with a value out of 10. My problem is that no matter what i do i cannot format these columns as numbers, which is absolutley necessary as averages and such need to be found.

In the format cells dialog box it states they are all numbers, but when I try and create average formulas i get a div#0 error no matter what. Also, the numbers are aligned left which leads me to believe their format has not been changed.

Would anyone be able to help with this? It would be greatly appreciated as I'm relativley new to Excel.

Thanks

[Trebor76]

Hi mo0ki,

Welcome to the forum!!

Try incorporating the following code into your existing macro or run it as a stand alone solution:

Option Explicit
Sub Macro1()

    Dim rngCell As Range
    
    For Each rngCell In Range("B2:E" & Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row) 'Assumes data is in the range B2:E[last row found]. Change to suit.
        If Len(rngCell) > 0 Then
            On Error Resume Next 'OK to ignore 'Run-time error '13' (i.e. text or errors) error messages
                rngCell.Value = rngCell.Value * 1
            On Error GoTo 0
        End If
    Next rngCell

End Sub

Regards,

Robert

[mo0ki]

Hi Robert,

Thanks alot for the welcome and your reply. Unfortunatley I am very new to excel and am not exactly sure where to implement this.

The data connection was already set up on the spread sheet when I started working on it. I've just thought of some ways to gather meaningful information from the raw data in the spreadsheet. Would I implement that code in the 'command text' portion of the 'Definition' tab in 'Connection Properties'? Thats where the current code is which retrieves the data from the database.

Thanks for your help!

[Trebor76]

OK, maybe it's better to do it the manual way via these five steps:

1. Make sure you're on the tab in question and in any empty cell enter an 1 and then copy this to the clipboard (Ctrl + C)
2. Highlight the relevant range and from the shortcut menu (accessed via a right mouse click) select Paste Special
3. From the Paste Special dialog click the Multiply option (radio button) and click OK
4. Press Esc
5. Clear the contents of the cell you used in step 1.

HTH

Robert