inserting formulas to each sheet, based on cell

cav~firez22

Well-known Member
Joined
Jun 21, 2006
Messages
543
Hi everyone,

Could someone help me get this code ....

Sub TESTTY()
Dim ws As Worksheet
'Loop through all worksheets
For Each ws In ActiveWorkbook.Worksheets
'Do this for all sheets except these sheets
If ws.Name = "namedranges" Then
'Do Nothing
Else
Code:
 start @ row 17. If there is data in Cell A, then in column L = VLOOKUP($c$13,MSTR,9,FALSE)
AND set column L to be data validation, list format.  range name is locnam
Column M = VLOOKUP($c$13,MSTR,10,FALSE)
AND set column M to be data validation, list format.  range name is deptnam
Column N = VLOOKUP($c$13,MSTR,11,FALSE)
AND set column N is to be data validation, list format.  range name is prodnam

If There is no value in A, then next sheet.

End If
Next ws

End Sub

Is that understandable?

If so, could someone please help me? i have been playing with the record, but its not working to my advantage.

Thanx much
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
OK, first the 'Do Nothing part is not necessary.

Just check like this

Code:
If ws.Name <> "namedrange" Then

then there is no need for an Else

but why are you using absolute references in the formulas? Do you want the same value in every cell it puts the formula in or do you want it to adjust?
 

cav~firez22

Well-known Member
Joined
Jun 21, 2006
Messages
543
It will be the same value as what ever is pulled from the vlookup formula. The validation list is there, in the event that one of the values needs to be changed.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
How about:

Code:
Sub test()
Dim ws As Worksheet, x As Long
For Each ws In ActiveWorkbook.Worksheets
    With ws
        If .Name <> "namedranges" Then
            x = .Range("A" & Rows.Count).End(xlUp).Row
                If x > 16 Then
                    .Range("L17:N" & Rows.Count).ClearContents
                    .Range("L17:L" & x).Formula = "=VLOOKUP($C$13,MSTR,9,0)"
                    .Range("M17:M" & x).Formula = "=VLOOKUP($C$13,MSTR,10,0)"
                    .Range("N17:N" & x).Formula = "=VLOOKUP($C$13,MSTR,11,0)"
                    .Range("L17:N" & Rows.Count).Validation.Delete
                    With .Range("L17:L" & x).Validation
                        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=locnam"
                        .InCellDropdown = True
                    End With
                    With .Range("M17:M" & x).Validation
                        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                            xlBetween, Formula1:="=deptnam"
                        .InCellDropdown = True
                    End With
                    With .Range("N17:N" & x).Validation
                        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                            xlBetween, Formula1:="=prodnam"
                        .InCellDropdown = True
                    End With
                End If
        End If
    End With
Next
End Sub
 

cav~firez22

Well-known Member
Joined
Jun 21, 2006
Messages
543
AWESOME!!!!

THANK YOU!!!!!!

one quick addition, if i were to want to add one more validation list for Column J.. what would i add?

range for list is descript
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,396
Members
414,235
Latest member
sucosama

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