Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Feb 2009
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have an export from a database in the form of a *.csv file that I am comparing to an excel spreadsheet. It used to work fine with for - next loops stepping through the various values & flagging differences. Recently the application that generates the *.csv values was updated and now instead of cells containing "Some Value", I get cells with formulas of "=Some Value". I am told this cannot be changed. My work-around was to select all cells and do a copy / paste special: values and this clears all the =" ", leaving just the value, but I believe numeric values are still "text" (remain left justified) and when the comparisons are made from the spreadsheet's numeric values against these, the *.csv cell value is "Value" is seen as different (ie. 12345 VS "12345"). I tried to programatically format to a numeric (Selection.NumberFormat = "0"), but no luck. The weird thing is that if I click into the cell and then accept (check) with no changes the cell converts to numeric and all is well with the macro after that. This is also true if I click on the "green formatting issue" triangle and select convert to number, but how to do this programatically? Use the value Command? How?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    You could try this piece of code, highlight the cells you want formatted

    [codebox]Sub StringToInteger()
    Dim y As Integer
    Dim x As Range
    Dim z As Range
    Set z = Selection
    y = 1
    Set x = Range("A65536").End(xlUp).Offset(1)
    If x <> "" Then
    Exit Sub
    Else: x.Value = y
    x.Copy
    z.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
    Application.CutCopyMode = False
    End If
    x.ClearContents 'Back to normal

    z.Copy
    z.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    End Sub[/codebox]
    Jerry

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Or slightly simpler

    Code:
    Sub StringToInteger()
      With Selection
    	.NumberFormat = "General"
    	.Value = .Value
      End With
    End Sub

  4. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    [quote name='JHWilkinson' post='780681' date='19-Jun-2009 15:11']This is also true if I click on the "green formatting issue" triangle and select convert to number, but how to do this programatically? Use the value Command? How?[/quote]

    NON VBA

    When I get data from a source that has values a text, my fast conversion is to find an empty column and put in the formula = A1 * 1 and copy down. A1 in the formula is the location of the cell with the text value.

    In most cases this should convert your text values to actual numbert. If you don't want the formulas just select the entire range with the forumulas and do a copy paste special (select values) and copy to the same location, that will change all the formulas to valules.

    Regards,

    Tom Duthie

  5. #5
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You might also try TextToColumns, setting format General in the last dialog screen, to do a quick conversion from text to number.

  6. #6
    New Lounger
    Join Date
    Feb 2009
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    All,

    Thanks much for the imput, it was helpful. In the end, because I was only comparing and not saving one of the two files, I handled the issue within the VBA code by using the "Format" command. To compare the "text" values from the "disposable" workbook with the original workbook's number values, I used "If Cells(CSV_Row, 10).Value = Format(PN) Then" (the default output of Format is text). In one case, the string had a decimal, while numerical value did not. Here I concatenated the missing ".0" as it was always a whole number (ea.) Ex: "If Cells(CSV_Row, 2).Value <> Format(Qty) + ".0" Then". I originally tried the Str command, but it adds a space prefix to hold the number's sign so an extra complication.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    As an alternative to Str you could use CStr; this does not add a leading space.

Posting Permissions

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