Copy Data from Cell A to B and convert to Number

Question

The below code is copying the data fine, however the copy when then number is copied over is copied as text. Is there any way to copy it over as a number?

    Worksheets(1).Range(destCol1 & LR & destCol2 & LR).cells(3).value = Split(srcRange(5).value)

I've tried recording a macro where I'd highlight the data and click the option "Convert text to number" but it's not recording the VBA for that part.

EDIT 1:

With Worksheets(1).Range(destCol1 & LR & destCol2 & LR)
    .cells(1).value = srcRange(2).text
    .cells(2).value = srcRange(1).value
    .cells(3).value = CDbl(Split(srcRange(5).value)(1))
    .cells(4).value = srcRange(6).value 
End With

Gary's Student · Answer 1 · 2017-10-16T18:27:52.783

0

Untested:

Sub jdskfhs()
    Worksheets(1).Range(destCol1 & LR & destCol2 & LR).Cells(3).Value = CDbl(Split(srcRange(5).Value))
End Sub

EDIT#1:

Here is what you need:

Sub jdskfhs2()
    Worksheets(1).Range(destCol1 & LR & destCol2 & LR).Cells(3).Value = CDbl(Split(srcRange(5).Value)(1))
End Sub

This new code converts the second element of the Split()-generated array to a number.

edited Oct 16 '17 at 18:27

answered Oct 16 '17 at 17:15

Gary's Student

95,722
10
59
99

@Maldred any spaces in the data ? – Gary's Student Oct 16 '17 at 17:26
Yes, that's what the Split() is doing... I've just tried splitting it first, and storing the split string into a temp variable and I'm still getting the same error, but it's a type mismatch error on the Split() function – Maldred Oct 16 '17 at 17:27
Post an example of the data you are trying to put into the cell. – Gary's Student Oct 16 '17 at 17:28
`12000541 DUP-` would be one example, there are approximately 12 spaces in there – Maldred Oct 16 '17 at 18:23
@Maldred See my **EDIT#1** – Gary's Student Oct 16 '17 at 18:28
Still getting the same compile error... I do have this in a With statement, would that make any difference? – Maldred Oct 16 '17 at 18:43

Maldred · Accepted Answer · 2017-10-16T19:08:48.033

After quite a bit of digging, I found another answer on these forums here

I would ideally like to have it all done it one line to reduce processing power as these pages can get quite large; unfortunately this was the best solution I could get.

EDIT:

Using the below code that I modified a bit, but it does work

Private Function TextToNumber(rng As Range)

    LR = ActiveSheet.Range("C" & Rows.Count).End(xlUp).row

    For R = LR To 3 Step -1
        With rng.cells(R, 1)
            .NumberFormat = "General"
            .value = .value
        End With
    Next R

End Function

Copy Data from Cell A to B and convert to Number

2 Answers2