Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Going loopy on a loop
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Going loopy on a loop
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 "John in Surrey" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Going loopy on a loop
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Going loopy on a loop
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Going loopy on a loop
thanks to all three of you
cheers john |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loopy? | Excel Worksheet Functions | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Loopy Code | Excel Programming | |||
Problem adding charts using Do-Loop Until loop | Excel Programming | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming |