inserting formulas to each sheet, based on cell

[cav~firez22]

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

 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

 

[Scott Huish]

OK, first the 'Do Nothing part is not necessary.

Just check like this

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]

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]

How about:

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]

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

 

[cav~firez22]

n/m, got it..


Thanks for this.. it will be a great help