Re: Macro Runs only from VBA Editor. Returns 400 Error when attempt to run from Exce
Yes. The source spreadsheet was open.
Posts by mdetroyer
-
-
I have a macro that inserts formulas, fills down, and copies and pastes as value.
It executes perfectly when run form inside the VBA editor.
I receive a 400 error when I try to run from Excel.
Here is the code.
Code
Display MoreSub InsertFormulas() Dim lrow As Long lrow = Range("a8").End(xlDown).Row Range("i8").Formula = "=iferror(VLOOKUP($A8,'[Walmart Spring D31 Item Data.xlsx]Data'!$A$25:$Ae$10000,11,FALSE),0)" Range("i8").AutoFill Destination:=Range("i8:i" & lrow) Range("i8:i" & lrow).Copy Range("i8").PasteSpecial xlPasteValues Range("j8").Formula = "=iferror(VLOOKUP($A8,'[Walmart Spring D31 Item Data.xlsx]Data'!$A$27:$Ae$10000,12,FALSE),0)" Range("j8").AutoFill Destination:=Range("j8:j" & lrow) Range("j8:j" & lrow).Copy Range("j8").PasteSpecial xlPasteValues Range("l8").Formula = "=iferror(VLOOKUP($A8,'[Walmart Spring D31 Item Data.xlsx]Data'!$A$27:$Ae$10000,13,FALSE),0)" Range("l8").AutoFill Destination:=Range("l8:l" & lrow) Range("l8:l" & lrow).Copy Range("l8").PasteSpecial xlPasteValues Range("p8").Formula = "=iferror(VLOOKUP($A8,'[Walmart Spring D31 Item Data.xlsx]Data'!$A$27:$Ae$10000,14,FALSE),0)" Range("p8").AutoFill Destination:=Range("p8:p" & lrow) Range("p8:p" & lrow).Copy Range("p8").PasteSpecial xlPasteValues Range("q8").Formula = "=iferror(VLOOKUP($A8,'[Walmart Spring D31 Item Data.xlsx]Data'!$A$27:$Ae$10000,19,FALSE),0)" Range("q8").AutoFill Destination:=Range("q8:q" & lrow) Range("q8:q" & lrow).Copy Range("q8").PasteSpecial xlPasteValues Range("s8").Formula = "=iferror(VLOOKUP($A8,'[Walmart Spring D31 Item Data.xlsx]Data'!$A$27:$Ae$10000,20,FALSE),0)" Range("s8").AutoFill Destination:=Range("s8:s" & lrow) Range("s8:s" & lrow).Copy Range("s8").PasteSpecial xlPasteValues Range("w8").Formula = "=iferror(VLOOKUP($A8,'[Walmart Spring D31 Item Data.xlsx]Data'!$A$27:$Ae$10000,26,FALSE),0)" Range("w8").AutoFill Destination:=Range("w8:w" & lrow) Range("w8:w" & lrow).Copy Range("w8").PasteSpecial xlPasteValues Range("y8").Formula = "=iferror(VLOOKUP($A8,'[Walmart Spring D31 Item Data.xlsx]Data'!$A$27:$Ae$10000,27,FALSE),0)" Range("y8").AutoFill Destination:=Range("y8:y" & lrow) Range("y8:y" & lrow).Copy Range("y8").PasteSpecial xlPasteValues Range("ad8").Formula = "=iferror(VLOOKUP($A8,'[Walmart Spring D31 Item Data.xlsx]Data'!$A$27:$Ae$10000,31,FALSE),0)" Range("ad8").AutoFill Destination:=Range("ad8:ad" & lrow) Range("ad8:ad" & lrow).Copy Range("ad8").PasteSpecial xlPasteValues End Sub