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



## mrmcmillanrevis (Dec 15, 2022)

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 (Dec 15, 2022)

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 (Dec 15, 2022)

```
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 (Dec 15, 2022)

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 (Dec 15, 2022)

```
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 (Dec 15, 2022)

Joe4 said:


> 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,"")
> ...


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 (Dec 15, 2022)

shinigamilight said:


> ```
> Sub replacerr2()
> Dim k As Integer
> Dim lr As Long
> ...


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 (Dec 15, 2022)

mrmcmillanrevis said:


> 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 (Dec 15, 2022)

mrmcmillanrevis said:


> 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


----------

