VBA code If then Statement

[Deenz_13]

I am very much a novice when it comes VBA for excel, I am currently working on a macro for a spread trading worksheet. The below if statement changes the value on the buy side, however the conditions are met to slowly and the value doesn't adjust quickly enough.

=IF(AND(H6=0,I68=0),D4,IF(AND(H6>0,H6<D4,I68=0),D4-H6,IF(AND(H6<0,H6>=-E4-F4,ISBLANK(L2)),H6*(-1),IF(H6<-E4-F4,-H6-E4-F4,IF(H6=D4,1,IF(H6>D4,1,IF(AND(H6=0,ISTEXT(I66),I68>0),D72,IF(AND(H6>0,H6<P5,ISTEXT(I66),I68>0),P5-H6,IF(AND(H6=P5,ISTEXT(I66)),D73,IF(AND(H6>P5,H6<P6,ISTEXT(I66)),P6-H6,IF(AND(H6=P6,ISTEXT(I66)),D74,IF(AND(H6>P6,H6<O7,ISTEXT(I66)),O7-H6,IF(AND(H6=D4,ISTEXT(I66),I68>0),1,IF(AND(H6+I68>D4,ISTEXT(I66),I68>0,H6>0),D4-H6,IF(AND(H6<-O3,H6>=-E4-F4,ISTEXT(L2)),H6*(-N2),1)))))))))))))))

I started writing a macro to perform the same function:

Please Login or Register  to view this content.

I am having a problem with the H6>=-E4-F4 and ISBLANK(L2), not 100% how this should be written in VBA to satisfy the condition.

Also wanted to know how to automate the process so that when I open the spreadsheet the macro runs on its own and if there was a way to improve the code.

Thanks for the assistance

[wenqq3]

Please Login or Register  to view this content.

Is this?

[ajryan88]

Hi,

For the macro to run when the workbook is opened, insert this code into the ThisWorkbook module:

Please Login or Register  to view this content.

To write your condition in VBA, you would use:

Please Login or Register  to view this content.

By the way, it might also be worth noting that this code will probably fail if it is moved to the Workbook_Open subroutine, as none of your cell references are worksheet-specific. The simplest way for you to amend this would be to wrap your code in a With statement and change every instance of "Cells" to ".Cells".

Hope this helps

[Deenz_13]

Thanks for the help guys. Just having a bit of an issue with the automation. Still having to click on the macro to run it.

[ajryan88]

Hi,

If you place my code above into the "ThisWorkbook" module, your code will run when the workbook is opened...Capture.JPG

[Deenz_13]

Put the code in ThisWorkbook and it does change when I start up however I need the macro to run based cell H6 which changes continually. effectively all that should happen is that I should open the spread sheet and select the cells to pull through to DDE and allow the trading to take place without having to touch the spread sheet.

[ajryan88]

Hi, try inserting this code into the worksheet module for the worksheet that you want to monitor cell H6 on:

Please Login or Register  to view this content.

Hopefully this time it works for you

[Deenz_13]

Hi, still only works when I open the worksheet and then I manually have to run the macro each time H6 changes.

Any chance I could attached the spreadsheet and you could take a look at the code and maybe see what I am doing wrong?

[ajryan88]

Certainly, please feel free to upload it

[Deenz_13]

Hi, attached the file. I have done all my workings on the third tab.

[ajryan88]

Hi,

Your Worksheet_Change subroutine was placed in the module "Module1", not the worksheet module for "Control Panel" (see the amended attachment below).

Note, I have also tidied your code up a little bit to make it more readable.

Hope this helps

[Deenz_13]

Thanks so much. Everything is working properly

[ajryan88]

No worries! Glad to hear it.

Please don't forget to mark this thread as solved and please click on the * next to my post(s) to say thanks

[Deenz_13]

Hi any chance of change this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$6" Then
Buy
Sell_Number
End If
End Sub

I need to include all the entire range of cells in the spread sheet as some cells require the data changing in other cells before they can run.