Excel VBA - Moving Between Columns *very simple questions*

[rossmcl]

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.

 

[BMeek]

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&quot.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.

 

[rossmcl]

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)

 

[godejamr]

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.

 

[pcdaveh]

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.

 

[pcdaveh]

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 &quot;Error&quot; & &quot;&quot; & 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.

 

[pcdaveh]

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(&quot;a&quot; & 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 &quot;Error&quot; & &quot;&quot; & Li_Position
End If
Loop


End Sub