Excel VBA - Paste a variable's value into a cell

Question

I have a For loop which will find a cell with a certain value in it (e.g. the word "Item"). Once it finds this cell, I want to paste a value into the row below it, in column I.

For example, if the For loop finds "Item" in cell A1, I want to paste the value of a variable into cell I2.

I'd like to be able to do this with both a Long variable and a String variable.

Right now I'm trying something like:

Cells(i.Offset(1, 0), 9).Value = myLongVariable

or

Cells(i.Offset(1, 0), 9).Text = myStringVariable

and other variations, but I keep getting an overflow error at this line.

I've tried this: Range("I" & i.Offset(1, 0)).Value = myLongVariable and slight variations, but I get an error that says Run-time error 1004: Method Range of object _Worksheet failed

Or workBook.Sheets("Sheet1").Range("I" & i.Offset(1, 0)).Value = myLongVariable but that gives Run-time error 1004: Application-defined or object-defined error. Not sure what that refers to since I've defined workBook and i and myLongVariable.

As a slightly different approach, I've tried pasting the variable data into the next blank cell of the row, which happens to be the cell in column I. Here's the code I used for that: 'i.Offset(1, 0).End(xlToRight).Offset(0, 1).Value = myLongVariable. It works great, except in this scenario: cells A1:C1 are blank, D1:H1 have values. It should then paste the value into I1, but instead it thinks D1 is the end and posts it there. Any ideas on that?

Thanks!

Dude, please proof read your question then leave it for people to answer, especially don't change the code you're asking about! — arcadeprecinct
– arcadeprecinct, Commented Jun 25, 2016 at 18:28
your Cells(i.Offset(1, 0), 9) needs to be Cells(i.Row + 1, 9)... also strings can be set via .Value... the .Text property of cells act in a different way... pls read THIS — Dirk Reichel
– Dirk Reichel, Commented Jun 25, 2016 at 18:28
@DirkReichel Please post that as an answer so I can accept it — pez
– pez, Commented Jun 25, 2016 at 18:30

Dirk Reichel · Accepted Answer · 2016-06-25 18:36:07Z

1

Just to be different from the other answer you also can use this:

i.Offset(1).EntireRow.Cells(9).Value = myLongVariable
i.Offset(1).EntireRow.Cells(9).Value = myStringVariable

Or to ensure to use the correct column:

i.Offset(1).EntireRow.Columns("I").Value = myWhateverVariable

still, like said in my comment Cells(i.Row + 1, 9) also is possible or Cells(i.Row + 1, "I") ;)

answered Jun 25, 2016 at 18:36

Dirk Reichel

7,9991 gold badge18 silver badges31 bronze badges

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

Comments

Steven Martin · Accepted Answer · 2016-06-25 18:30:49Z

1

Its wrong to use offset here, because you are not referencing a cell,

i is just a number, so you cant offset it

Cells(i.Offset(1, 0), 9).Value = myLongVariable
Cells(i.Offset(1, 0), 9).Text = myStringVariable

should be

Cells(i + 1, 9).Value = myLongVariable
Cells(i + 1, 9).Value = myStringVariable

answered Jun 25, 2016 at 18:30

Steven Martin

3,3201 gold badge23 silver badges29 bronze badges

Comments

arcadeprecinct · Accepted Answer · 2016-06-25 18:47:32Z

This assumes that i is a range because you had that in another question: i.Offset(1,0) returns a Range object, not a number. The way you use it in your code looks like you are trying to use it as the number of a row. To get the Row of a range, just use the .Row property:

i.Offset(1,0).Row
i.Row + 1 'same thing

Then

Cells(i.Offset(1, 0), 9).Value = myLongVariable

should be

Cells(i.Row+1, 9).Value = myLongVariable

Next thing:

 Cells(i.Offset(1, 0), 9).Text = myStringVariable

This is the same problem with Offset but also the .Text property. The .Text property is read only and returns the string that is displayed on screen. It differs from the value for example if the cell is too small, it becomes #####. Use the .Value property for strings as well as numbers.

Now for the last thing:

Somecell.End(xlToRight)

End(xlToRight) looks for the end of the region, which means that if the cell is empty, it looks for the end of the empty region, which is the next cell that contains something or the end of the sheet. If the cell is not empty, it looks for the first empty cell. What you want is to search from the right for the first non-empty cell:

Cells(Somecell.Row,Columns.Count).End(xlToLeft) 'this is the last nonempty cell in the Row of Somecell

you have to move one column to the right to get the first empty cell, for example using Offset.

Collectives™ on Stack Overflow

Excel VBA - Paste a variable's value into a cell

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related