Loop Problem

NYQuicksale
NYQuicksale used Ask the Experts™
on
this code doesn't work when there's just one symbol.

actually this code's purpose is to do the following:
i've sheet which contains too many ticker symbols and whenever one symbol is done, then cut and paste that symbol somewhere else and make that cell blank and goto next symbol, so whenevr i again run the loop, it would start from whatever next populated cell in that column. i hope u get the idea, but this below code doesn't work, when there's just one symbol there in column A

Sub loopC()
Dim cel As Range
Dim mySheet As Worksheet

Set mySheet = Sheets("info")

For Each cel In Sheets("symbols").Range("A" & Sheets("symbols").Range("A1").End(xlDown).Row & ":A" & Sheets("symbols").Range("A1").End(xlDown).End(xlDown).Row)
mySheet.Range("A1") = cel.Value
Next cel

End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I think you have a mixup where you're setting the value.  Try this
Sub loopC()
Dim cel As Range
Dim mySheet As Worksheet

Set mySheet = Sheets("info")

For Each cel In Sheets("symbols").Range("A" & Sheets("symbols").Range("A1").End(xlDown).Row & ":A" & Sheets("symbols").Range("A1").End(xlDown).End(xlDown).Row)
cel.Value = mySheet.Range("A1").Value
Next cel

End Sub

Open in new window

Note the difference between this:

mySheet.Range("A1") = cel.Value

And this:

cel.Value = mySheet.Range("A1").Value
Try this

For Each cel In Sheets("symbols").Range("A" & Sheets("symbols").Range("A1").End(xlDown).Row & ":A" & Sheets("symbols").Range("A" & Rows.Count).End(xlUp).Row)

Author

Commented:
it has entered into infinite loop
Which solution are you referring to?

Author

Commented:
both
Is your goal to select all the cells in column A that have text?

Author

Commented:
saqib your code is working now, why u didn't give it before? i'm just testing it every perspective, dun want to get into trouble again
Take your time. I am not in a hurry. You could have continued in the previous question and I would have been pleased to assist you.

...why u didn't give it before?

I never imagined you would want to cover "Only one" as well.

Saqib

Author

Commented:
that's by default, it can be from one to one million

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial