Jumping in a For ..Next Loop
 

XL2002

My basic macro is:-

Sub ABC()

Newtext = ""
For C = 1 to 5
For R = 1 to 10
newtext = newtext & Cells(R,C)
next R
Cells(r,12) = newtext
newtext = ""
next C

End

I.E. I want to concatenate the text in Column A rows 1 - 10 and put the
result in L1
Then do the same for column B and put the result in L2 and so on

However, I want to ignore any blank cells - i.e. do not insert any ""s into
the strings.

I've tried inserting what I would call a forced jump [as below in caps]

Sub ABC()

Newtext = ""
For C = 1 to 5
For R = 1 to 10

IF CELLS(R,C) = "" THEN NEXT R

newtext = newtext & Cells(R,C)
next R
Cells(r,12) = newtext
newtext = ""
next C

End

in other words ; if the cell concerned is blank then jump to the next row
but this doesn't work.

I thought that it could be that the forced NEXT R instruction is putting R
out of its For...Next range but it happens even when R is below 10

What am I doing wrong?

Thanks

Bertrand

Jumping in a For ..Next Loop
 

For R = 1 to 10
IF CELLS(R,C) < "" THEN newtext = newtext & Cells(R,C)
next R

"Bertrand" wrote:

XL2002

My basic macro is:-

Sub ABC()

Newtext = ""
For C = 1 to 5
For R = 1 to 10
newtext = newtext & Cells(R,C)
next R
Cells(r,12) = newtext
newtext = ""
next C

End

I.E. I want to concatenate the text in Column A rows 1 - 10 and put the
result in L1
Then do the same for column B and put the result in L2 and so on

However, I want to ignore any blank cells - i.e. do not insert any ""s into
the strings.

I've tried inserting what I would call a forced jump [as below in caps]

Sub ABC()

Newtext = ""
For C = 1 to 5
For R = 1 to 10

IF CELLS(R,C) = "" THEN NEXT R

newtext = newtext & Cells(R,C)
next R
Cells(r,12) = newtext
newtext = ""
next C

End

in other words ; if the cell concerned is blank then jump to the next row
but this doesn't work.

I thought that it could be that the forced NEXT R instruction is putting R
out of its For...Next range but it happens even when R is below 10

What am I doing wrong?

Thanks

Bertrand