Macro/VBA to add text from another cell into current cell when I type a certain value.

[mrmcmillanrevis]

I am creating a schedule within excel with days along the top, time down the left and activities populated in the table. The column headers/dates are not stored as dates, but as unique values based on our calender (e.g. Week 1 Mon, Week 1 Tue). I would like something that means if I type in specific text (in this case the word 'end'), it then automatically adds in the value from the column header in the same cell.

So if for example cell A1 (column header for column A) says "Week 1 Mon", then if I type the word 'end' into any cell underneath this after I type 'end' and press enter it will be converted to 'end Week 1 Mon' (i.e. the text from the column header has been automatically added to the cell).

I'd also like to be able to do this for other values. For example, if I type 'Lunch' in the column that has "Week 1 Tue" at the top it will then convert 'lunch' into 'lunch Week 1 Tue".

I'm not very good with VBA/Macros but I suspect the action if something like If 'cell value' = 'desired text' then change 'desired text' to 'desired text & column header'.

Hope someone can help!

 

[Joe4]

Assuming that your first row where you are entering data is row 4, then you can enter this formula in cell F4 and copy down for all rows:

=IF(D5<>"",D5 & " " & $F$1,"")

The only thing you have not explained is where the "BO" at the end of those entries comes from.
If it is hard-coded, and will always be "BO", then use this formula:

=IF(D5<>"",D5 & " " & $F$1 & " BO","")

 

[shinigamilight]

Sub replacerr2()
                Dim k As Integer
                Dim lr As Long
                
                lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlWhole, xlByRows, xlPrevious, False).Row
                
                For k = 4 To lr
                        If Cells(k, 4) <> "" Then
                                Cells(k, 4) = Cells(k, 4) & " " & Cells(1, 4) & " BO"
                        End If
                Next k
                
                ActiveSheet.UsedRange.EntireColumn.AutoFit


End Sub

 

[Joe4]

Sorry, I gave you a non-VBA solution (seems like VBA isn't really necessary here, as simple formulas should be able to handle it).

 

[shinigamilight]

Sub replacerr2()
                Dim k As Integer
                Dim lr As Long
                
                lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlWhole, xlByRows, xlPrevious, False).Row
                
                For k = 4 To lr
                        If Cells(k, 4) = "Break" Or Cells(k, 4) = "Lunch" Or Cells(k, 4) = "End" Then
                                Cells(k, 4) = Cells(k, 4) & " " & Cells(1, 4) & " BO"
                        End If
                Next k
                
                ActiveSheet.UsedRange.EntireColumn.AutoFit


End Sub

This one is hardcoded to break, lunch and end only

 

[mrmcmillanrevis]

Assuming that your first row where you are entering data is row 4, then you can enter this formula in cell F4 and copy down for all rows:

=IF(D5<>"",D5 & " " & $F$1,"")

The only thing you have not explained is where the "BO" at the end of those entries comes from.
If it is hard-coded, and will always be "BO", then use this formula:

=IF(D5<>"",D5 & " " & $F$1 & " BO","")

Sorry - I realise the screenshot is a bit confusing. I'm not looking for a solution where the result in D column is then inputted in to the F column (that would just require a basic formula). What I want is that if I type in what I have in column D, it will end up looking like column F.

The various letters at the top (e.g. the BO, etc) will be different for different things. The point is I want whatever text is in the column header to be automatically inserted into the cell where the word 'end' or 'lunch' or whatever is typed.

So I'm pretty sure a VBA/Macro solution is needed.

 

[mrmcmillanrevis]

Sub replacerr2()
                Dim k As Integer
                Dim lr As Long
               
                lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlWhole, xlByRows, xlPrevious, False).Row
               
                For k = 4 To lr
                        If Cells(k, 4) = "Break" Or Cells(k, 4) = "Lunch" Or Cells(k, 4) = "End" Then
                                Cells(k, 4) = Cells(k, 4) & " " & Cells(1, 4) & " BO"
                        End If
                Next k
               
                ActiveSheet.UsedRange.EntireColumn.AutoFit


End Sub

This one is hardcoded to break, lunch and end only

Hi, I tried adding this code, but couldn't seem to get it working. I should have also pointed out that I don't want this macro/VBA to run for the whole worksheet. There is a section of the sheet which is for the schedule and this should only apply to that section.

I think I need to learn more about VBA to understand what is happening here.

 

[Joe4]

I'm not looking for a solution where the result in D column is then inputted in to the F column (that would just require a basic formula). What I want is that if I type in what I have in column D, it will end up looking like column F.

That is exactly what the formula does. If you populate column F with that formula, the value in column F will remain blank until values appear in column D.
So I really don't see any need for VBA. The formula option seems like it will do exactly what you want.
Did you try it out?

VBA is great, but there is no need to use it if it is not needed (when a formula option does exactly what you need without VBA).

 

[shinigamilight]

Hi, I tried adding this code, but couldn't seem to get it working. I should have also pointed out that I don't want this macro/VBA to run for the whole worksheet. There is a section of the sheet which is for the schedule and this should only apply to that section.

I think I need to learn more about VBA to understand what is happening here.

you need to provide sample data then, use xl2bb extension

 

[mrmcmillanrevis]

I eventually found a fairly straightforward solution to this.

Sub DayName()

' Short Cut CTRL + SHIFT + D

Dim CellText As String
Dim ColumnHeader As String

'Identify the RowHeader and Define in Memory
ColumnHeader = Cells(1, ActiveCell.Column).Value

'Identify the Active Cell Value and Define in Memory
CellText = ActiveCell.Value

'Combine The CellText with the ColumnHeader in the Active Cell
ActiveCell.Value = CellText & " " & ColumnHeader


End Sub