Going loopy on a loop

[John in Surrey]

hi folks

Im looping thru a range to find the first blank cell to fill...
of exiting my loop how do I paste into it?

Range("B4").Select
Selection.Copy
For Each c In Worksheets("admit").Range("B11:B32").Cells
If c.Value = 0 Then Exit For
Next
' past data here

how do I past into c.? - the first cell containing 0 that Ive found...

thanks folks

Images of home (NZ)
http://www.titahi-bay.co.nz/home
What we are up to in the UK
http://www.titahi-bay.co.nz

[Tom Ogilvy]

My understanding is that you can't trust the loop variable outside the loop.
So I would just do the copy at that point

Dim c as Range
For Each c In Worksheets("admit").Range("B11:B32")
If c.Value = 0 Then
Range("B4").copy c
Exit For
end if
Next


--
Regards,
Tom Ogilvy



"John in Surrey" <[email protected]> wrote in message
news:[email protected]...
> hi folks
>
> Im looping thru a range to find the first blank cell to fill...
> of exiting my loop how do I paste into it?
>
> Range("B4").Select
> Selection.Copy
> For Each c In Worksheets("admit").Range("B11:B32").Cells
> If c.Value = 0 Then Exit For
> Next
> ' past data here
>
> how do I past into c.? - the first cell containing 0 that Ive found...
>
> thanks folks
>
> Images of home (NZ)
> http://www.titahi-bay.co.nz/home
> What we are up to in the UK
> http://www.titahi-bay.co.nz

[Don Guillett]

No loop required. Try this

Sub fillfirstblankinrange()
With Worksheets("sheet2")
..Range("b4").Copy .Range(.Range("b11:b32").Find("").Address)
End With
End Sub
--
Don Guillett
SalesAid Software
[email protected]
"John in Surrey" <[email protected]> wrote in message
news:[email protected]...
> hi folks
>
> Im looping thru a range to find the first blank cell to fill...
> of exiting my loop how do I paste into it?
>
> Range("B4").Select
> Selection.Copy
> For Each c In Worksheets("admit").Range("B11:B32").Cells
> If c.Value = 0 Then Exit For
> Next
> ' past data here
>
> how do I past into c.? - the first cell containing 0 that Ive found...
>
> thanks folks
>
> Images of home (NZ)
> http://www.titahi-bay.co.nz/home
> What we are up to in the UK
> http://www.titahi-bay.co.nz

[Norman Jones]

Hi John,

If you mean blank, rather than 0 value, then try:

'=============>>
Public Sub Tester001()
Dim rng As Range
Dim srcRng As Range
Dim destRng As Range

Set srcRng = Range("B4")
Set rng = Range("B11:B32")


On Error Resume Next
Set destRng = rng.SpecialCells(xlCellTypeBlanks)(1)
On Error GoTo 0

If Not destRng Is Nothing Then
srcRng.Copy Destination:=destRng
End If
End Sub
'<<=============


---
Regards,
Norman



"John in Surrey" <[email protected]> wrote in message
news:[email protected]...
> hi folks
>
> Im looping thru a range to find the first blank cell to fill...
> of exiting my loop how do I paste into it?
>
> Range("B4").Select
> Selection.Copy
> For Each c In Worksheets("admit").Range("B11:B32").Cells
> If c.Value = 0 Then Exit For
> Next
> ' past data here
>
> how do I past into c.? - the first cell containing 0 that Ive found...
>
> thanks folks
>
> Images of home (NZ)
> http://www.titahi-bay.co.nz/home
> What we are up to in the UK
> http://www.titahi-bay.co.nz

[John in Surrey]

thanks to all three of you
cheers
john