lookup vba
 

I am new to macros and trying to make things as simple as possible, I have
this code which enters a formula into the cell to do a look up but the cell
then needs to be left unprotected but i get the error bit in the corner of
the cell. How could i do this in VBA code?

Thank you


Sub AutoFillSG()
ActiveCell.FormulaR1C1 = _

"=IF(RC[-2]="""","""",IF(R[-13]C[-2]=""WBM"",LOOKUP(R[11]C[-2],Densities!C[-5],Densities!C[-4]),LOOKUP(RC[-2],Densities!C[-1],Densities!C)))"
Range("H21").Select
Selection.AutoFill Destination:=Range("H21:H39"), Type:=xlFillDefault
Range("H21:H39").Select
Range("H21").Select
End Sub

lookup vba
 

I wasdn't getting any errors with excel 2003. I simplified the macro and
still din't have any problems.

Sub AutoFillSG()
Range("H21").FormulaR1C1 = _
"=IF(RC[-2]="""",""""," & _
"IF(R[-13]C[-2]=""WBM""," & _
"LOOKUP(R[11]C[-2],Densities!C[-5],Densities!C[-4])," & _
"LOOKUP(RC[-2],Densities!C[-1],Densities!C)))"

Range("H21").AutoFill Destination:=Range("H21:H39"), _
Type:=xlFillDefault
End Sub

"Miree" wrote:

I am new to macros and trying to make things as simple as possible, I have
this code which enters a formula into the cell to do a look up but the cell
then needs to be left unprotected but i get the error bit in the corner of
the cell. How could i do this in VBA code?

Thank you


Sub AutoFillSG()
ActiveCell.FormulaR1C1 = _

"=IF(RC[-2]="""","""",IF(R[-13]C[-2]=""WBM"",LOOKUP(R[11]C[-2],Densities!C[-5],Densities!C[-4]),LOOKUP(RC[-2],Densities!C[-1],Densities!C)))"
Range("H21").Select
Selection.AutoFill Destination:=Range("H21:H39"), Type:=xlFillDefault
Range("H21:H39").Select
Range("H21").Select
End Sub

lookup vba
 

You probably need to adjust the error checking, try with just the Unlocked
first:
Sub Err_Check()
With Application.ErrorCheckingOptions
..TextDate = False
..NumberAsText = False
..UnlockedFormulaCells = False
End With
End Sub
__________________
Regards,
The Code Cage Team
http://www.thecodecage.com/forumz/

"Miree" wrote:

I am new to macros and trying to make things as simple as possible, I have
this code which enters a formula into the cell to do a look up but the cell
then needs to be left unprotected but i get the error bit in the corner of
the cell. How could i do this in VBA code?

Thank you


Sub AutoFillSG()
ActiveCell.FormulaR1C1 = _

"=IF(RC[-2]="""","""",IF(R[-13]C[-2]=""WBM"",LOOKUP(R[11]C[-2],Densities!C[-5],Densities!C[-4]),LOOKUP(RC[-2],Densities!C[-1],Densities!C)))"
Range("H21").Select
Selection.AutoFill Destination:=Range("H21:H39"), Type:=xlFillDefault
Range("H21:H39").Select
Range("H21").Select
End Sub