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 can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,425
Office Version
  1. 2019
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:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,783
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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:

haperah

New Member
Joined
Feb 25, 2016
Messages
15
Hi,

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

any other ideas?

Thanks
 

haperah

New Member
Joined
Feb 25, 2016
Messages
15

ADVERTISEMENT

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 & "))))"
 

LockeGarmin

Active Member
Joined
Sep 11, 2015
Messages
350
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">
 

haperah

New Member
Joined
Feb 25, 2016
Messages
15

ADVERTISEMENT

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
 

haperah

New Member
Joined
Feb 25, 2016
Messages
15
<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">
 

LockeGarmin

Active Member
Joined
Sep 11, 2015
Messages
350
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 & "))))"
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,095
Messages
5,835,356
Members
430,351
Latest member
ddalton

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
Top