# Run Time error 1004 in vba

#### haperah

##### New Member
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

BR

Last edited by a moderator:

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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:
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:
Hi,

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

any other ideas?

Thanks

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

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">``

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

I believe "=" & "Null"" should be "=" & ""Null""

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

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

Replies
12
Views
336
Replies
5
Views
249
Replies
8
Views
159
Replies
10
Views
675
Replies
42
Views
1K

1,196,323
Messages
6,014,639
Members
441,833
Latest member
Rangerreeve

### 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.

### Which adblocker are you using?

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

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