VBA macro: insert string value in the same row (number) after finding column header

[TommasoP11]

Hi I was wondering if someone can help me with a macro. I am creating a schedule in excel and I need to insert a specific string value (timing) to a column in the same sheet that match a specific person that comes from a drop down list. Basically I would like to create a system like this with a macro:

	A	B	C	D	E	F	G	H	I
1​	Date	8.00-17.00	9.00-13.00	14.00-17.00		Spiderman	Hulk	Batman	Robin
2​	01/08/2021​	Spiderman	Hulk			8.00-17.00	9.00-13.00
3​	02/08/2021​	Hulk		Robin			8.00-17.00		14.00-17.00
4​	03/08/2021​	Batman						8.00-17.00
5​	04/08/2021​	Robin		Spiderman		14.00-17.00			8.00-17.00

I would like to assign to every member his time in the Range F:I by inserting their name in range A:D (source range). The two ranges are in the same row. Thanks a lot for your help very appreciated. If not clear please feel free to ask me.

 

[Snakehips]

@TommasoP11 Try this.

MRXLMAY21.xlsm
	A	B	C	D	E	F	G	H	I
1	Date	8.00-17.00	09.00-13.00	14.00-17.00		Spiderman	Hulk	Batman	Robin
2	01/08/2021	Spiderman	Hulk			8.00-17.00	09.00-13.00
3	02/08/2021	Hulk		Robin			8.00-17.00		14.00-17.00
4	03/08/2021	Batman						8.00-17.00
5	04/08/2021	Robin		Spiderman		14.00-17.00			8.00-17.00
CASH SHEET

Cell Formulas
Range	Formula
F2:I5	F2	=IFERROR(INDEX($B$1:$D$1,1,MATCH(F$1,$B2:$D2,0)),"")

Hope that helps.

EDIT: Sorry, just realised you were asking for a macro. Is a macro still required?

 

[TommasoP11]

@TommasoP11 Try this.

MRXLMAY21.xlsm
	A	B	C	D	E	F	G	H	I
1	Date	8.00-17.00	09.00-13.00	14.00-17.00		Spiderman	Hulk	Batman	Robin
2	01/08/2021	Spiderman	Hulk			8.00-17.00	09.00-13.00
3	02/08/2021	Hulk		Robin			8.00-17.00		14.00-17.00
4	03/08/2021	Batman						8.00-17.00
5	04/08/2021	Robin		Spiderman		14.00-17.00			8.00-17.00
CASH SHEET

Cell Formulas
Range	Formula
F2:I5	F2	=IFERROR(INDEX($B$1:$D$1,1,MATCH(F$1,$B2:$D2,0)),"")

Hope that helps.

EDIT: Sorry, just realised you were asking for a macro. Is a macro still required?

Thanks a lot for your promt reply, really appreciated. If it possible to have in macro that would be great, if not don't worry. Many thanks

 

[Snakehips]

Maybe this, pasted into the code pane for that workshhet.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Not Target.Column > 1 And Target.Column < 5 Then Exit Sub
If Not (Target.row > 1 And Target.row < Range("A" & Rows.Count).End(xlUp).row + 1) Then Exit Sub

Application.EnableEvents = False
Range("F" & Target.row & ":I" & Target.row).FormulaR1C1 = "=IFERROR(INDEX(R1C2:R1C4,1,MATCH(R1C,RC2:RC4,0)),"""")"
Range("F" & Target.row & ":I" & Target.row).Value = Range("F" & Target.row & ":I" & Target.row).Value
Application.EnableEvents = True

End Sub

 

[TommasoP11]

Maybe this, pasted into the code pane for that workshhet.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Not Target.Column > 1 And Target.Column < 5 Then Exit Sub
If Not (Target.row > 1 And Target.row < Range("A" & Rows.Count).End(xlUp).row + 1) Then Exit Sub

Application.EnableEvents = False
Range("F" & Target.row & ":I" & Target.row).FormulaR1C1 = "=IFERROR(INDEX(R1C2:R1C4,1,MATCH(R1C,RC2:RC4,0)),"""")"
Range("F" & Target.row & ":I" & Target.row).Value = Range("F" & Target.row & ":I" & Target.row).Value
Application.EnableEvents = True

End Sub

Thanks a lot, it work smashing! thanks for your time really appreciated and apologies if my request was not so clear. Have a good evening.

 

[TommasoP11]

Hi, it's me again. Please I would like to ask you another advice. If I want to start this code from Row n.2 what settings do I need to change in your code? Sorry for the silly question.

 

[Snakehips]

Hi, it's me again. Please I would like to ask you another advice. If I want to start this code from Row n.2 what settings do I need to change in your code? Sorry for the silly question.

Hi, If you are still inputting in columns B,C,D but want output to N,O,P,Q rather than F,G,H,I then try

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Not Target.Column > 1 And Target.Column < 5 Then Exit Sub
If Not (Target.row > 1 And Target.row < Range("A" & Rows.Count).End(xlUp).row + 1) Then Exit Sub

Application.EnableEvents = False
Range("N" & Target.row & ":Q" & Target.row).FormulaR1C1 = "=IFERROR(INDEX(R1C2:R1C4,1,MATCH(R1C,RC2:RC4,0)),"""")"
Range("N" & Target.row & ":Q" & Target.row).Value = Range("N" & Target.row & ":Q" & Target.row).Value
Application.EnableEvents = True

End Sub

 

[TommasoP11]

VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Not Target.Column > 1 And Target.Column < 5 Then Exit Sub
If Not (Target.row > 1 And Target.row < Range("A" & Rows.Count).End(xlUp).row + 1) Then Exit Sub

Application.EnableEvents = False
Range("N" & Target.row & ":Q" & Target.row).FormulaR1C1 = "=IFERROR(INDEX(R1C2:R1C4,1,MATCH(R1C,RC2:RC4,0)),"""")"
Range("N" & Target.row & ":Q" & Target.row).Value = Range("N" & Target.row & ":Q" & Target.row).Value
Application.EnableEvents = True

End Sub

Thank you so much for your reply Snakehips. I would like to put either the input or the output in Row. n. 2. Basically having all my headers in Row. n. 2 rather than Row. 1. Thanks a lot for your assistance really much appreciated.