vba looping for blank cells

  • Thread starter Thread starter BeJay
  • Start date Start date
B

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
 
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
 
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
 
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
 
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.
 
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
 
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.
 
Back
Top