For Each Visible cell in range, get me the left 6 characters

[RockandGrohl]

Hi all, venturing into the wild world of For Each instead of Do Until loops, have the following:

Dim LastrowAD As LongDim cl As Range
Dim visrng As Range


LastrowAD = Cells(Rows.Count, "A").End(xlUp).Row


Set visrng = Range("A2:A" & LastrowAD)


For Each cl In visrng.SpecialCells(xlCellTypeVisible)
   '>>IF LEN OF CELL IN COLUMN A IS >6, THEN GET THE LEFT 6 CHARACTERS, OTHERWISE JUST REPEAT THE CELL<<
Next

What I'm struggling with is how to get it to loop through each visible cell. It seems to do the first visible cell, but no others in the range?

I'm sure once I've got that, I can just put in the IF LEN blah blah blah part.

Cheers!

 

[RoryA]

What's the value of LastrowAD when the code runs?

 

[Fluff]

That should work, what is the value of LastrowAD?

 

[mikerickson]

I've found that, when looping through ranges, its best to specify what you are looping through

For Each cl In visrng.SpecialCells(xlCellTypeVisible)[COLOR="#FF0000"].Cells[/COLOR]

(as opposed to .Row, .Columns. or .Areas)

I'd add to RoryA's question, what is the address of visrng.SpecialCells(xlCellTypeVisible)?

 

[RockandGrohl]

What's the value of LastrowAD when the code runs?

28,433

I've got it currently set to "Cells(Activecell.row, "W").value = "DONK" "

In the For Each loop

So I get a nice fat DONK in cell W26 as that's the first visible one, but nothing in W70, 71, 72, 73, 74 and so on.

 

[RockandGrohl]

I've found that, when looping through ranges, its best to specify what you are looping through

For Each cl In visrng.SpecialCells(xlCellTypeVisible)[COLOR=#FF0000].Cells[/COLOR]

(as opposed to .Row, .Columns. or .Areas)

I'd add to RoryA's question, what is the address of visrng.SpecialCells(xlCellTypeVisible)?

Thanks, that top bit doesn't work.

To answer your second, when hovering over visrng it doesn't display anything.

 

[Fluff]

How many rows are visible?

 

[mikerickson]

28,433

I've got it currently set to "Cells(Activecell.row, "W").value = "DONK" "

In the For Each loop

So I get a nice fat DONK in cell W26 as that's the first visible one, but nothing in W70, 71, 72, 73, 74 and so on.

That is because the ActiveCell is the same in every loop.

Try putting

cl.Offset(0, 22).Value = "Donk"

inside the loop

 

[RockandGrohl]

That should work, what is the value of LastrowAD?

If I do this:

Dim LastrowAD As LongDim cl As Range
Dim visrng As Range


LastrowAD = Cells(Rows.Count, "A").End(xlUp).Row


Set visrng = Range("A2:A" & LastrowAD)


For Each cl In visrng.SpecialCells(xlCellTypeVisible).Cells
    MsgBox cl.Address
Next cl

It correctly shows the address of the filtered cells - A26, A70, A71 etc.

So it is looping, but I have no idea the syntax to get the left 6 or checking the len if I can't use ActiveCell.

I'm trying to transcend above activecell and activating and writing one cell at a time as I want to develop my VBA skills.

 

[RockandGrohl]

That is because the ActiveCell is the same in every loop.

Try putting

cl.Offset(0, 22).Value = "Donk"

inside the loop

Thanks, think I'm getting closer, how do I get it to write to column W though?

EDIT: Gold star please, I did it (nearly) all by myself!

Dim LastrowAD As LongDim cl As Range
Dim visrng As Range


LastrowAD = Cells(Rows.Count, "A").End(xlUp).Row


Set visrng = Range("A2:A" & LastrowAD)


For Each cl In visrng.SpecialCells(xlCellTypeVisible).Cells
    If Len(Range("A" & cl.Row)) < 7 Then
    Range("W" & cl.Row).Value = Range("A" & cl.Row).Value
    Else
    Range("W" & cl.Row).Value = Left(Range("A" & cl.Row).Value, 6)
    End If
Next cl