How do I use excel data types properly in my data entry form?-CodePudding

Summary of the Problem

I am attempting to create a database for stocks that I value and have hit a snag. I am attempting to create a robust data entry sheet that makes the process easier, I am doing so by utilizing VBA. I am trying to use the "stocks" datatype function to copy over the stock ticker into the database using the following line:

.Cells(iRow, 3).Value = frm.Range("F5").Value

However when it gets copied over to the database it spits back the #VALUE! error.

What I have Tried

I attempted to specify the RangeValueDataType but that didn't seem to be what I was looking for. I also tried changing the Range syntax to .Value2 along with .ConvertToLinkedDataType, the ladder changed the end output in the database from #VALUE! to "TRUE".

Additional Code

This is the code from the beginning of the sub that may shed some light on what is happening:


    Dim frm As Worksheet
    Dim database As Worksheet
    
    Dim iRow As Long
    Dim iSerial As Long
    
    Set frm = ThisWorkbook.Sheets("input")
    
    Set database = ThisWorkbook.Sheets("database")
    
    If Trim(frm.Range("L1").Value) = "" Then
        
        iRow = database.Range("B" & Application.Rows.Count).End(xlUp).Row   1
        
        If iRow = 2 Then
        
            iSerial = 1
        
        Else
        
            iSerial = database.Cells(iRow - 1, 2).Value   1
        
        End If
        
    Else
    
        iRow = frm.Range("K1").Value
        iSerial = frm.Range("L1").Value
        
    End If

My understanding of VBA is super rudimentary so I am sure it is something simple I just don't understand about the functions I am utilizing. Any help whatsoever is greatly appreciated, Thanks!

CodePudding user response：

When you set a range of cells to the Stocks datatype, it's actually not the traditional datatype existed in Excel but Excel convert the selected range to a new object class, LinkedDataType 268435456.

The method you are trying to use

Target_Range = Source_Range

will not convert the destination range to the new object type. It will neither copy over the extended data behind the object nor the plain text company name or stock symbol you entered. Therefore, you will not have the proper data sitting in the destination range for you to reapply ConvertToLinkedDataType

The only two viable methods you can use are either something like the following,

frm.Range("F5").copy
Database.Cells(iRow, 3).select
ActiveSheet.Paste

which will convert the destination range to the same object type, or capture the company name / stock symbol, during entry, into a variable, and paste the plain text into the destination range then reapply ConvertToLinkedDataType.

If I were you, I would pick the Copy and Paste.

CodePudding user response：

Thanks to Harry Lee I figured it out. The I ended up having to use the copy function as he recommend and ended up with the following:

    frm.Range("F5").Copy
    database.Select
    .Cells(iRow, 3).Select
    database.Paste