Moving the cursor around with a macro....

[HpyTrvlr69]

Silly question for all you smart guys out here....where does one lookup info
on the moving and selecting of cursor and cell locations programmatically
(Macro or VB). I know....Im really dumb!!

 

[HpyTrvlr69]

Silly question for all you smart guys out here....where does one lookup info
on the moving and selecting of cursor and cell locations programmatically
(Macro or VB). I know....Im really dumb!!

My basic use is to test cells in a column for matches and move down the
column, cell by cell, as the test continues

 

[FSt1]

hi
there are a number of ways to do this. basicily
range("a2").select

or with a range name..
lets say your range name is myrange
range("myrange").select

or maybe with varialbes..
dim r as range
set r = range("A2")
r.select

or if moving down a list in a loop....
dim r as range
dim ro as range
set r = range("A2")
do until stuff = done
set ro = r.offset(1,0)
r.select
stuff
set r = ro
loop

you may want to know the difference between select and activate.
run this macro
range("A2:F10").select
range("C5").activate

normally when selecting a range, the upper left cell becomes the active
cell. but in the above example C5 is the activecell, not A2.

in short, the method used depends on situation. but normally selecting the
cells is not neccessary unless you just want to entertain your users with the
curser and screen flopping all over the place. that slows things down.
but it does entrertain the users.

post back if i didn't cover something.
regards
FSt1

 

[FSt1]

ooo
i re-read your second post.
matches what??
regards
FSt1

 

[HpyTrvlr69]

Well I am trying to tell if the cell above it is a duplicate, if it is, I
format both cells and then advance one cell down and do the process all over
again. Thanks for the info so far.

 

[HpyTrvlr69]

I am trying to "weed" out duplicates from the cell above. The process is
compare the active cell with the cell above and if they match, format it a
certain way and then advance the active dell down one cell. Easier said than
done, for me.

 

[Patrick Molloy]

you could do this
(1) using Conditional Formatting. For exampl if you want to see duplicates
in D, then select the entire column, select Format/Conditional Formatting,
switch to Formula Is and add the formula
=AND(D1=D2,D1<>"")
then select some back color or whatever

(2) use COUNTIF so in E1 put =COUNTIF(D,D1) copy the formula down.
use autofilter on E where the value <>1 or >1

(3) in VBA:

col="D"
for rw = 2 to cells(1,col).End(xldown).Row
if cells(rw,col)=cells(rw-1,col) then
cells(rw,col).Iterior.Color=vbRed
end if
next