VBA Array Return the Number not the String

Question

I have created an array as range of cells that contains numbers and text. One column is called PolicyNumber. There are values as:

11/2007
8/2006
12/2005
etc.

If I want to copy the cells from the array back to the sheet it returns not values 11/2007, 8/2006, 12/2005, etc, however it returns the result of calculation

11/2007 = 0.00548
8/2006 = 0.003988
12/2005 = 0.00598
etc.

I have tried

Formatted the values PolicyNumberas text before created the array;
Split the PolicyNumber onto two cells and than put them back;
When exported the values from the array used function format, cstr.

I hope that it is clear to understand the problem.

How are you pasting the values into the worksheet? You need to show your code — user2140173
– user2140173, Commented Sep 26, 2014 at 11:43

Charles Williams · Accepted Answer · 2014-09-26 13:05:48Z

2

Assuming that the policynumber column is formatted as text, then formatting the output as text works for me:

Sub getit()
    Dim var As Variant
    var = Range("a1:A2")
    Range("c1:c2").NumberFormat = "@"
    Range("c1:c2") = var
End Sub

answered Sep 26, 2014 at 13:05

Charles Williams

23.6k5 gold badges40 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gofr1 · Accepted Answer · 2016-04-18 08:23:36Z

1

Sub firstcheck()

Dim X()
dim ind as Worksheet, FC as Worksheet
dim N as Double 

Set ind = Sheets("Input Data")

Set FC - Sheets("First Check")
N = Application.CountA(Ind.Range("G:G"))

X = ind.Range("C2:T" & N).Value 'data
....
FC.Range("B2:S" & N) = X

End Sub

Column :

Policynumber:

Data that works ok :

99-155547/003
99-155690/003
99-155837/003
99-173341/003
99-185326/003

Copied some data that does not work:

edited Apr 18, 2016 at 8:23

gofr1

16k11 gold badges47 silver badges56 bronze badges

answered Sep 26, 2014 at 11:58

Matus Uhrin

1031 silver badge5 bronze badges

Collectives™ on Stack Overflow

VBA Array Return the Number not the String

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related