add text in every every cell in a specific column

atditiljazi

New Member
Joined
Nov 22, 2022
Messages
41
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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.
 
Upvote 0
This code may work for you:
VBA Code:
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
 
Upvote 0
I tested OP's code - it worked so "nothing happens" might mean something else.
 
Upvote 0
I tested OP's code - it worked so "nothing happens" might mean something else.
This code may work for you:
VBA Code:
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
 

Attachments

  • Annotation 2023-02-07 084245.png
    Annotation 2023-02-07 084245.png
    63.4 KB · Views: 12
Upvote 0
Seems to me that this:
VBA Code:
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.
 
Upvote 0
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
ABCDEFGHIJKLM
1Unique Reference NumberSUPPLIER NAMEItem Reference NumberItem DescriptionPurchase Order NumberPurchase Order Line NumberDate Purchase Order Created / RaisedOrder CurrencyUnit Cost Pricedue dateOpen Quantity on Purchase OrderMYM expedite, De-Expedite or Cancel MessagesMYM Required Delivery Date
2aaaaaq1a2101/01/2001gdp201/02/200104/02/1900ftg01/01/2023
3aaaaawe2a2102/01/2001gdp302/02/200105/02/1900gfhgxhgx02/01/2023
4aaaaaere3a2103/01/2001gdp403/02/200106/02/1900
5aaaaafds3a2104/01/2001gdp504/02/200107/02/1900
6aaaaacvx3a2105/01/2001gdp605/02/200108/02/1900gxhdhd05/01/2023
7aaaaacvx3a2106/01/2001gdp706/02/200109/02/1900
8aaaaagjh3a2107/01/2001gdp807/02/200110/02/1900gfhgxhgx07/01/2023
9aaaaavcx3a2108/01/2001gdp908/02/200111/02/1900
10aaaaahf3a2109/01/2001gdp1009/02/200112/02/1900"confirm"09/01/2023
11aaaaafdgfd3a2110/01/2001gdp1110/02/200113/02/1900hgfhxhgx10/01/2023
12aaaaahgfh3a2111/01/2001gdp1211/02/200114/02/1900hgxhgxhgx11/01/2023
13aaaaahjh3a2112/01/2001gdp1312/02/200115/02/1900gxhhg12/01/2023
14aaaaao3a2113/01/2001gdp1413/02/200116/02/1900
15aaaaak3a2114/01/2001gdp1514/02/200117/02/1900
order book


After running my code (no changes to code at all):
Book1
ABCDEFGHIJKLM
1Unique Reference NumberSUPPLIER NAMEItem Reference NumberItem DescriptionPurchase Order NumberPurchase Order Line NumberDate Purchase Order Created / RaisedOrder CurrencyUnit Cost Pricedue dateOpen Quantity on Purchase OrderMYM expedite, De-Expedite or Cancel MessagesMYM Required Delivery Date
2aaaaaq1a2101/01/2001gdp201/02/200104/02/1900ftg01/01/2023
3aaaaawe2a2102/01/2001gdp302/02/200105/02/1900gfhgxhgx02/01/2023
4aaaaaere3a2103/01/2001gdp403/02/200106/02/1900confirm03/02/2001
5aaaaafds3a2104/01/2001gdp504/02/200107/02/1900confirm04/02/2001
6aaaaacvx3a2105/01/2001gdp605/02/200108/02/1900gxhdhd05/01/2023
7aaaaacvx3a2106/01/2001gdp706/02/200109/02/1900confirm06/02/2001
8aaaaagjh3a2107/01/2001gdp807/02/200110/02/1900gfhgxhgx07/01/2023
9aaaaavcx3a2108/01/2001gdp908/02/200111/02/1900confirm08/02/2001
10aaaaahf3a2109/01/2001gdp1009/02/200112/02/1900"confirm"09/01/2023
11aaaaafdgfd3a2110/01/2001gdp1110/02/200113/02/1900hgfhxhgx10/01/2023
12aaaaahgfh3a2111/01/2001gdp1211/02/200114/02/1900hgxhgxhgx11/01/2023
13aaaaahjh3a2112/01/2001gdp1312/02/200115/02/1900gxhhg12/01/2023
14aaaaao3a2113/01/2001gdp1413/02/200116/02/1900confirm13/02/2001
15aaaaak3a2114/01/2001gdp1514/02/200117/02/1900confirm14/02/2001
order book
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top