add text in every every cell in a specific column

[atditiljazi]

hi all,

I have tried to do a macro but nothing happens when I try it...

I want the macro to write a text in every empty cell in column L until it reaches the last row. can someone tell me where I have gone wrong? my macro is below


Sub replace_black_cell()

Dim count_row As Long
Dim count_col As Long
Dim x As Range
Dim y As Range

Sheets("order book").Activate
count_col = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlToRight)))
count_row = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown)))

Set x = Range(Cells(1, 12), Cells(count_row, 12))

For Each y In x.Cells

If y = "" Then
y = "confirm"
End If

Next y



End Sub

also, how would I add data from column J into every empty cell in column M? can I add it to the current macro or would I need to make a new one?

thanks

 

[Micron]

Not exactly sure what you have and are attempting to do. If using row and column methods the normal approach seems to be to work from the right to left and bottom to top - unless maybe you have a cell at the lower end of the column that defines the end point.

Put a break point at the beginning of the code and step through (F8) and see what gives. Not likely that it 'nothing happens' unless it doesn't fire at all.

 

[Z51]

This code may work for you:

Sub atditiljazi()
Dim lastrow As Long, ws As Worksheet, ce As Range
Set ws = Sheets("order book")
lastrow = ws.Range("A" & Rows.Count).End(xlUp).Row

On Error Resume Next
ws.Range("L1:L" & lastrow).SpecialCells(xlCellTypeBlanks).Value = "confirm"
For Each ce In ws.Range("M1:M" & lastrow).SpecialCells(xlCellTypeBlanks)
    ce.Value = ce.Offset(0, -3).Value
Next ce
On Error GoTo 0
End Sub

 

[Micron]

I tested OP's code - it worked so "nothing happens" might mean something else.

 

[atditiljazi]

I tested OP's code - it worked so "nothing happens" might mean something else.

This code may work for you:

Sub atditiljazi()
Dim lastrow As Long, ws As Worksheet, ce As Range
Set ws = Sheets("order book")
lastrow = ws.Range("A" & Rows.Count).End(xlUp).Row

On Error Resume Next
ws.Range("L1:L" & lastrow).SpecialCells(xlCellTypeBlanks).Value = "confirm"
For Each ce In ws.Range("M1:M" & lastrow).SpecialCells(xlCellTypeBlanks)
    ce.Value = ce.Offset(0, -3).Value
Next ce
On Error GoTo 0
End Sub

hi thanks for the help but I still can't seem to get it to work. I'm not sure what I'm doing wrong. I tried the macro but, no "confirm" goes in the empty cells in column L, and the date in column J doesn't copy and paste into the empty cells in column M. I'm not getting an error code either. i have attached an image of a sample worksheet

 

[Micron]

Seems to me that this:

For Each ce In ws.Range("M1:M" & lastrow).SpecialCells(xlCellTypeBlanks).Value = "confirm"

will only put confirm in the last row that has data in column A. It doesn't loop over the range?
This ce.Value = ce.Offset(0, -3).Value
puts the date from J into M on the same row. If that's not happening either, how do you know if the code actually runs? How are you calling it? To confirm, put
msgbox "atditiljazi" right after On Error Resume Next and see if you get a message box.

IMO having Resume Next is asking for trouble and should only be used in certain contexts and I don't think this is one of them. You didn't have it in your original code so I'm not seeing that as the main cause at the moment.

 

[Z51]

Note that I added the On Error Resume Next statement to prevent an error when no blank cells existed in columns L or M. As you said, it's not a great but it works on a sample spreadsheet I put together.

I re-created your sample data in a new workbook and named the worksheet "order book". Note that my code should go into the code module for that worksheet. If the worksheet with your data is named something else, change that in the code.

