Excel VBA Cell will not activate

Question

When I run my code it says I am using the correct cell (through my variables), but it does not activate the cell or input my variable value into the cell.

myColumnNumber = 1
Range(myColumnNumber & currentRow1).Activate

Range(myColumnNumber & currentRow1) = FirstName & LastName

myColumnNumber = myColumnNumber + 1
Range(myColumnNumber & currentRow1) = AccNumber
myColumnNumber = myColumnNumber + 1
Range(myColumnNumber & currentRow1) = AccName

My variables all show the correct values when running, but the sheet does not change. For example, if I put Range("a1").Activate for the Active Sheet I can see when that cell activates, but my variables do not affect the change in position.

MyColumnNumner steps through adding "1" and my Current row stays at 2, as it should, but on the sheet there is no change. Can you see any errors in this snip, or is more information required? thank you for any assistance.

try `.Select` in your case but do not use `.Select` in general — , Sep 19 '13 at 17:43
Your syntax is off. Try `Cells(currentRow1, myColumnNumber).Activate` — Tim Williams, Sep 19 '13 at 17:56
Why do you want to activate on the first place. You could just put the value. An alternative way to put value in cell is Cells(currentRow1, myColumnNumber).Value=FirstName & LastName I prefer this over the other one. — Foreever, Sep 19 '13 at 18:00
Or the whole thing reduces to `Cells(currentRow1, myColumnNumber).resize(1,3).value = Array(FirstName & LastName , AccNumber, AccName)` — Tim Williams, Sep 19 '13 at 18:01
@TimWilliams I did not see your comment before I posted my answer. +1 on your comment for beating me to it by 1 minute :) — tigeravatar, Sep 19 '13 at 18:08
This worked, thank you very much for the help!! another person added the same response below, thank YOU as well. — user2796515, Sep 19 '13 at 18:10
`.Activate`? Nooooooooooooo! :) You might want to see [this](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Siddharth Rout, Sep 19 '13 at 18:14
You cannot give an integer value to a column name in range. You should put myColumnNumber = "A" rather than 1. And to enter values to adjacent cells ,use offset. Its damn easy to use Cells instead of Range. — Foreever, Sep 19 '13 at 18:14

score 1 · Accepted Answer · answered Sep 19 '13 at 18:02

Generally speaking, never use .Activate or .Select if you can avoid it. Your code can be reproduced as a one-liner:

Cells(currentRow1, myColumnNumber).Resize(, 3).Value = Array(FirstName & LastName, AccNumber, AccName)

As a side note, you are trying to use Range("Address") but are giving it a number as the column, so use Cells instead of Range (as shown in my provided code).

What I mean by that is, the Range method takes column letters, like Range("A1") or Range("C3"). You are giving it numbers, so it looks like this Range("11") which isn't a valid range. I'm kind of surprised you're not getting an error.

The Cells method, however, takes column numbers or column letters. Cells(rownumber, columnnumber) which is how you should be referencing the appropriate cell, based on the provided code.

score 0 · Answer 2 · answered Sep 19 '13 at 17:42

0

Range(myColumnNumber & currentRow1) should reference the range in your workbook.

try adding a range object instead of using Range to reference.

dim rng as Range
set rng = Activeworksheet.Cells

myColumnNumber = 1 
rng (myColumnNumber & currentRow1).Activate
rng (myColumnNumber & currentRow1) = FirstName & LastName

myColumnNumber = myColumnNumber + 1
rng (myColumnNumber & currentRow1) = AccNumber
myColumnNumber = myColumnNumber + 1
rng (myColumnNumber & currentRow1) = AccName

answered Sep 19 '13 at 17:42

Sorceri

7,870
1
29
38

sorry, I didnt see this, the rng before (myColumnNumber & currentRow1) = FirstName & LastName – user2796515 Sep 19 '13 at 17:50
Same result. Only cell A1 stays highlighted and none of my values are added. – user2796515 Sep 19 '13 at 17:53

Foreever · Answer 3 · 2013-09-19T18:19:49.953

0

myColumnNumber = "A"
Range(myColumnNumber & currentRow1).Activate 'No Use

Range(myColumnNumber & currentRow1).Value = FirstName & LastName

' myColumnNumber = myColumnNumber + 1   Error
'The Offset property is the one that you will use the most with Range to move around
'the 'sheet.                  

Range(myColumnNumber & currentRow1).Offset(0,1).Value = AccNumber
Range(myColumnNumber & currentRow1).Offset(0,2).Value = AccName

edited Sep 19 '13 at 18:19

answered Sep 19 '13 at 17:58

Foreever

7,099
8
53
55

This still does not work. The correct cell is not being selected and the values are not being entered. I looked at the MSDN as well and added the sheetname for each line and that did not work either. – user2796515 Sep 19 '13 at 18:03

Excel VBA Cell will not activate

3 Answers3