How To Assign A Range to a String in VBA Excel

Question

I have a column with dates in a table called Transactions in Excel. I have a column called transactions and I would like to iterate through the descriptions and assign them to a string. My test code is as below and I am getting a error 1004.

For i = 1 To Range("Transactions").Rows.Count
    ' Set group to the value returned from the DEscription Table
    strGroup = Range("Transactions[Description]")(i)
Next i

I need help i tried adding .Value at the end and it did not help anything

I want to basically search every cell in a particular column for specific words and if they match. Copy the text to a new column. So that's why I want to get every description as a string and then use it as a parameter in the InStr function

you can't have a range assigned to a string. You can assign a value from a range to a string. — Luuklag
– Luuklag, Commented Aug 21, 2017 at 14:42
A String is one value. A Range is one object representing one or more cells in a worksheet; Range has a Value property, but if the range is for more than one cell then it gets you an array of values, which you can't legally assign to a String. By the way, iterating cells the way you're doing here, is the single slowest way to go about it. And even if the assignment was legal, you're overwriting it with every new iteration. — Mathieu Guindon
– Mathieu Guindon, Commented Aug 21, 2017 at 14:56
Ok I actually want to overwrite the value every time. But then I tried using the list object way but I wasn't getting what I wanted. What do you suggest — the-bax
– the-bax, Commented Aug 21, 2017 at 15:08
You definitely don't want to overwrite the value every time; otherwise when the loop exits, strGroup will only ever contain whatever the last iteration assigned it to, which defeats the entire point of looping in the first place (just read the last value if what you want is the last value). I'd love to help, but I've no idea what you're asking / trying to do. — Mathieu Guindon
– Mathieu Guindon, Commented Aug 21, 2017 at 15:30

Nathan_Sav · Accepted Answer · 2017-08-21 15:13:04Z

2

maybe join(application.transpose(range("a1:a100").value)," ") not sure how it will perform at various sizes, but 100 rows was ok.

edited Aug 21, 2017 at 15:13

answered Aug 21, 2017 at 14:44

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

the-bax Over a year ago

Thanks I will try it.

Nathan_Sav Over a year ago

What happened/were you expecting?

the-bax Over a year ago

I want to basically search every cell in a particular column for specific words and if they match. Copy the text to a new column. So that's why I want to get every description as a string and then use it as a parameter in the InStr function

Nathan_Sav Over a year ago

You should add that bit to your question.

Collectives™ on Stack Overflow

How To Assign A Range to a String in VBA Excel

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related