SIMPLE VBA CODING REQUIRED

[nburaq]

Hi Gents,
The database program I work with generally converts the data with empty spaces and it is very annoying to arrange it every time to be able to work with excel. Is it possible to fill empty cells with the screenshot below: PS: I filled one by manually by red color as an example. If there is title, item or group belongs to an order it gives empty cells and I have to fill it manually and between every item there are three empty rows if there is data belong to order. Thanks for the comments and help!

 

[kweaver]

So, is row 5 missing ONLY A5 and then you want the "2345" filled in? Same with row 7.
OR, does the 2345 exist in A5 and you want the other three columns (B, C, and D) filled when there's a match?

 

[nburaq]

So, is row 5 missing ONLY A5 and then you want the "2345" filled in? Same with row 7.
OR, does the 2345 exist in A5 and you want the other three columns (B, C, and D) filled when there's a match?

2345 exists in row3 with other values next to each other and until row 11 then it starts with new item. i need to fill column A5/B5/C5/D5 by looking at column E whether there is a value ( in this case for 2345 "BAD")

 

[kweaver]

I'm still a bit confused. Does all of row 5 get filled in because "BAD" is in E5 and row 5 is 2 below row 3 that has a "BAD" in E?

If so, by that logic, row 7 is filled in the same.

Then, what gets filled in row 13? Does the GOOD match the "GOOD" in row 11 so the KLM, etc. info gets filled in?

 

[kweaver]

If this is the original:

Book3
	A	B	C	D	E
1	Order	Title	Item	Group	Desc
2	1234	ABC	1	A	GOOD
3	2345	XYZ	10	B	BAD
4
5					BAD
6
7					BAD
8
9
10
11	3210	KLM	12	C	GOOD
12
13					GOOD
14
15
16
17	4567	CDE	9	K	BAD
18
19					BAD
Sheet1

Is this what you want:

Book3
	A	B	C	D	E
1	Order	Title	Item	Group	Desc
2	1234	ABC	1	A	GOOD
3	2345	XYZ	10	B	BAD
4
5	2345	XYZ	10	B	BAD
6
7	2345	XYZ	10	B	BAD
8
9
10
11	3210	KLM	12	C	GOOD
12
13	3210	KLM	12	C	GOOD
14
15
16
17	4567	CDE	9	K	BAD
18
19	4567	CDE	9	K	BAD
Sheet1

 

[nburaq]

If this is the original:

Book3
	A	B	C	D	E
1	Order	Title	Item	Group	Desc
2	1234	ABC	1	A	GOOD
3	2345	XYZ	10	B	BAD
4
5					BAD
6
7					BAD
8
9
10
11	3210	KLM	12	C	GOOD
12
13					GOOD
14
15
16
17	4567	CDE	9	K	BAD
18
19					BAD
Sheet1

Is this what you want:

Book3
	A	B	C	D	E
1	Order	Title	Item	Group	Desc
2	1234	ABC	1	A	GOOD
3	2345	XYZ	10	B	BAD
4
5	2345	XYZ	10	B	BAD
6
7	2345	XYZ	10	B	BAD
8
9
10
11	3210	KLM	12	C	GOOD
12
13	3210	KLM	12	C	GOOD
14
15
16
17	4567	CDE	9	K	BAD
18
19	4567	CDE	9	K	BAD
Sheet1

You are magician! Yes even I cant explain very well this is what I really want ! Then I will remove the empty rows with classic find empty cells and delete rows then voila a clean data to be used

 

[kweaver]

I used this:

Sub FillIn()
Dim lr As Long, i As Long, w As Long
lr = Cells(Rows.Count, "E").End(xlUp).Row
For i = 1 To lr
 If IsEmpty(Cells(i, 1)) And Not IsEmpty(Cells(i, 5)) Then
 Cells(i, 1) = Cells(i - 2, 1)
 Cells(i, 2) = Cells(i - 2, 2)
 Cells(i, 3) = Cells(i - 2, 3)
 Cells(i, 4) = Cells(i - 2, 4)
 Else
 End If
Next i
End Sub

 

[nburaq]

I used this:

Sub FillIn()
Dim lr As Long, i As Long, w As Long
lr = Cells(Rows.Count, "E").End(xlUp).Row
For i = 1 To lr
If IsEmpty(Cells(i, 1)) And Not IsEmpty(Cells(i, 5)) Then
Cells(i, 1) = Cells(i - 2, 1)
Cells(i, 2) = Cells(i - 2, 2)
Cells(i, 3) = Cells(i - 2, 3)
Cells(i, 4) = Cells(i - 2, 4)
Else
End If
Next i
End Sub[/
[/QUOTE]
It gives an compile error: expected end of statement

 

[kweaver]

Do you have that stuff at the end? Re-copy and install this:

Sub FillIn()
Dim lr As Long, i As Long
lr = Cells(Rows.Count, "E").End(xlUp).Row
For i = 1 To lr
 If IsEmpty(Cells(i, 1)) And Not IsEmpty(Cells(i, 5)) Then
 Cells(i, 1) = Cells(i - 2, 1)
 Cells(i, 2) = Cells(i - 2, 2)
 Cells(i, 3) = Cells(i - 2, 3)
 Cells(i, 4) = Cells(i - 2, 4)
 Else
 End If
Next i
End Sub

 

[nburaq]

Do you have that stuff at the end? Re-copy and install this:

Sub FillIn()
Dim lr As Long, i As Long
lr = Cells(Rows.Count, "E").End(xlUp).Row
For i = 1 To lr
If IsEmpty(Cells(i, 1)) And Not IsEmpty(Cells(i, 5)) Then
Cells(i, 1) = Cells(i - 2, 1)
Cells(i, 2) = Cells(i - 2, 2)
Cells(i, 3) = Cells(i - 2, 3)
Cells(i, 4) = Cells(i - 2, 4)
Else
End If
Next i
End Sub

Sorry it was my bad because I think I found why it gave error; There are empty cells under column "Desc" despite that row has "order " in this case either I have to fill those cells by myself as "NA" or could you implement this to your code ? Thanks again