Excel VBA tip for incrementing strings (ie A-->B-->C) 1

[handlebars]

hi there,

Trying to write a basic vba procedure to loop through a spreadsheet looking for duplicates (by first name and initials). Need to increment the row number, but have defined row as an integer.

Does anyone know the best way to do this (is it using chr(20), chr(21)??

Thanks in advance.

Andrew

 

[dyarwood]

Have you sorted the spreadsheet?

If so can you not do a loop such as

i = 1
Do until IsEmpty(Cells(i,1))
j=i+1
If Cells(i,1).Value = Cells(j,1) Then

Do Something here

Else
i=i+1
End If

Loop

 

[handlebars]

Thanks for your reply,

It must be monday morning. I am not thinking straight - as i need to increment the column number (which is an integer - that can be incremented by + 1)!!!


Thanks for your reply all the same.

Andrew

 

[xlbo]

you need to increment column or row - you have stated both so far...either way, there is no need to increment letters as both rows AND columns can be incremented by numbers alone

Columns(1) = A
Columns(2) = B

etc etc - I'm sure you get the idea. To reference a range using this kind of syntax, you need to use the CELLS function which takes a row number and a column number to express the intersection of teh 2 as a range eg

cells(1,1) = Range("A1")
cells(1,2) = Range("B1")
cells(2,1) = Range("A2")

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[VBAjedi]

handlebars,

Also, if the range you are looping through is a contiguous block of cells, you can easily do this without specifically incrementing anything:

Dim C as Range
For Each C in Range("A4:Z100")
   ' Check C.Value here and do whatever.
   ' One option would be adding the value to a collection.
   ' If it errors, it is a duplicate. 
Next C

Hope that's helpful


VBAjedi