Using activecell.row

AndrewMB

Board Regular
Joined
Feb 3, 2008
Messages
75
Guys,

I have a spreadsheet setup for someone at work. They used to have to enter all sorts of details everytime we shipped out a product. Now they just enter the cust. ord. no. and using vlookup the rest is filled in for them.

A new customer has come along and they don't use seperate order numbers per part, so booking out those parts doesn't suit the sheet. All other customers do so I don't want to change how the sheet works throughout.

What I wanted to do, was when it came to shipping out product on this new customer, was have a button at the side of the sheet which when pressed would find the active cell, and enter the new lookup formulas on this row only, working off the part number instead of the cust. ord. no.

This would allow the person to enter the part number and still have the details fill in automatically. But then on the next row, for our usual customers the sheet wouldnt have changed.

The row will be different each time, so i tried to get vba to add the below formula into column D at which ever row the active cell was.

Range("D" & activecell.row & ").Formula = "=IF($E & activecell.row=0," ",VLOOKUP($E & activecell.row,$AD$7:$AH$268,5,FALSE))"
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Range("D" & ActiveCell.Row).Formula = "=IF($E" & ActiveCell.Row & "=0,"""",VLOOKUP($E" & ActiveCell.Row & ",$AD$7:$AH$268,5,FALSE))"
 

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,774
Members
413,934
Latest member
austinb

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
Top