×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel VBA - Moving Between Columns *very simple questions*

Excel VBA - Moving Between Columns *very simple questions*

Excel VBA - Moving Between Columns *very simple questions*

(OP)
Sorry I am new to Excel VBA and its obects etc.

What is the VBA syntax from moving from one column to another.

ie I want to move from C2 to D2 for example.

With Rows I could just do a count (ie going up 1 every time I want to move down a row) and concatenate that with the COLUMN in a string.

But with Columns, how do I do a A+1 = B, B+1 = C!!!

There must be a simple acMoveOneCellLeft (obviously not this exactly!!) or something?????

HELP!!

Thanks as Always
Ross

PS Also does anyone know any good EXCEL VBA online sites with hints/tips/example coding etc. I would much appreciated some good sites.

RE: Excel VBA - Moving Between Columns *very simple questions*

Ross,
The VBA code for this is relatively simple. Use the following code in a macro or event to make the desired cell the active cell.

Range("D2").Select

I'm not sure of any Excel forum, but I can give you a tip to find code for yourself. When I first started creating code and designing programs in Microsoft Office, a friend suggested that I use the "record macro" option to get me started. Whether your using Excel or Word or even Access, the ability to record your movements as a macro can lay the foundation for your code. At the very least it will get you started with your code module. Try it and see what you think.



Bryan Meek
bmeek@pacbell.net
http://www.handtech.com/meekent
A TC in your corner gives you the personal attention you need to find the right technology solutions for your business.

RE: Excel VBA - Moving Between Columns *very simple questions*

(OP)
Bryan.

This is not what I meant. What I wanted was something to use in a loop.
eg using some sort of counter so you could move from column to column without hardcoding the "D". How would you move to E in a loop?

BTW, I know how to do it now (using offset).

Thanks anyway
Ross

I already know about recording macros....I am not that much of a beginner!
(no offence meant thought, any replies to my postings on here are always much appreciated)

RE: Excel VBA - Moving Between Columns *very simple questions*

I see that you've discovered the offset method.  You can also use the Cells method.  This is particularly handy in conjunction with for/next loops. For example, to put something into every cell in column "D", from rows 5 through 125, you could write:

Dim Counter as Integer
For Counter = 5 to 125 step 1
   Cells(Counter,4).Value = "XXXXXX"
Next Counter

I can't speak for any online sites, but for a small amount of money you can purchase a GREAT set of examples with associated code at the following web site.
http://www.add-ins.com/vbexmpls.htm

I have no association with the company other than being an extremly satisfied customer.  This is an add-in to Excel which may be called from the "Help" menu.  It contains a tremendous amount of "how to" examples.  The associated code is demonstrated.

RE: Excel VBA - Moving Between Columns *very simple questions*

Use the Activecell.offset(Rowposition, Columnposition)
create a variable for the columposition, and increment it by 1 each time.  I have some code already written that does this sort of thing but I haven't found it yet.
Try it.  It should work.

RE: Excel VBA - Moving Between Columns *very simple questions*

Sub Trythis()

Dim ls_CellValue As String
Dim ls_CellValue2 As String
Dim Li_Position As Integer

Li_Position = 2

Range("a" & Li_Position).Select


Do Until Li_Position = 125

        ls_CellValue = ActiveCell.Value
        Li_Position = Li_Position + 1
        ActiveCell.Offset(0, Li_Position).Select
        ls_CellValue2 = ActiveCell.Value
    If ls_CellValue <> ls_CellValue2 Then
        MsgBox "Error" & "" & Li_Position
    End If
    Loop
    

End Sub

You'll need to creat another variable to keep track of where you are curently and then subract from it.  this example moves you 4 columns each time.  

RE: Excel VBA - Moving Between Columns *very simple questions*

I got this to finally work.  Sorry about any confusion above.  There still is some unneeded code in here. You can walk across you spread sheet with this code.


Sub Trythis()

Dim ls_CellValue As String
Dim ls_CellValue2 As String
Dim Li_Position As Integer
Dim Li_SubPosition As Integer
Li_Position = 1

Range("a" & Li_Position).Select


Do Until Li_Position = 75

        ls_CellValue = ActiveCell.Value
        'Li_Position = Li_Position + 1
        ActiveCell.Offset(0, Li_Position).Select
        ls_CellValue2 = ActiveCell.Value
    If ls_CellValue <> ls_CellValue2 Then
        MsgBox "Error" & "" & Li_Position
    End If
    Loop
    

End Sub

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close