vba looping for blank cells

[BeJay]

hi there, to simplify, I need to work out some VBA to take the value of
populated cells in column C and insert this into blank cells in column
A.

A B C

a 10.00
a 20.00 30.00

b 1.00
b 23.00
b 25.00
b 15.00 64.00

c 24.00
c 54.00
c 62.00 140.00

d 1.00 1.00

I could work out how to loop to find the populated cells in C and then
to find blanks in A but I have no idea how to combine this code and
then to insert the value it has found from C into A. The number of
lines inbetween always varies and the first value will match the first
blank etc.

Again, any help would be really very gratefully appreciated. BevJ

 

[Bondi]

Hi,

I not sure what you are looking for but this code will take the number
in column C and place it in the first empty cell in column A.

Private Sub CommandButton1_Click()
Dim i
For i = 1 To 20
If Cells(i, 3) <> vbEmpty Then
Cells(i, 3).Offset(1, -2).Value = Cells(i, 3).Value
End If
Next i
End Sub

Regards,
Bondi

 

[Don Guillett]

try something like this?

for each c in range("a2:a" & cells(rows.count,1).end(xlup).row)
if len(c)<2 then c.value=c.offset(,2)
next c

 

[BeJay]

hey bondi, thanks.. that works almost perfect, but the value needs to
be moved into the blank space above not below - if you get my drift

i.e

30
a 10
a 20
60
b 10
b 20
b 30

can I use the same code as a basis to do this. I know the offset
affects which column but wouldn't know how to find the next row up
rather than the next row down. thankyou for your help with this, BevJ

 

[Don Guillett]

Look in help index for OFFSET to see how it works. As I understand you want
the info from col C on the SAME row to be copied to col A.

 

[BeJay]

Cheers Don, thanks for your suggestions. I was looking up help for
OFFSET as your message appeared. I also ran ur code, (thanks for that)
but it removed the existing data in column A.

I actually need the subtotal to appear ABOVE all the order lines in an
order header row, so in effect, in the first empty cell in A that
occurs before the subtotal occurs in C.

I do not know if using OFFSET allows you to look back up a column for
an empty cell, still trying to work out the help file, but would really
appreciate any further suggestions you may have, Bev J

 

[Don Guillett]

send a workbook to my personal address with a very clear and detailed
explanation of what you want with what it looks like before and what it
looks like after.

 

[Don Guillett]

Solved privately