need a macro to copy the data to the following cells

[pol]

Please help with a macros to copy the data to acitve cell from the cell just
before the activce cell ( Only if the active cell is not null ). for example

type name
1 XX
2 xx1
3 xx2
4 YY
5 yy1
6 yy2

In the above example I want to copy XX from A1 cell to range a2..a3,
YY from A4 to copy to a4...a6. So I need to write the macro to copy
conditionally if the active cell is not null . The out put should come
type name
1 XX
2 XX xx1
3 XX xx2
4 YY
5 YY yy1
6 YY yy2
Please help

With thanks
Pol

 

[Jacob Skaria]

To do this you dont need a macro...

--Select a unused cell say E2
--Enter the formula in cell E2 which references the top cell (=E1)
--Copy the cell
--Keeping the copy select the data range in ColA (say A1:A100).
--Press F5. From Goto window select blanks
--This will select all blanks.. Now Right click>PasteSpecial>Formulas. will
fill in with the data you need..
--Once done you can select col A > Copy >PasteSpecial>Values to turn
formulas to actual values


PS: Post back if you still would prefer a macro

If this post helps click Yes

 

[pol]

If the excel have 4000 or 5000 record it is not easy to do this by a user .
In the following example given , the unused cell is is A2..A3 and A5 .. A6
In a2.. a3 I have to copy XX and A5.. A6 I have to copy YY. So I think that
is not a solution for giving the equation ( =A1) from unused cell (a2) . The
copy and paste of this formula will not be wroked out.

With thanks for the reply.

Pol

 

[Jacob Skaria]

By unused cell I mean a cell out side this range...Try out entering the
formula in E2 itself> copy the cell (not the formula..). Select your data
range ie Columna A. Use Go To window to select blanks and then PasteSpecial
formulas...

Even if you have an entire column of data this way works...

If this post helps click Yes

 

[Jacob Skaria]

Blanks cells are selected from the Goto Window > Special> select 'Blanks'

OR ..try the below macro which works on your data in ColA and ColB

Sub MyMacro()
Dim lngRow As Long
For lngRow = 2 To Cells(Rows.Count, "B").End(xlUp).Row
If Trim(Range("A" & lngRow)) = "" Then _
Range("A" & lngRow) = Range("A" & lngRow - 1)
Next
End Sub


If this post helps click Yes

 

[pol]

I think may be you haven't got clear idea from my example. One column
contain Item size , another column in next row contain item name. The same
item size may be repeated for 100s of item. So if the same item size is
coming for following item, the item size will not be repeated for the item
under the same size. 1000's of item size may come. But the orginal excel will
be sorted acording to the size of the item.

I need to copy each size against each item ( each row) . many itemsize and
its item can be seen

Size item
A
1
2
3
B
4
5
6
Output

A
A 1
A 2 as follows

With thanks
Pol

 

[Joel]

You need to have a macro to perform this task. I writine dprograms like this
before but I need more information from you to get it correct.

1) Are you groups always 3 rows or is there a different criteria that you
use to seperate the groups of rows?

2) Are you combining the data into 1 cell or are you putting data in column B.

3) You said there was empty cells but your data didn't show any empty cells.
Can you post an example showing the empty cells as well as data with
different number of rows per group.

 

[Jacob Skaria]

OK. Did you try the macro?

If this post helps click Yes

 

[pol]

example

A B C D

1 SIZE1
2 ITEM1
3 ITEM2
4 SIZE2
5 ITEM3
6 SIZE3
7 ITEM4
8 ITEM5
9 ITEM6


In the above example a2, a3,a5 etc are empty becuase A2, A3 will be the same
size mentioned in A1. A5 will be the same in A4. So I need copy the value
from A1 to a2,a3 , simillary the value from A4 to A5, value from A6 to
A7,A8,A9. In short I have to copy from top cell value to the next cell if it
is blank.

With thanks
Pol

 

[Joel]

This should work. It makes a big difference when a good example is posted.


Sub test()

LastRow = Range("B" & Rows.Count).End(xlUp).Row

Col_A = ""
For RowCount = 1 To LastRow
If Range("A" & RowCount) = "" Then
Range("A" & RowCount) = Col_A
Else
Col_A = Range("A" & RowCount)
End If

Next RowCount


End Sub

 

[pol]

works fine. thanks lot

This should work. It makes a big difference when a good example is posted.


Sub test()

LastRow = Range("B" & Rows.Count).End(xlUp).Row

Col_A = ""
For RowCount = 1 To LastRow
If Range("A" & RowCount) = "" Then
Range("A" & RowCount) = Col_A
Else
Col_A = Range("A" & RowCount)
End If

Next RowCount


End Sub