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

MatthewField

New Member
Joined
Jan 5, 2018
Messages
8
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,
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Hi & welcome to the board.
How about this
Code:
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
Try
Code:
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.
 
Upvote 0
Try
Code:
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.
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,123
Members
452,381
Latest member
Nova88

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