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

Question

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!

Harry Lee · Accepted Answer · 2021-10-15T01:12:25.643

0

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.

edited Oct 15 '21 at 01:12

answered Oct 15 '21 at 00:56

Harry Lee

150
9

Thank you for taking the time to help, I am going to use the copy method you posted. But I am hitting a Run-Time Error with the ".select" method. The error specifically says "Select method of Range class failed" in reference to the "Database.Cells(iRow, 3).select" line. I tried to be even more specific in referencing the sheet itself (database) but that didn't seem to work. – Cynical Oct 15 '21 at 03:45
Hey Harry I managed to figure it out thanks to your help, thanks again. I wish I could upvote you but it wont let me. – Cynical Oct 15 '21 at 04:44
Oops! My bad. I forgot to have the line Database.Activate the copy and select lines. Excel doesn't like it when the sheet Database is not active. – Harry Lee Oct 15 '21 at 20:06

score 0 · Answer 2 · answered Oct 15 '21 at 04:45

0

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

answered Oct 15 '21 at 04:45

Cynical

3
1

1

[How to avoid using select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). `frm.Range("F5").Copy Destination:=database.Cells(iRow, 3)` – Darren Bartrup-Cook Oct 15 '21 at 07:45

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

2 Answers2