VBA Revision - Value is Text but gets pasted as a date?

[Johnny Thunder]

Hello all,

I have a piece of code that I finally got working but running into a small issue that is hopefully a quick fix.

I have an array type loop that looks at my dashboard and extracts the data and populates another sheet with the values. There are text values within the range of values being extracted that are completely numeric but I have a few cells that are written as "1-3" or "1-8" with a dash in between two numbers. For whatever reason when the code see's these values they look fine since I stepped thru the code to see how they are being seen but when the code pastes to my Data Dump sheet (Ws2) they are being pasted as a date, only the values that have the dash, all other values are coming thru as a text numeric value.

Any ideas on how to modify this code to correct?

'----------------------------------------------------------------------------------------------------------
'--- Extracts Timeline Data and Creates Linear Report
'----------------------------------------------------------------------------------------------------------
Sub Creating_Report()
  
Dim ws1                   As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim a                        As Variant, b As Variant
Dim i                         As Long, j As Long, k As Long, LastR As Long, LastCol As Long, StartPosition As Long, LastR2 As Long
Dim wKstNames     As Variant, Wksht As Variant

Set ws1 = Sheets("Timeline")
Set ws2 = Sheets("Data Dump")
 
 
LastR2 = ws2.Cells.ClearContents
LastR = ws1.Range("C" & Rows.Count).End(3).Row     'last row of column O
LastCol = ws1.Cells(13, Columns.Count).End(1).Column  'last column of row 2
    
                  'Load in memory all data from cell O1 and to the last cell with data
                  a = ws1.Range("A1", ws1.Cells(LastR, LastCol)).Value2
                                
                  'Calculate an output matrix (1 to rows, 1 to columns) - the number of rows will be equal to the number of cells from cell O1 to the last cell - With 4 columns
                  ReDim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 11)
                  
                  For i = 15 To UBound(a, 1)      '15 initial rowto start on
                    
                    If a(i, 1) <> "" Then
                      
                      For j = 10 To UBound(a, 2) 'Column 10 is the start of the dates - Used to be "UBound(a, 1)"
                        If a(i, j) <> "" Then
                          k = k + 1
                          b(k, 1) = a(i, 1) 'Title
                          b(k, 2) = a(i, 2) 'Season
                          b(k, 3) = a(i, 3) 'Genre
                          b(k, 4) = a(i, 4) 'Series Start
                          b(k, 5) = a(i, 5) 'Series End
                          b(k, 6) = a(i, 6) 'Series Validation
                          b(k, 7) = a(i, 7) 'How many Ep on Drop
                          b(k, 8) = a(i, 8) 'Total EP Count
                          b(k, 9) = a(i, 9) 'Run-Time
                          b(k, 10) = a(i, j) 'Amount    <-----------------------Issue line
                          b(k, 11) = a(12, j) 'Date
                        End If
                      Next j
                    End If
                  Next i
              
            'Determines the last row on Sheet Ws2 before pasting in my extracted data
           StartPosition = ws2.Range("A" & Rows.Count).End(xlUp).Row
              
            ws2.Range("A" & StartPosition + 1).Resize(k, 11).Value = b   'Drops in values on Ws2

 

[Norie]

Format the problem destination column as Text before putting the data on the 'Data Dump' sheet.

 

[Johnny Thunder]

Tried that Norie, it will still convert it to a date as the values get dropped there.

 

[Snakehips]

I'm not sure whether the leading " ' " will in any way make it awkward for any future processing you may wish to do with the dumped data but the below should ensure that the 1 - 3 etc renders as text.

b(k, 10) = "'" & a(i, j) 'Amount

Hope that helps.

 

[Johnny Thunder]

That worked great! That is exactly what I was looking for! Thank you!

 

[Snakehips]

That worked great! That is exactly what I was looking for! Thank you!

You are most welcome.