Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    formatting imported data

    I import data from SAP into excel 2007. I then use a macro to insert 2 columns and in the second column the macro enters a formula as follows:

    ws.Cells(k, 4).Value = "=if(" & ws.Cells(k, 3).Address(False, False) & "=""""," & ws.Cells(k, 2).Address(False, False) & "," & ws.Cells(k, 3).Address(False, False) & ")"

    However excel insists on formating the inserted columns C and D as formulas. The only way that I can get excel to change the formating of these columns is to manually sit the cursor on one cell, hit F2 and then hit return, finally I have to copy the recalculated cell to all rows.

    I have tried including various versions of the following line in my macro as a fix:

    ws.Columns("C").NumberFormat = "0"

    None of the above changes the column format to show the cell value rather than the formula.

    Can somebody suggest how I can force the columns to show values rather than formulas

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Does it help if you format the columns BEFORE you add the formulas. They appear to formatted as TEXT when created, suggesting that Col B may be formatted as text (New Cols inserted to the right of B would take the formatting of B)

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    recalculate with vba

    Hi

    Investigate the use of range.calculate.

    example: selection.calculate

    Hope it helps

    Geof

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FYI, you can replace this:
    Code:
    ws.Cells(k, 4).Value = "=if(" & ws.Cells(k, 3).Address(False, False)  & "=""""," & ws.Cells(k, 2).Address(False, False) & ","  & ws.Cells(k, 3).Address(False, False) & ")"
    with this:
    Code:
    ws.Cells(k, 4).FormulaR1C1 = "=if(RC3="""",RC2,RC3)"
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks - formatting before inserting the formula worked

    @ Rory - the formula you provided was useful but has $ signs in front of the column references. Is there a variant that inserts formulas without the absolute column references?

    Alex

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Yes, I just couldn't see why it mattered as your column number was fixed:
    Code:
    ws.Cells(k, 4).FormulaR1C1 = "=if(RC[-1]="""",RC[-2],RC[-1])"
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •