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, Jun 25 '16 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](http://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2) — Dirk Reichel, Jun 25 '16 at 18:28
@DirkReichel Please post that as an answer so I can accept it — pez, Jun 25 '16 at 18:30

score 1 · Answer 1 · answered Jun 25 '16 at 18:30

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

score 1 · Accepted Answer · answered Jun 25 '16 at 18:36

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") ;)

arcadeprecinct · Answer 3 · 2016-06-25T18:47:32.010

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.

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

3 Answers3