Add a word or sentence to every blank cell

A

atditiljazi

New Member
Joined
Nov 22, 2022
Messages
41
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I'm after a macro to add "is it still on track" in every blank cell in column L. I am really struggling so I am hoping for some help.

I am also after a macro that will fill in every blank cell in column M with the data in column H

Many thanks
 
Sort by date Sort by votes
Which column has all the rows are filled? Like an index number or an account number?

Also what is the name of your sheet?
 
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Flashbond said:
Which column has all the rows are filled? Like an index number or an account number?

Also what is the name of your sheet?
Click to expand...
sorry Flashbond, i know you tried helping on my other question. my colleague gave me the wrong details!..... i will start again.

i would like to copy all the data in column N (date) and paste it into Column y where there is a blank cell

i would also like the macro to fill in every blank cell in column x with "still on track"

i currently have a macro in the spreadsheet to send certain columns to another workbook so if the new macro can be added into the same macro, that would be great.

current macro is,

VBA Code: 
Sub copycolumns()

'workbook and sheet declarations
Dim tWB As Workbook: Set tWB = ThisWorkbook
Dim tWS As Worksheet: Set tWS = tWB.Sheets("raw materials")
Dim xWB As Workbook: Set xWB = Workbooks("order book macro.xlsm")
Dim xWS As Worksheet: Set xWS = xWB.Sheets("order book")

'last row declarations
Dim tLRow As Long: tLRow = tWS.Cells(tWS.Cells.Rows.Count, 1).End(xlUp).Row
Dim xLRow As Long: xLRow = xWS.Cells(xWS.Cells.Rows.Count, 1).End(xlUp).Row

'copy columns
With tWS
.Range("C2:C" & tLRow).Copy xWS.Cells(xLRow + 1, 1)
.Range("H2:H" & tLRow).Copy xWS.Cells(xLRow + 1, 2)
.Range("D2:D" & tLRow).Copy xWS.Cells(xLRow + 1, 3)
.Range("E2:E" & tLRow).Copy xWS.Cells(xLRow + 1, 4)
.Range("J2:J" & tLRow).Copy xWS.Cells(xLRow + 1, 5)
.Range("L2:L" & tLRow).Copy xWS.Cells(xLRow + 1, 6)
.Range("M2:M" & tLRow).Copy xWS.Cells(xLRow + 1, 7)
.Range("N2:N" & tLRow).Copy xWS.Cells(xLRow + 1, 8)
.Range("W2:W" & tLRow).Copy xWS.Cells(xLRow + 1, 9)
.Range("W2:W" & tLRow).Copy xWS.Cells(xLRow + 1, 10)
.Range("P2:P" & tLRow).Copy xWS.Cells(xLRow + 1, 11)
.Range("X2:X" & tLRow).Copy xWS.Cells(xLRow + 1, 12)
.Range("y2:y" & tLRow).Copy xWS.Cells(xLRow + 1, 13)
End With

End Sub


sorry to be a pain. i appreciate your help
 
Last edited by a moderator:
Upvote 0
Maybe

VBA Code: 
Sub CopyColumns()

'workbook and sheet declarations
Dim tWB As Workbook: Set tWB = ThisWorkbook
Dim tWS As Worksheet: Set tWS = tWB.Sheets("raw materials")
Dim xWB As Workbook: Set xWB = Workbooks("order book macro.xlsm")
Dim xWS As Worksheet: Set xWS = xWB.Sheets("order book")

'last row declarations
Dim tLRow As Long: tLRow = tWS.Cells(tWS.Cells.Rows.Count, 1).End(xlUp).Row
Dim xLRow As Long: xLRow = xWS.Cells(xWS.Cells.Rows.Count, 1).End(xlUp).Row

'copy columns
With tWS
  .Range("C2:C" & tLRow).Copy xWS.Cells(xLRow + 1, 1)
  .Range("H2:H" & tLRow).Copy xWS.Cells(xLRow + 1, 2)
  .Range("D2:E" & tLRow).Copy xWS.Cells(xLRow + 1, 3)
'  .Range("D2:D" & tLRow).Copy xWS.Cells(xLRow + 1, 3)
'  .Range("E2:E" & tLRow).Copy xWS.Cells(xLRow + 1, 4)
  .Range("J2:J" & tLRow).Copy xWS.Cells(xLRow + 1, 5)
  .Range("L2:N" & tLRow).Copy xWS.Cells(xLRow + 1, 6)
'  .Range("L2:L" & tLRow).Copy xWS.Cells(xLRow + 1, 6)
'  .Range("M2:M" & tLRow).Copy xWS.Cells(xLRow + 1, 7)
'  .Range("N2:N" & tLRow).Copy xWS.Cells(xLRow + 1, 8)
  .Range("W2:W" & tLRow).Copy xWS.Cells(xLRow + 1, 9)
  .Range("W2:W" & tLRow).Copy xWS.Cells(xLRow + 1, 10)    'same conten to 2 different columns?
  .Range("P2:P" & tLRow).Copy xWS.Cells(xLRow + 1, 11)
  .Range("X2:Y" & tLRow).Copy xWS.Cells(xLRow + 1, 12)
