Wrong in refering to workbook

[kin]

Hello

Sub Macro4()


Columns("A:A").Select
Range("A16").Activate
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[6],'THEMATERIALSBROKEN'Sheet1!R1C1:R250C2,2,FALSE)"
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:E300"), Type:=xlFillDefault
Range("A1:E300").Select
Selection.AutoFill Destination:=Range("A1:E300"), Type:=xlFillDefault
Range("A1:E300").Select
Selection.NumberFormat = "0"
End Sub


I dont know how to refer to "=VLOOKUP(RC[6],'THEMATERIALSBROKEN'Sheet1

THEMATERIALSBROKEN is another workbook with the vlookup table

 

[VoG]

Try

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[6],'[THEMATERIALSBROKEN]Sheet1'!R1C1:R250C2,2,FALSE)"

 

[kin]

ok now but when even if I have the MATERIALSBROKEN open

and run the code another window opens which says UPDATE VALUES: the materialsbroken

while I d like to use the code to make a fast vlookup and not open this window

can you help me??

thanks

 

[VoG]

I'm not sure if this will work but try

Application.DisplayAlerts = False
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[6],'[THEMATERIALSBROKEN]Sheet1'!R1C1:R250C2,2,FALSE)"
Application.DisplayAlerts = True

 

[kin]

a message saying run time 1004 error

Autofil method of range class failed

WHat is this ?

 

[VoG]

Try

Application.DisplayAlerts = False
With Range("A1:E300")
    .FormulaR1C1 = "=VLOOKUP(RC[6],'[THEMATERIALSBROKEN]Sheet1'!R1C1:R250C2,2,FALSE)"
    .NumberFormat = "0"
End With
Application.DisplayAlerts = True

 

[kin]

now it makes the autofill but stil the same message and then gets yellow this one

Selection.AutoFill Destination:=Range("A1:E300"), Type:=xlFillDefault


the whole code is


Sub Macro4()



Columns("A:A").Select
Range("A1").Activate
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Range("A1").Select

Application.DisplayAlerts = False
With Range("A1:E300")
.FormulaR1C1 = "=VLOOKUP(RC[6],'[THEMATERIALSBROKEN]Sheet1'!R1C1:R250C2,2,FALSE)"
.NumberFormat = "0"
End With
Application.DisplayAlerts = True


'Application.DisplayAlerts = False
'ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[6],'[THEMATERIALSBROKEN]Sheet1'!R1C1:R250C2,2,FALSE)"
'Application.DisplayAlerts = True

Range("A1").Select
Selection.AutoFill Destination:=Range("A1:E300"), Type:=xlFillDefault
Range("A1:E300").Select
Selection.AutoFill Destination:=Range("A1:E300"), Type:=xlFillDefault
Range("A1:E300").Select
Selection.NumberFormat = "0"




End Sub

 

[VoG]

Delete this code

'Application.DisplayAlerts = False
'ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[6],'[THEMATERIALSBROKEN]Sheet1'!R1C1:R250C2,2,FALSE)"
'Application.DisplayAlerts = True

Range("A1").Select
Selection.AutoFill Destination:=Range("A1:E300"), Type:=xlFillDefault
Range("A1:E300").Select
Selection.AutoFill Destination:=Range("A1:E300"), Type:=xlFillDefault
Range("A1:E300").Select
Selection.NumberFormat = "0"

 

[kin]

ok now

 

[kin]

no now the values are not returned at all