What happened to the Messenger Plus! forums on msghelp.net?
Shoutbox » MsgHelp Archive » Skype & Technology » Tech Talk » Active Cell in VBA

Active Cell in VBA
Author: Message:
CookieRevised
Elite Member
*****

Avatar

Posts: 15519
Reputation: 173
– / Male / Flag
Joined: Jul 2003
Status: Away
RE: Active Cell in VBA
There are many possebilities.

Ranging from the use of the objects: Range(), Cells(), Selection(), Offset(), etc...

The principle is to keep your locations in variables (and update them) and use those variables as your locations in those objects.

I can't give a strait forward solution, as there are so many possebilities to do this...

You should already know that:
* to set a numerical value 123 inside the cell A1 on a worksheet called "mysheet":
    Worksheets("mysheet").Range("A1").Value = 123
* to set a numerical value 123 inside the range A1:H8 on a worksheet called "mysheet":
    Worksheets("mysheet").Range("A1:H8").Value = 123
(this is basic VBA/Excel knowledge you should know before you start programming anything)

From here it is easy to make the location "A1" as a variable...
    Dim mylocation As String
    mylocation = "A1"
    Worksheets("mysheet").Range(mylocation).Value = 123
    mylocation = "A2"
    Worksheets("mysheet").Range(mylocation).Value = 123
    mylocation = "A3"
    Worksheets("mysheet").Range(mylocation).Value = 123

But to change the location you need some things todo and to interpret the alphanumerical data and all. In your case, it is easier to define an offset from the topleft of the location and count upwards from there:

In your global variable space you define an array which will hold all the current locations. You don't need to use an array but the use of an arrray depends on how and what you want to do. And since I have to do with the info you gave me I assume you want to hold all the locations globally and independantly:
    Dim CellLocation(1 to 6) As Integer

In the click procedure of button 1 you need to add something like:
    Worksheets("mysheet").Range("D11").Offset(0, CellLocation(1)).Value = mynumber
    CellLocation(1) = CellLocation(1) + 1

In the click procedure of button 2 you need to add something like:
    Worksheets("mysheet").Range("D12").Offset(0, CellLocation(2)).Value = mynumber
    CellLocation(2) = CellLocation(2) + 1

Etc...

Important part is to use the integrated help function of VBA!!!! Search and read the help for the objects used in this post: Range, Cells, Selection, Offset, Worksheets, etc... For the other things on how to do something (working with variables) you need to learn how to program, this is very hard to explain in 1 post and you're better of getting a book on VBA from your local library.

This post was edited on 05-15-2005 at 11:23 PM by CookieRevised.
.-= A 'frrrrrrrituurrr' for Wacky =-.
05-15-2005 11:20 PM
Profile PM Find Quote Report
« Next Oldest Return to Top Next Newest »

Messages In This Thread
Active Cell in VBA - by mwe99 on 05-15-2005 at 10:03 PM
RE: Active Cell in VBA - by CookieRevised on 05-15-2005 at 11:20 PM
RE: Active Cell in VBA - by mwe99 on 05-15-2005 at 11:33 PM


Threaded Mode | Linear Mode
View a Printable Version
Send this Thread to a Friend
Subscribe | Add to Favorites
Rate This Thread:

Forum Jump:

Forum Rules:
You cannot post new threads
You cannot post replies
You cannot post attachments
You can edit your posts
HTML is Off
myCode is On
Smilies are On
[img] Code is On