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

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
673
Office Version
  1. 2016
Platform
  1. MacOS
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?

VBA Code:
'----------------------------------------------------------------------------------------------------------
'--- 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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,919
Office Version
  1. 365
Platform
  1. Windows
Format the problem destination column as Text before putting the data on the 'Data Dump' sheet.
 

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
673
Office Version
  1. 2016
Platform
  1. MacOS
Tried that Norie, it will still convert it to a date as the values get dropped there.
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Apr 9, 2010
Messages
673
Office Version
  1. 2016
Platform
  1. MacOS
That worked great! That is exactly what I was looking for! Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,137
Messages
5,546,147
Members
410,732
Latest member
tkouti1
Top