Formatting error when switching from Mac to PC

[Jockeyp]

Hi all,

I have built a spreadsheet that imports data from a CSV file and then applies some formulas to do some calculations on the newly imported data. I built the spreadsheet on a Mac and then moved it over to a PC. The macro scripts work perfectly on the Mac, but I get an error when I run them on the PC.

The error comes from the fact that the imported data is not recognised as a number and therefore it cannot complete the calculations in the sum. Things that makes this weird:

1. I have an initial script that converts text to a number based on the csv file import.
2. I then have a formatting script that applies an accountancy format to the columns in question.
3. Finally, I have a calculation script that performs the calculation.

The first two scripts full execute with no errors - and yet, the formatting is not applied to the cells. Then when I run the third script, I get an error as it cannot recognise the data as a number.

Has anyone had anything like this before? Any ideas as to how I can fix it? Need to get this working on both the Mac and PC versions.

I have included the code for the formatting below which is executing, but applying the formatting to the defined columns.

Thanks in advance

Sub Format()
    Dim wsBet_Data As Worksheet
    Dim dataRange As Range
    Dim colG As Range, colJ As Range, colP As Range
    Dim colK As Range
    Dim lastRow As Long
    Dim cell As Range

    ' Set the worksheet
    Set wsBet_Data = ThisWorkbook.Sheets("Bet_Data")

    ' Determine the last row with data in column A (assuming A has data in all rows)
    lastRow = wsBet_Data.Cells(wsBet_Data.Rows.Count, 1).End(xlUp).Row

        ' Convert text in G, J, and P to numbers
        Set colG = wsBet_Data.Range("G2:G" & lastRow)
        Set colJ = wsBet_Data.Range("J2:J" & lastRow)
        Set colP = wsBet_Data.Range("P2:P" & lastRow)
        Set colK = wsBet_Data.Range("K2:K" & lastRow)

        For Each cell In colG
            If IsNumeric(cell.Value) Then
                cell.Value = cell.Value * 1 ' Convert text to number
            End If
        Next cell
       
        For Each cell In colJ
            If IsNumeric(cell.Value) Then
                cell.Value = cell.Value * 1
            End If
        Next cell
       
        For Each cell In colP
            If IsNumeric(cell.Value) Then
                cell.Value = cell.Value * 1
            End If
        Next cell

        ' Apply accounting formatting with £ symbol to column AF and other columns
       
        colG.NumberFormat = "_(* £* #,##0.00_);_(* £* \(#,##0.00\);_(* £* ""-""_);_(@_)"
        colJ.NumberFormat = "_(* £* #,##0.00_);_(* £* \(#,##0.00\);_(* £* ""-""_);_(@_)"
        colP.NumberFormat = "_(* £* #,##0.00_);_(* £* \(#,##0.00\);_(* £* ""-""_);_(@_)"

        ' Convert column K to dates and apply UK date formatting
       
       
        Set colK = wsBet_Data.Range("K2:K" & lastRow)
        For Each cell In colK
            If IsDate(cell.Value) Or IsNumeric(cell.Value) Then
                cell.Value = CDate(cell.Value) ' Convert text to date
            End If
        Next cell
       
        Set colK = wsBet_Data.Range("K2:K" & lastRow)
    For Each cell In colK
        If IsDate(cell.Value) Then
            cell.Value = Int(cell.Value) ' Remove the time portion
            cell.NumberFormat = "dd/mm/yyyy" ' Format as UK date
            End If
    Next cell
           
End Sub

 

[Jockeyp]

I should clarify, the first two steps are occurring in the same formatting script rather than in separate ones.

 

[RoryA]

I have built a spreadsheet that imports data from a CSV file

How does it do that? I suspect that is where the source of the problem lies.

 

[Jockeyp]

How does it do that? I suspect that is where the source of the problem lies.

Thanks Rory,

I can share the script that imports the csv data - but again, it works fine when using it on the mac. All that script is doing is copying and pasting whatever is in the csv file into the intended sheet.

 

[RoryA]

It would probably help to see it, and also to know how and where (on what type of machine) the csv file is created.

 

[Jockeyp]

So here is the script for copying the data from the csv file into my spreadsheet.

Sub Import_Data()
    Dim Start_Cell As String
    Dim Last_Row As Integer 'save the last row no. in the import file
    Dim Last_Col As Integer 'save the last column
    Dim Next_Row As Long 'like an integer variable 'allows for >34k rows
    Dim FilePath As String 'variable to store the file path

' Remove any filters from Bet_Data sheet
    With Sheets("Bet_Data")
        If .AutoFilterMode Then .AutoFilterMode = False
    End With

    With Sheets("Process Manager")
        Start_Cell = .Range("C4").Value 'save start cell to variable
        Last_Col = .Range("C5").Value 'save last column to variable
        FilePath = .Range("C2").Value 'get the file path from cell C2

        Workbooks.Open (FilePath & "/" & .Range("C3").Value) 'open file using the path from C2

        Last_Row = Sheets(1).Range(Start_Cell).End(xlDown).Row 'get the last row
        'select all data in the import file
        Range(Range(Start_Cell), Cells(Last_Row, Last_Col)).Copy

        'paste into file on the next empty row
        ThisWorkbook.Activate
        Sheets("Bet_Data").Select
        Next_Row = Cells(Rows.Count, 1).End(xlUp).Row + 1
        Range("A" & Next_Row).Select
        ActiveSheet.Paste 'copy everything in
    End With

    Application.CutCopyMode = False 'clear clipboard
   
    Call Format
    Call InsertFormulas_Part1
    MsgBox "Data Copied into row " & Next_Row
End Sub

As for where the csv file is coming from - it is a data export from a piece of software that I use on a VPN. Have used it for years and extracted data each month and again when working with the exported files on a Mac, it has never caused any issues. It is only now trying to work it on a PC am I running into issues.

 

[RoryA]

when working with the exported files on a Mac, it has never caused any issues

I appreciate that, but Macs and PCs are different, which is why I am asking these questions. For example, they use different line feeds by default. Also, you might have different regional settings that could cause an issue with converting text.

After your code pastes the data into the workbook, can you write a formula that refers to one of the 'number' cells and multiplies it by 1? (i.e. a simple =A2*1 type formula)

Also, given that you are opening a text file, you might want to use Workbooks.OpenText which allows you to specify a lot of information like the origin, delimiters and field info. Or it could be that using Workbooks.Open but specifying the Local:=True option will fix it. Honestly, without a sample CSV file, it will be hard to say.