CODEWITHSUNDEEP
arraysvbaexcel
Matus Uhrin
Matus Uhrin

Reputation: 93

VBA Array Return the Number not the String

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

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

I hope that it is clear to understand the problem.

Upvotes: 0

Views: 497

Answers (2)

Matus Uhrin
Matus Uhrin

Reputation: 93

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:

2013/00176
2012/08233
2012/08016
2012/08330
2012/08749
2012/01122

Upvotes: 1

Charles Williams
Charles Williams

Reputation: 23520

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

Upvotes: 2

Related Questions