VBA remove " from cell content in a range

Question

I am new to vba coding (and coding in general) but I have started doing a small macro that transfers values of a csv file into an excel file.

My next step is to remove the quotation marks in each cell. I have tried these lines of codes in my sub:

Dim Table As Range
Dim Cell As Variant

Set Table = Range("A1:currentregion")

For Each Cell In Table        
    cell.value2 = Replace(cell.value2,""","")   *I get a syntax error here*        
Next Cell

I know this is very basic but I can't find a solution on the net or using the macro recorder. If anybody could tell me what I am doing wrong and also what code I could use to change the value in a cell that contains a string of numbers into a numeric value?

Thanks!

Answer 1

Cell.Value2 = Replace(Cell.Value2, Chr(34), "")
There always is a workaround, this one is referencing via character code.

Answer 2

You don't need to loop through each cell. The Range("A1").CurrentRegion may be operated on as a whole.

With Range("A1").CurrentRegion
    .Replace What:="""", Replacement:=vbNullString, LookAt:=xlPart
End With

Note that to look for a single quote (e.g. ") you need 4 quotes in a row. An alternative is Chr(34) as a quote symbol is the 34th ASCII character.

Addendum:

With regard to the second portion of your question, changing text that looks like a number to actual numbers is best done with a quick Text to Columns ► Fixed Width command. Each column would have to be run through individually but this can be accomplished within the .CurrentRegion.

Dim c As Long
With Range("A1").CurrentRegion
    .Replace What:="""", Replacement:=vbNullString, LookAt:=xlPart
    For c = 1 To .Columns.Count
        .Columns(c).NumberFormat = "General"
        .Columns(c).TextToColumns Destination:=.Columns(c), _
            DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
    Next c
End With

There is the question of what number format the columns were in to begin with. If they were Text (e.g. '.NumberFormat = "@"` then removing the quotes is not going to convert them to true numbers. I've added a reversion to the General number format to accommodate this. If you have existing number formats that you wish to keep, there are more precise ways of reverting the number formats of some of the cells.

Answer 3

You should dim Cell as Range. Also it's not a very good idea to name a variable using a VBA syntax element. Use MyTable and MyCell instead.

And to set the range better use

Set MyTable = [A1]

For Each MyCell In MyTable.CurrentRegion

...