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:

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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
36,066
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 & "))))"
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

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