Before running my code:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Unique Reference Number	SUPPLIER NAME	Item Reference Number	Item Description	Purchase Order Number	Purchase Order Line Number	Date Purchase Order Created / Raised	Order Currency	Unit Cost Price	due date	Open Quantity on Purchase Order	MYM expedite, De-Expedite or Cancel Messages	MYM Required Delivery Date
2	aaaaa	q	1	a	2	1	01/01/2001	gdp	2	01/02/2001	04/02/1900	ftg	01/01/2023
3	aaaaa	we	2	a	2	1	02/01/2001	gdp	3	02/02/2001	05/02/1900	gfhgxhgx	02/01/2023
4	aaaaa	ere	3	a	2	1	03/01/2001	gdp	4	03/02/2001	06/02/1900
5	aaaaa	fds	3	a	2	1	04/01/2001	gdp	5	04/02/2001	07/02/1900
6	aaaaa	cvx	3	a	2	1	05/01/2001	gdp	6	05/02/2001	08/02/1900	gxhdhd	05/01/2023
7	aaaaa	cvx	3	a	2	1	06/01/2001	gdp	7	06/02/2001	09/02/1900
8	aaaaa	gjh	3	a	2	1	07/01/2001	gdp	8	07/02/2001	10/02/1900	gfhgxhgx	07/01/2023
9	aaaaa	vcx	3	a	2	1	08/01/2001	gdp	9	08/02/2001	11/02/1900
10	aaaaa	hf	3	a	2	1	09/01/2001	gdp	10	09/02/2001	12/02/1900	"confirm"	09/01/2023
11	aaaaa	fdgfd	3	a	2	1	10/01/2001	gdp	11	10/02/2001	13/02/1900	hgfhxhgx	10/01/2023
12	aaaaa	hgfh	3	a	2	1	11/01/2001	gdp	12	11/02/2001	14/02/1900	hgxhgxhgx	11/01/2023
13	aaaaa	hjh	3	a	2	1	12/01/2001	gdp	13	12/02/2001	15/02/1900	gxhhg	12/01/2023
14	aaaaa	o	3	a	2	1	13/01/2001	gdp	14	13/02/2001	16/02/1900
15	aaaaa	k	3	a	2	1	14/01/2001	gdp	15	14/02/2001	17/02/1900
order book

After running my code (no changes to code at all):

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Unique Reference Number	SUPPLIER NAME	Item Reference Number	Item Description	Purchase Order Number	Purchase Order Line Number	Date Purchase Order Created / Raised	Order Currency	Unit Cost Price	due date	Open Quantity on Purchase Order	MYM expedite, De-Expedite or Cancel Messages	MYM Required Delivery Date
2	aaaaa	q	1	a	2	1	01/01/2001	gdp	2	01/02/2001	04/02/1900	ftg	01/01/2023
3	aaaaa	we	2	a	2	1	02/01/2001	gdp	3	02/02/2001	05/02/1900	gfhgxhgx	02/01/2023
4	aaaaa	ere	3	a	2	1	03/01/2001	gdp	4	03/02/2001	06/02/1900	confirm	03/02/2001
5	aaaaa	fds	3	a	2	1	04/01/2001	gdp	5	04/02/2001	07/02/1900	confirm	04/02/2001
6	aaaaa	cvx	3	a	2	1	05/01/2001	gdp	6	05/02/2001	08/02/1900	gxhdhd	05/01/2023
7	aaaaa	cvx	3	a	2	1	06/01/2001	gdp	7	06/02/2001	09/02/1900	confirm	06/02/2001
8	aaaaa	gjh	3	a	2	1	07/01/2001	gdp	8	07/02/2001	10/02/1900	gfhgxhgx	07/01/2023
9	aaaaa	vcx	3	a	2	1	08/01/2001	gdp	9	08/02/2001	11/02/1900	confirm	08/02/2001
10	aaaaa	hf	3	a	2	1	09/01/2001	gdp	10	09/02/2001	12/02/1900	"confirm"	09/01/2023
11	aaaaa	fdgfd	3	a	2	1	10/01/2001	gdp	11	10/02/2001	13/02/1900	hgfhxhgx	10/01/2023
12	aaaaa	hgfh	3	a	2	1	11/01/2001	gdp	12	11/02/2001	14/02/1900	hgxhgxhgx	11/01/2023
13	aaaaa	hjh	3	a	2	1	12/01/2001	gdp	13	12/02/2001	15/02/1900	gxhhg	12/01/2023
14	aaaaa	o	3	a	2	1	13/01/2001	gdp	14	13/02/2001	16/02/1900	confirm	13/02/2001
15	aaaaa	k	3	a	2	1	14/01/2001	gdp	15	14/02/2001	17/02/1900	confirm	14/02/2001
order book

 

[Micron]

Note that I added the On Error Resume Next statement to prevent an error when no blank cells existed in columns L or M.

I figured as much. Better to test with something like an If block or at least use an error handler. If anything in the sheet changes such that it raises an error that has nothing to do with empty vs non-empty cells it will be ignored. Have seen more than one thread where this has caused a silent failure. Lots of coders do it on a regular basis, but not me.

 

[Peter_SSs]

I still can't seem to get it to work.

Could you hide columns B:I and then post A1:M15 with XL2BB so that we can easily test with the same data as you have? We can't really do anything with just a picture.