Looping Question (I think?)

[Guest]

I think I need to write code to loop through my data, but cannot figure out
how to do this. What I need to do is, copy the number in column B down until
a different number is displayed, and then copy the new number down, etc. (ie
in the example below,for the Banana and Carrot entries, the corresponding
values in column B would be 548)

(Column A) (Column B)
Banana 548
Banana
Banana
Carrot
Carrot
Carrot
Carrot
Apple 635
Apple
Apple
Pear 589
Pear

Any and all assistance would be greatly appreciated.

Thank You,
MWS

 

[Harald Staff]

Hi MWS

Run this with a Sub test()
Dim L As Long
Dim C As Long
Dim V As Variant
C = 2
L = 1
Do Until Cells(L, 1).Value = ""
If Cells(L, C).Formula <> "" Then
V = Cells(L, C).Formula
Else
Cells(L, C).Formula = V
End If
L = L + 1
Loop
End Sub

HTH. Best wishes Harald

 

[Thomas Ramel]

Grüezi MWS

MWS schrieb am 27.10.2004

What I need to do is, copy the number in column B down until
a different number is displayed, and then copy the new number down, etc. (ie
in the example below,for the Banana and Carrot entries, the corresponding
values in column B would be 548)

(Column A) (Column B)
Banana 548
Banana
Banana
Carrot
Carrot
Carrot
Carrot
Apple 635
Apple
Apple
Pear 589
Pear

Any and all assistance would be greatly appreciated.

Assuming your first value is in B1 then follow these steps:

Mark column B completly --> F5 --> [Contains...] (butteon bottom left of
the window) --> [x] empty cells --> [OK]

Now write the following formula:

=B1

and press Ctrl+Return

Mark column B completly again --> Copy --> Rightclick -> insert values (or
similar) --> [x] values --> [OK]

--
Regards

Thomas Ramel
- MVP for Microsoft-Excel -

[Win XP Pro SP-1 / xl2000 SP-3]