'  .Range("X2:X" & tLRow).Copy xWS.Cells(xLRow + 1, 12)
'  .Range("y2:y" & tLRow).Copy xWS.Cells(xLRow + 1, 13)
End With

'change worksheet to suit
With xWS
  With .Range("X2:X" & xLRow)
    .Value = Evaluate("=IF(" & .Address & "="""",  ""still on track"" ," & .Address & ")")
  End With
  With .Range("Y2:Y" & xLRow)
    .Value = Evaluate("=IF(" & .Address & "=""""" & "," & .Offset(0, -11).Address & "," & .Address & ")")
  End With
End With

End Sub

Ciao,
Holger
 
Upvote 0
xWB is a workbook, xWS should be the sheet in question

Holger
 
Upvote 0
HaHoBe said:
Maybe

VBA Code: 
Sub CopyColumns()

'workbook and sheet declarations
Dim tWB As Workbook: Set tWB = ThisWorkbook
Dim tWS As Worksheet: Set tWS = tWB.Sheets("raw materials")
Dim xWB As Workbook: Set xWB = Workbooks("order book macro.xlsm")
Dim xWS As Worksheet: Set xWS = xWB.Sheets("order book")

'last row declarations
Dim tLRow As Long: tLRow = tWS.Cells(tWS.Cells.Rows.Count, 1).End(xlUp).Row
Dim xLRow As Long: xLRow = xWS.Cells(xWS.Cells.Rows.Count, 1).End(xlUp).Row

'copy columns
With tWS
  .Range("C2:C" & tLRow).Copy xWS.Cells(xLRow + 1, 1)
  .Range("H2:H" & tLRow).Copy xWS.Cells(xLRow + 1, 2)
  .Range("D2:E" & tLRow).Copy xWS.Cells(xLRow + 1, 3)
'  .Range("D2:D" & tLRow).Copy xWS.Cells(xLRow + 1, 3)
'  .Range("E2:E" & tLRow).Copy xWS.Cells(xLRow + 1, 4)
  .Range("J2:J" & tLRow).Copy xWS.Cells(xLRow + 1, 5)
  .Range("L2:N" & tLRow).Copy xWS.Cells(xLRow + 1, 6)
'  .Range("L2:L" & tLRow).Copy xWS.Cells(xLRow + 1, 6)
'  .Range("M2:M" & tLRow).Copy xWS.Cells(xLRow + 1, 7)
'  .Range("N2:N" & tLRow).Copy xWS.Cells(xLRow + 1, 8)
  .Range("W2:W" & tLRow).Copy xWS.Cells(xLRow + 1, 9)
  .Range("W2:W" & tLRow).Copy xWS.Cells(xLRow + 1, 10)    'same conten to 2 different columns?
  .Range("P2:P" & tLRow).Copy xWS.Cells(xLRow + 1, 11)
  .Range("X2:Y" & tLRow).Copy xWS.Cells(xLRow + 1, 12)
'  .Range("X2:X" & tLRow).Copy xWS.Cells(xLRow + 1, 12)
'  .Range("y2:y" & tLRow).Copy xWS.Cells(xLRow + 1, 13)
End With

'change worksheet to suit
With xWS
  With .Range("X2:X" & xLRow)
    .Value = Evaluate("=IF(" & .Address & "="""",  ""still on track"" ," & .Address & ")")
  End With
  With .Range("Y2:Y" & xLRow)
    .Value = Evaluate("=IF(" & .Address & "=""""" & "," & .Offset(0, -11).Address & "," & .Address & ")")
  End With
End With

End Sub

Ciao,
Holger
Click to expand...
hi,

regarding the same content, .Range("W2:W" & tLRow).Copy xWS.Cells(xLRow + 1, 9) should of been .Range("V2:V" & tLRow).Copy xWS.Cells(xLRow + 1, 9) which i have corrected in your macro you have kindly provided.

The information from TWS still didn't go into column L and M in XWS. i have uploaded an image of the worksheet with part of column L and M with the blank cells after the data was copied across from



1674132056444.png
 
Upvote 0
Hi,

you have updated the columns in #12, now you refer to the columns stated in the opening post - if you ever have made up your mind what columns to work on I think somebody will provide a suiting code.

Holger
 
Upvote 0
You must log in or register to reply here.

Similar threads

Q
  • Question
Automate missing cell value report
Replies
2
Views
115
mumps
mumps
M
  • Solved
Copy the last sheet from file to another with ignore empty whole column
Replies
7
Views
171
mumps
mumps
S
  • Question
Macro to add sum total to track percentages
Replies
0
Views
106
spenserallen7
S
B
  • Solved
vba code to give next Sunday date for pasted data
Replies
2
Views
188
bh24524
B
W
  • Solved
Conditional formatting - formula for blank or specific text
Replies
3
Views
296
Joe4
Joe4

Forum statistics

Threads
1,215,043
Messages
6,122,825
Members
449,096
Latest member
Erald

Share this page

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