Run Time error 1004 in vba

haperah

New Member
Joined
Feb 25, 2016
Messages
15
Hi,
I get this error in this row in vba...



WS2.Cells(i, 112) = "=IF(AV" & i & "=" & "Null"",20,IF(AV" & i & "*1000" & " < DA" & i & "*CH" & i & ",0,IF(AV" & i & "*1000" & ">DB" & i & "*CH" & i & ",20,20 * (AV" & i & "*1000" & "-DA" & i & "*CH" & i & ")/(DB" & i & "*CH" & i & "-DA" & i & "*CH" & i & ")))"


the original formula in excel is :

=IF(AV2="NULL",20,IF(AV2*1000< DA2*CH2,0,IF(AV2*1000>DB2*CH2,20,20*(AV2*1000-DA2*CH2)/(DB2*CH2-DA2*CH2))))

I'm sitting on it hours can't understand where is the problem

who can help me please?

BR
 
Last edited by a moderator:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
1) Replacing WS2.Cells with a variable and printing that variable produces

=IF(AV1=Null",20,IF(AV1*1000 DB1*CH1,20,20 * (AV1*1000-DA1*CH1)/(DB1*CH1-DA1*CH1)))

Looks like you need 2 lots of double quotes around Null, ie

""NULL""

2) Possibly remove space in front of DB since it doesn't match your original formula you've specified.

3) You have 4 closing brackets in original formula and only 3 in VBA.
 
Last edited:
Upvote 0
Simpler version is to use R1C1 addressing:

Code:
ws2.cells(i, 112).formular1c1 = "=IF(RC[-64]=""NULL"",20,IF(RC[-64]*1000< RC[-7]*RC[-26],0,IF(RC[-64]*1000>RC[-6]*RC[-26],20,20*(RC[-64]*1000-RC[-7]*RC[-26])/(RC[-6]*RC[-26]-RC[-7]*RC[-26]))))"
 
Last edited:
Upvote 0
Hi,

I checked null is ok , I've added 1 closing bracket still have the same problem

any other ideas?

Thanks
 
Upvote 0
this is the formula now:

WS2.Cells(i, 112) = "=IF(AV" & i & "=" & "Null"",20,IF(AV" & i & "*1000" & "<DA" & i & "*CH" & i & ",0,IF(AV" & i & "*1000" & ">DB" & i & "*CH" & i & ",20,20 * (AV" & i & "*1000" & "-DA" & i & "*CH" & i & ")/(DB" & i & "*CH" & i & "-DA" & i & "*CH" & i & "))))"
 
Upvote 0
Does this work as well as follow what your formula is trying to do? (I put a minus sign beside "DB" so it is making a change to your formula.)

Code:
[COLOR=#333333]WS2.Cells(i, 112) = "=IF(AV" & i & "=" & "Null"",20,IF(AV" & i & "*1000" & "-[/COLOR]<da" &="" i="" "*ch"="" ",0,if(av"="" "*1000"="" "="" style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">DB" & i & "*CH" & i & ",20,20 * (AV" & i & "*1000" & "-DA" & i & "*CH" & i & ")/(DB" & i & "*CH" & i & "-DA" & i & "*CH" & i & "))))"</da">
 
Upvote 0
WS2.Cells(i, 112) = "=IF(AV" & i & "=" & "Null"",20,IF(AV" & i & "*1000" & "<DA" & i & "*CH" & i & ",0,IF(AV" & i & "*1000" & ">DB" & i & "*CH" & i & ",20,20 * (AV" & i & "*1000" & "-DA" & i & "*CH" & i & ")/(DB" & i & "*CH" & i & "-DA" & i & "*CH" & i & "))))"

sorry this is the formul
 
Upvote 0
<da" &="" i="" "*ch"="" ",0,if(av"="" "*1000"="" "="">sorry this is the formula :
[/WS2.Cells(i, 112) = "=IF(AV" & i & "=" & "Null"",20,IF(AV" & i & "*1000" & "<DA" & i & "*CH" & i & ",0,IF(AV" & i & "*1000" & ">DB" & i & "*CH" & i & ",20,20 * (AV" & i & "*1000" & "-DA" & i & "*CH" & i & ")/(DB" & i & "*CH" & i & "-DA" & i & "*CH" & i & "))))"]

</da">
 
Upvote 0
Correction: Thought I added the quotation marks next to null in my solution as well.

Code:
"=IF(AV" & i & "=" & """Null"",20,IF(AV" & i & "*1000" & "-DB" & i & "*CH" & i & ",20,20 * (AV" & i & "*1000" & "-DA" & i & "*CH" & i & ")/(DB" & i & "*CH" & i & "-DA" & i & "*CH" & i & "))))"
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top