VBA lookup/Transpose function

[MassiveJim]

I have been using the following code for some time now, and of all the code that I use this has always been problematic.
I often get a type 13 mismatch error and my macro fails to run properly.

        Dim rngFound As Range: Set rngFound = Columns("A").Find(WSO.Range("L2").Value)    If rngFound Is Nothing Then
    MsgBox "HW ID""" & WSO.Range("L2").Value & """ was not found in the DB. ", , "Data not found"
    
    GoTo ErrHandler
    Else
    
    
    Cells(rngFound.Row, "B").Resize(1, WorksheetFunction.CountA(WSO.Range("L2:L21")) - 1).Value = _
    WorksheetFunction.Transpose(WSO.Range("L3:L21", WSO.Cells(Rows.Count, "L").End(xlUp)).Value)
    End If

The issue for me is I am not super competent with VBA, I get by using the excel record function and googling the gaps where I need code that the recorder can't handle.
I don't understand this bit of code or how it exactly works to be able to modify/change it, or better yet find a different way of handling the function I need.


The code is looking at the value in cell L2 in sheet1 and finding it in another spreadsheet, then copying a range of data from sheet 1 (all arranged in 1 column, but not an entire column) and transposing it to the row in sheet 2 where it found the value it looked for.

95% of the time it works, but I have just found out that it will fail if any of the cells contain over 255 characters (which they occasionally might), it also sometimes fails without the 255 character limit for some reason. is there a better way of executing this function ?

 

[Yongle]

Welcome to the forum

Q1. Which cell values sometimes exceed 255 characters? Is it L2 or column A values
Q2. Why do they exceed 255 characters?
- just wondering if anything can be done to reduce their legth
Q3. Is L2 a single word, a phrase, alphanumeric?

 

[MassiveJim]

Q1. One of the values in column L could exceed 255.
Q2. They exceed 255 because that cell is being used to to enter a description (in this particular case to describe any damage noted on a device)
Q3. L2 is numeric on this sheet, but I have a similar usage case on another sheet, where L2 would be alphanumeric.

 

[Yongle]

Thanks. Will update thread with revised code when back at PC tomorrow
Is it possible for L2 to exceed 255 characters?

 

[MassiveJim]

Hi, no L2 is a short string.
On one sheet it is 3/ 4 numbers. On another it is a 10 digit alpha numeric string.

Cheers
James

 

[Yongle]

I was unable to recreate your error and I tested with cells containing 32,000 characters
- perhaps you are on an earlier version of Excel (mine is the latest)
- 255 is a constraint for a number of things, but it is not obvious to me which constraint is breached within the snippet of code you provided

Another way to achieve the same end result should eliminate your problem
Instead of attributing transposed values ....

.... copy & paste transposed values
by replacing:

    Cells(rngFound.Row, "B").Resize(1, WorksheetFunction.CountA(WSO.Range("L2:L21")) - 1).Value = _
    WorksheetFunction.Transpose(WSO.Range("L3:L21", WSO.Cells(Rows.Count, "L").End(xlUp)).Value)

with:

    WSO.Range("L3:L21").Copy
    Cells(rngFound.Row, "B").PasteSpecial Paste:=xlPasteValues, Transpose:=True

Let me know how you get along

 

[MassiveJim]

Computer I was using at the time was running 2007. I should get chance to test this later. I'll try it on 2016 as well and let you know how I get on. Thanks for your help

 

[MassiveJim]

New code works just fine and gets around the problem with 2007.

Thanks for your help