Range.formula Vba Error 1004
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn
Excel Vba Error 1004 Application-defined Or Object-defined Error
more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users runtime error 1004 Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Run-time error '1004' when applying formula to Range.FormulaR1C1 in VBA up vote 1 down vote favorite I'm trying to apply a big nested formula to a range using the code below. Basically, if the value in cell A of the active row exists in the column A of another workbook and if the cell in column E of the active row is not empty, I want the active cell to display the cells to display the value of the equivalent cell in a separate workbook. This needs to be applied to several worksheets so I'm using the variables lrow (which is an int with the last row of the active worksheet in workbook#1) and tlrow (which is an int equal to the last row of the active worksheet in workbook#2). When I step through the sub, these variables both return the numbers I would expect them to. Likewise, this is inside of a for loop so I also use Worksheets(i).Name where I is an int. When I run the code, I get the run-time error "'1004': Application-defined or object-defined error". I'm assuming it's a syntax issue. Code: Range("B15:B" & lrow).FormulaR1C1 = _ "=IF(OR(RC1="""",RC5=""""),"""",IF(ISERROR(VLOOKUP(RC1,'[temp.xlsx]" & _ Worksheets(i).Name & _ "'!A15:D" & tlrow & ",3,FALSE)),""0"",VLOOKUP(RC1,'[temp.xlsx]" & _ Worksheets(i).Name & "'!A15:D" & tlrow & ",3,FALSE))))" excel vba excel-vba share|improve this question edited Feb 12 '15 at 0:00 L42 12.5k52143 asked Feb 11 '15 at 23:49 Inderbitzen 62 1 Is the error generated on the line you've given? Have you tried using a simple formula first (such as "=2+2", then building it up? –tospig Feb 11 '15 at 23:57 I think you've kinda mixed things up between R1C1 and A1 notation when you construct your formula. If you use R1C1 notation, it should be consistent throughout the formula. –L42 Feb 12 '15 at 0:01 add a comment| 3 Answers 3 active oldest votes up vote 0 down vote Try using this: Range("B15:B" & lrow).FormulaR1C1 = _ "=IF(OR(RC1="""",RC5=""""),"""",IF(ISERROR(VLOOKUP(RC1," & _ Worksheets(i).Range("A1:D" & lrow).Address(ReferenceStyle:=xlR1
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Excel vba error 1004 - insert a formula up vote 0 down vote favorite 1 i'm trying to execute these code in my excel sheet ActiveCell.Offset(0, 3).Formula = "=if(SUM(N" & i + 2 & ":N" & i + 5 & ")>0;MEDIAN(N" & i + 2 & ":N" & http://stackoverflow.com/questions/28466836/run-time-error-1004-when-applying-formula-to-range-formular1c1-in-vba i + 5 & ");0)" and i'm get an #1004 error with no more informations. Can anybody eyplain my failure? I hav some others formulars insert in the same way...thx EDIT: My Tables look like that This should be a projectmanagement tool - Breitband Delphi Method ;) So my code goes through all the rows and check in which column the descripton is (level 1,2,3,4). Next the code is adding the rows 8-12 for example.. here i can enter some informations for the project... and now my http://stackoverflow.com/questions/27972157/excel-vba-error-1004-insert-a-formula script should add the formula at column k-n. My code is not very nice (as my english :) ) - it is just a prototype.. This is my Loop i = 5 canSkip = False Do ' fist first the level If Not IsEmpty(Range("B" & i).Value) Then level = 1 If Not IsEmpty(Range("D" & i + 1)) Then ' ye we can - so skip this loop canSkip = True End If ElseIf Not IsEmpty(Range("D" & i).Value) Then level = 2 If Not IsEmpty(Range("F" & i + 1)) Then ' ye we can - so skip this loop canSkip = True End If ElseIf Not IsEmpty(Range("F" & i).Value) Then level = 3 If Not IsEmpty(Range("H" & i + 1)) Then ' ye we can - so skip this loop canSkip = True End If ElseIf Not IsEmpty(Range("H" & i).Value) Then level = 4 canSkip = False End If If canSkip = True Then i = i + 1 Else ' First insert some... and bang it to a group ' Insert Formula Range("K" & i).Activate ActiveCell.Formula = "=min(L" & i + 2 & ":L" & i + 5 & ")" ActiveCell.Offset(0, 1).Formula = "=max(L" & i + 2 & ":L" & i + 5 & ")" 'Range("T1").FormulaLocal = insertMedianFormula 'ActiveCell.Offset(0, 3).Formula = "=WENN(SUMME(N" & i + 2 & ":N" & i + 5 & ")>0;MITTELWERT(N" & i + 2 & ":N" & i + 5 & ");0)" Range("A" & i + 1).Activate For x = 1 To 5 ActiveCell.EntireRow.Insert If x = 5 Then If level = 1 Then Activ
Forums Excel Questions [VBA] .formula = "=" & variable - Error 1004 Results 1 to 3 of 3 [VBA] .formula = "=" & variable - Error 1004This is a discussion on http://www.mrexcel.com/forum/excel-questions/672334-%5Bvisual-basic-applications%5D-formula-%3D-%3D-variable-error-1004-a.html [VBA] .formula = "=" & variable - Error 1004 within the Excel Questions http://www.pcreview.co.uk/threads/excel-formula-error-1004-bug-mistake.961873/ forums, part of the Question Forums category; Hello, I'm trying to use the .formula function in VBA but I don't know how to use it with a ... LinkBack LinkBack URL About LinkBacks Bookmark & Share Digg this Thread!Add Thread to del.icio.usBookmark in TechnoratiTweet this thread Thread Tools Show Printable Version Display Linear error 1004 Mode Switch to Hybrid Mode Switch to Threaded Mode Nov 30th, 2012,10:01 PM #1 Uehara New Member Join Date Nov 2012 Posts 2 [VBA] .formula = "=" & variable - Error 1004 Hello, I'm trying to use the .formula function in VBA but I don't know how to use it with a variable following the "=" character... Here's my code: Code: Dim sheetlist As String Dim i vba error 1004 As Integer i = 2 While Range("B" & i).Value <> "" sheetlist = Range("B" & i).Value Range("C" & i).Formula = "=" & sheetlist & " '!$D$3 i = i + 1 Wend I got a "error 1004 : Application-defined or object-defined error". Thanks Last edited by Uehara; Nov 30th, 2012 at 10:04 PM. Share Share this post on Digg Del.icio.us Technorati Twitter Reply With Quote Nov 30th, 2012,10:32 PM #2 bjurney Board Regular Join Date Aug 2009 Posts 320 Re: [VBA] .formula = "=" & variable - Error 1004 Almost got it, just put a ' after the = sign so it looks like this: Code: Dim sheetlist As String Dim i As Integer i = 2 While Range("B" & i).Value <> "" sheetlist = Range("B" & i).Value Range("C" & i) = "='" & sheetlist & "'!$D$3" i = i + 1 Wend Share Share this post on Digg Del.icio.us Technorati Twitter Reply With Quote Nov 30th, 2012,10:56 PM #3 Uehara New Member Join Date Nov 2012 Posts 2 Re: [VBA] .formula = "=" & variable - Error 1004 Originally Posted by bjurney Almost got it, just put a ' after the = sign so it looks like this: Code
Guest Hello, I have a worksheet and the following line of code: Me.Range("G11").Formula = "=SUMIF(A1;1;A3:A6)" Excel says: 1004 Application error What am I doing wrong? If I enter the exact same string as a formula, everything works fine. Help! Markus Markus Wilthaner, Dec 15, 2003 #1 Advertisements Tom Ogilvy Guest Either change the semi colons ( to commas or use FormulaLocal rather than Formula (assuming your list separator is set to semicolon). However, I don't thing the formula will produce what you want. Maybe you want an if statement =If(A1=1,Sum(A3:A6),0) -- Regards, Tom Ogilvy "Markus Wilthaner" <> wrote in message news:... > Hello, > > I have a worksheet and the following line of code: > Me.Range("G11").Formula = "=SUMIF(A1;1;A3:A6)" > > Excel says: 1004 Application error > > What am I doing wrong? If I enter the exact same string as a formula, > everything works fine. Help! > > > Markus Tom Ogilvy, Dec 15, 2003 #2 Advertisements Markus Wilthaner Guest "Tom Ogilvy" wrote: > Either change the semi colons ( to commas or use FormulaLocal rather than > Formula (assuming your list separator is set to semicolon). First, thanks for helping me out. You were right, the semi colons are my list separator. Excel is smart and replaces commas in the from-code-formulas with the semicolons. I tried the same script on my Excel 2003 computer and it filled in the formulas. However, all I get is an "Name" Error. When I edit the cell (F2) and press enter without changing anything, it works! Odd.... Using "LocalFormula" causes my Excel 2003 to give me that 1004 Application error. What I was trying to accomplish is to count the occurence of a giving string in a number of cells, using SUMIF(