Copying formula to a cell based on another cell value being added

[MatthewField]

Firstly, i'm new to Macro's and to Mr Excel so apologies if this question has been answered elsewhere within the forums but i'm hoping someone can help and this is an easy fix.

I'm trying to automatically populate a VLOOKUP formula in each cell of Column C but only when there is a value entered in the adjacent cell in Column B.

I'm aware of the easy approach of copying and pasting the formula down the entire sheet, but this causes a problem when printing, as even though there is no values there it knows there is a formula waiting in each cell of Column C which then means it prints hundreds upon hundreds of pages. I'm aware of why this is happening to stop that, other users of the spreadsheet won't be.

My spreadsheet looks like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Item Type[/TD]
[TD]Printing Process[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]05/01/2018[/TD]
[TD]Covers[/TD]
[TD]=VLOOKUP(B2, Fields!$B$5:$C$24, 2, FALSE)[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The VLOOKUP a separate table within a different sheet but the same workbook to select the appropriate printing process for the selected item type. This formula works perfectly but I would like for it to automatically enter it on to the next cell down only when someone has selected an item type (Column B)

I would greatly appreciate any advice and help if you can.

Many thanks,

 

[Fluff]

Hi & welcome to the board.
How about this

Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.CountLarge > 1 Then Exit Sub
   If Not Target.Column = 2 Then Exit Sub
   Target.Offset(, 1).FormulaR1C1 = "=VLOOKUP(rc[-1], Ranking!r5c2:r24c3, 2, FALSE)"
End Sub

This needs to go in the sheet module.
Right click on the sheet tab > View Code > Paste the above into the code window that opens up.
Every time an entry is made in col B the formula will go into col C.
For this to work col B must be entered manually.

 

[MatthewField]

Thank you for your reply Fluff.

Though this works, it's not entirely doing what I want it to do.

To explain, Column B is a drop down list of items for the user to select a particular printing item. I need this in place to prevent a user from adding in a variation of the same item type and so that the information is consistent and so I can report it on later on.

What's happening with this code is, every-time one particular item is selected, the item then disappears from the drop down list.

Would you possibly know how to make this work?

Many thanks in advance.

 

[Fluff]

How is the dropdown list populated?

 

[MatthewField]

Amazing!!! I've managed to get it to work, I think the drop down list was not set up correctly.

Many thanks for your help

 

[MatthewField]

How is the dropdown list populated?

Hi Fluff,

I managed to sort it, I think it was an issue with the drop down list. One thing I can't figure out now is having two pieces of code on the same sheet.

To explain, I need the exact same process but for two other columns - G & H.

Can you possibly help?

 

[Fluff]

Try

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 2 Then
      Target.Offset(, 1).FormulaR1C1 = "=VLOOKUP(rc[-1], Ranking!r5c2:r24c3, 2, FALSE)"
   ElseIf Target.Column = 7 Then
      Target.Offset(, 1).FormulaR1C1 = "=VLOOKUP(rc[-1], Ranking!r5c2:r24c3, 2, FALSE)"
   End If
Application.EnableEvents = True

End Sub

changing the 2nd formula as required.

 

[MatthewField]

Try

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 2 Then
      Target.Offset(, 1).FormulaR1C1 = "=VLOOKUP(rc[-1], Ranking!r5c2:r24c3, 2, FALSE)"
   ElseIf Target.Column = 7 Then
      Target.Offset(, 1).FormulaR1C1 = "=VLOOKUP(rc[-1], Ranking!r5c2:r24c3, 2, FALSE)"
   End If
Application.EnableEvents = True

End Sub

changing the 2nd formula as required.

Amazing, thank you so much for your help, this works perfectly.

 

[Fluff]

Glad to help & thanks for the feedback