Data from another worksheet won't automatically change into desired format (Accounting)

[Grumps]

I'm using Microsoft Excel 2010 on Windows 7 64-bit.
I have a worksheet where I have different products with their prices from 2 different stores. I have 3 worksheets altogether - this front one where I can see the stuff I need, one for one shop, and one for the other shop. For each product I have data from the web, and then on my "front page" (where I see only what I need to), I have referenced the cells with the prices.

So, I run some macros to tidy up these cells - there is nothing left except for a number. No spaces, no non-breaking spaces, nothing. However, the numbers that are referenced on my front page stay as just numbers, and don't automatically go into the Accounting format. This only happens when I go into my data worksheets, find the cells where the prices are, click in the top bar when you can enter the info/data, and press enter.

This is quite annoying as I also have columns for the price difference, best price, and the store which is cheapest. But of course none of these columns work because the numbers won't go into Accounting format.

Is there some sort of macro I could use that could "press enter" on the cells I need it to? I tried making a macro myself of finding the cells, and pressing enter, but then of course the macro saves what the data was at the current time of the macro. So when I update the web sources, and run the macros to clean it up, and the macro to press enter on the cells, it just enters the old data again. Maybe there's a way of getting rid of the specificity of what goes in the cells, and rather just allowing whatever is currently in there?

I have looked everywhere, all day. I can't find anything similar or helpful.

Thanks in advance for any help.

Josh

 

[ChrisM]

In the course of tidying up your numbers, you are making Excel think they are text values rather than numbers. You need to coerce them back to numbers. Hard to say without seeing your code, but you can likely use VAL("yournumber") to fix it.

 

[Grumps]

Okay, thanks for a bit of an explanation. I shall post the code tomorrow (don't have access to it at the moment). I'm new to VBA, so it may be a bit messy and not so efficient, but it's only for personal use so it doesn't bother me too much. However, improvements would be welcome!

 

[Grumps]

Okay so here is the code for the macros. Like I said, probably a bit messy and could probably do with some improvements.
They are run in this order...

1. This is for removing any asterisks and nbsp's:

Sub MFRemoveAsterix()


Application.ScreenUpdating = False


    Sheets("MindFactory").Select


    Dim Cel As Range, rng As Range, i As Long
    Set rng = Range("A1:EU1", Range("A1000:EU1000").End(xlUp))
    For Each Cel In rng
        For i = Len(Cel.Value) To 1 Step -1
            Select Case Mid(Cel.Value, i, 1)
            Case Chr(42) 'this is an * sign
                Cel.Value = Left(Cel.Value, i - 1) & Right(Cel.Value, Len(Cel.Value) - i)
            Case Chr(32), Chr(160) 'these are spaces
                Cel.Value = Left(Cel.Value, i - 1) & Right(Cel.Value, Len(Cel.Value) - i)
            End Select
        Next i
    Next Cel
    Sheets("Compare").Select
    
Call AMARemoveEUR
End Sub

2. This is for removing "Preis: Eur " from the "Amazon" data sheet:

Sub AMARemoveEUR()


Application.ScreenUpdating = False


    Sheets("Amazon").Select


    Dim Cel As Range, rng As Range, i As Long
    Dim Word As String
    Set rng = Range("A1:GC1", Range("A100:GC100").End(xlUp))
    Word = "Preis: EUR "
    For Each Cel In rng
        If Cel Like "*" & Word & "*" Then
            Cel = Replace(Cel, Word, "")
           'To remove the double space that follows ..
        End If
    Next Cel
    
    Call AMARemoveTrailingSpace


End Sub

3. This is for removing trailing spaces in the "Amazon" data sheet:

Sub AMARemoveTrailingSpace()

Application.ScreenUpdating = False
Sheets("Amazon").Select
    
    Sheets("Amazon").UsedRange.Value = Evaluate("=Index(Trim(" & Sheets("Amazon").UsedRange.Address & "),)")


Sheets("Compare").Select


Call MFRemove€
End Sub

4. This is for removing a € symbol from the cells on the "MindFactory" data sheet:

Sub MFRemove€()


Application.ScreenUpdating = False
'
' MFRemove€ Makro
'


'
    Sheets("MindFactory").Select
    Cells.Replace What:="€", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Sheets("Compare").Select
End Sub

And here is what I'm left with on the front sheet:

 

[ChrisM]

Ok, try the VAL thing I mentioned, and if that doesn't work maybe CDBL. Add this to each line that manipulates your prices, like this first one from your mindfactory code:

Cel.Value = Val(Left(Cel.Value, i - 1) & Right(Cel.Value, Len(Cel.Value) - i))
or
Cel.Value = CDBL(Left(Cel.Value, i - 1) & Right(Cel.Value, Len(Cel.Value) - i))

and then Amazon:
Cel = Val(Replace(Cel, Word, ""))

 

[Grumps]

Well, we're half way there! The Amazon prices are correct. However, most of the information from the mindfactory data sheet has now been turned into 0's.


Here are the codes:


The results:


What happens to mindfactory after cleaning:


What it looks like before cleaning:

 

[Grumps]

Well, we're half way there! The Amazon prices are correct. However, most of the information from the mindfactory data sheet has now been turned into 0's.


Here are the codes:

Oops, I cut part of MFRemoveAsterix off:

Case Chr(42) 'this is an * sign
    Cel.Value = Val(Left(Cel.Value, i - 1) & Right(Cel.Value, Len(Cel.Value) - i))
Case Chr(32), Chr(160) 'these are spaces
    Cel.Value = Val(Left(Cel.Value, i - 1) & Right(Cel.Value, Len(Cel.Value) - i))

 

[ChrisM]

I think I would have done the Mindfactory * and spaces as a find/replace like you did the $ signs. is there a reason you chose to loop through all text?

Anyways, try this way:

Sub MFRemoveAsterix()


Application.ScreenUpdating = False


    Sheets("MindFactory").Select


    Dim Cel As Range, rng As Range, i As Long
    Set rng = Range("A1:EU1", Range("A1000:EU1000").End(xlUp))
    For Each Cel In rng
        For i = Len(Cel.Value) To 1 Step -1
            Select Case Mid(Cel.Value, i, 1)
            Case Chr(42) 'this is an * sign
                Cel.Value = Left(Cel.Value, i - 1) & Right(Cel.Value, Len(Cel.Value) - i)
            Case Chr(32), Chr(160) 'these are spaces
                Cel.Value = Left(Cel.Value, i - 1) & Right(Cel.Value, Len(Cel.Value) - i)
            End Select
        Next i
        Cel.Value = Val(Cel.Value)
    Next Cel
    Sheets("Compare").Select
    
Call AMARemoveEUR
End Sub

 

[Grumps]

Hm no reason for looping through the text. I don't know why I didn't think of finding and replacing. I'll try it out later and let you know how it goes. By the way, I really appreciate your time and help. You're awesome.

 

[Grumps]

Sorry for the delay, busy weekend.

I have changed the code a bit, but can't get MindFactory to change into a number. I don't know if/where I can use "VAL" or CDbl"..? The Amazon stuff works fine.