CODEWITHSUNDEEP
excelvba
Nicholas Humphrey
Nicholas Humphrey

Reputation: 1250

VBA use right() on range

I'm trying to put the last 4 characters of a table column into an array and dump to column L. I'm using array because it's faster:

Dim arrCard() As String
.Range("TableExport[CARD]").NumberFormat = "@"
arrCard = Right(.Range("TableExport[CARD]").Text, 4)
.Range("L2:L10000") = arrCard

Then it says

type mismatch

which doesn't make sens, as CARD column is already text. Does that mean that I cannot use Right() on a range? But I do see a lot of examples use this method (except they do not apply on table columns)

I also tried:

Dim arrCard() As Variant
Dim cardnum As Integer
.Range("TableExport[CARD]").NumberFormat = "@"
arrCard = Right(.Range("TableExport[CARD]").Text, 4)
.Range("L2:L10000") = arrCard

Then it complains about Type Mismatch on the 4th line. TBH I don't get it, it doesn't seem to be a hell amount of requirement.

Upvotes: 1

Views: 1337

Answers (1)

jonyfries
jonyfries

Reputation: 854

You can't use RIGHT on a range and need to use it on the specific elements. Also, because you're setting the array equal to a range it will be a two dimensional array which is why arrCard(cardnum) wouldn't work and you have to use arrCard(cardnum,1).

Dim arrCard As Variant
Dim cardnum As Long

With ThisWorkbook.Sheets(1)
    .Range("CARD").NumberFormat = "@"
    arrCard = .Range("CARD").Value
    For cardnum = LBound(arrCard, 1) To UBound(arrCard, 1)
        arrCard(cardnum, 1) = Right(arrCard(cardnum, 1), 4)
    Next cardnum
    .Range("L2:L10000") = arrCard
End With

Upvotes: 4

Related Questions