Multiply Array Value to Convert Text to Number

[jmpmolegraaf]

In my excel file I have Numbers that are stored as Text and by multiplying the value by 1 it is normally solved (i.e. the cell value becomes a number).

If I do the following simple test it works:
Range("A1") = Range("A1") * 1

Now I have a big file and I want to use an Array to do so but it doesn't work. This is my code:

---

Dim Arr As Variant

Arr = Range(Cells(2, 6), Cells(lastrowraw, 10)).Value

Dim R As Integer
Dim C As Integer

For R = LBound(Arr, 1) To UBound(Arr, 1)
For C = LBound(Arr, 2) To UBound(Arr, 2)

Arr(R, C) = Arr(R, C) * 1



Next C
Next R

---

The Array is correctly assigned and the value are correct if I follow the macro's progress. FYI, lastrowraw is already declared before and is also correct.

However, in my cells I see no conversion to numbers while the simple test works.

What is wrong here?

 

[pgc01]

However, in my cells I see no conversion to numbers while the simple test works.

What is wrong here?

??

You did not post where you write the values to the cells, after the array has already the right values.

It should be something like:

Range(Cells(2, 6), Cells(lastrowraw, 10)).Value = Arr

 

[pgc01]

You can also try this alternative to your code:

With Range(Cells(2, 6), Cells(15, 10))
    .Value = .Value
End With

Remark: I'm using a small value for lastrowraw for testing purposes

 

[jmpmolegraaf]

Thanks for your reply pgc01.

Can't I use the R and C values for writing the value in the appropriate cell? because R and C determine the location.

Not possible to do like this?
Arr(R, C).Value = Arr(R, C) * 0.1

 

[pgc01]

Hi

First of all. Did you try the code I posted? Did it work?

Can't I use the R and C values for writing the value in the appropriate cell? because R and C determine the location.

Not possible to do like this?
Arr(R, C).Value = Arr(R, C) * 0.1

Your code is working fine. You are writing the values in the array and if you check after the code has executed, you'll see that it worked OK.

What you are not doing anywhere (that I can see) is writing values in cells.

You can write the values in the cells one by one, but in that case why defining the array?

To write the values in the cells, looping through the cells (much less efficient than the 2 previous solutions), these are 2 options:

1 - using a logic similar to yours:

Sub Test2()
Dim r As Range
Dim lRow As Long
Dim lCol As Long
Dim lastrowraw As Long

lastrowraw = 15
Set r = Range(Cells(2, 6), Cells(lastrowraw, 10))

For lRow = 1 To r.Rows.Count
    For lCol = 1 To r.Columns.Count

        r(lRow, lCol).Value = r(lRow, lCol) * 1

    Next lCol
Next lRow

End Sub

2 - a bit simpler:

Sub Test1()
Dim r As Range
Dim lastrowraw As Long

lastrowraw = 15

For Each r In Range(Cells(2, 6), Cells(lastrowraw, 10))

    r.Value = r.Value * 1

Next r

End Sub

Remark: As I said they are less efficient than the others I posted previously, as they write the cells 1 by 1.

 

[jmpmolegraaf]

Thanks! Based on your feedback I noticed indeed that the value were not written so it works now and the code looks like this:

Dim Arr As Variant
Dim R As Integer
Dim C As Integer
Dim ArrDest As Range

Set ArrDest = Range(Cells(2, 5), Cells(lastrowraw, 10))
Arr = ArrDest.Value

For R = LBound(Arr, 1) To UBound(Arr, 1)
For C = LBound(Arr, 2) To UBound(Arr, 2)

Arr(R, C) = Arr(R, C) * 1
ArrDest.Value = Arr

Next C
Next R

But I would rather not have fixed cell locations (even though it works fine for my sheet now).

I wanted indeed an array because i thought that would be best solution, i see your option 2 is much easier.

Thanks a lot!

 

[pgc01]

You're welcome. Thanks for the feedback.

 

[jmpmolegraaf]

I actually got rid of the array and took your option 1 - much easier to work with.

 

[pgc01]

I agree. The array was not adding anything.

The array option is good if have many cells. You can read all the range into the array, then process the data of the array (much quicker that using worksheet read/write operations) an then, after all the array values are processed, do a bulk write operation.
This is very efficient as you only have 2 worksheet read/write operations, one at the beginning, when you read all the data into the array, and one at the end, when you write all the array to the range.

If you have few cells, looping through the range and write the cells one by one is an easy solution.

It's important, however, to explore all the options, so that in the future you can choose the best one for each case.