0

I am having trouble with something in my excel sheet and it's probably due to lack of understanding. Basically, I have an IF() statement in cell I2, and for the if_true I would like to run a Public Sub, namely Sub MyFunc() that just copies data from the row to a new sheet.

In other words, I have in cell I2:

=IF(OR(AND($D2="ABOVE", $F2>$E2, $H2="YES"), AND($D2="BELOW", $F2<$E2, $H2="YES")),MyFunc(),"")

This does not seem to run the sub at all and if I run the Sub in the VBE it works fine. 

Sub MyFunc()

'begin populating table
'Date
Sheets("Sheet3").Range("A:A").End(xlDown).Offset(1, 0).Value = Format(Now(), "dd/mm/yyyy")
'DayofWeek
Sheets("Sheet3").Range("B:B").End(xlDown).Offset(1, 0).Value = Format(Now(), "ddd")
'copy code and paste in cell
Sheets("Sheet1").Range("A2").Copy Destination:=Sheets("Sheet3").Range("E:E").End(xlDown).Offset(1, 0)
'copy level
Sheets("Sheet1").Range("E2").Copy Destination:=Sheets("Sheet3").Range("F:F").End(xlDown).Offset(1, 0)
'delete row information
Worksheets("Sheet1").Range("A2").ClearContents
Worksheets("Sheet1").Range("C2:E2").ClearContents
Worksheets("Sheet1").Range("G2:H2").ClearContents
'resort colums
SortByMarket
   End Sub

Is there any advice I can get in how to call this Sub based on the above condition?

Any help is greatly appreciated :)

Community
  • 1
  • 1
Dean
  • 2,326
  • 3
  • 13
  • 32
  • Possible duplicate of [Function Output in Different Cell](https://stackoverflow.com/questions/24943242/function-output-in-different-cell) – Darren Bartrup-Cook Jun 22 '18 at 11:08
  • Why not just evaluate the if condition in VBA and run the code if the condition is true there? Seems like an obvious design choice. – Samuel Hulla Jun 22 '18 at 11:27
  • Hi Rawrplus, yes I agree with your statement, but I am unsure how to go about this? Could you please help? – Dean Jun 23 '18 at 12:21

2 Answers2

0

Yes it is possible to do if you call your sub with a function as Andy Pope did on this link:
https://www.ozgrid.com/forum/forum/help-forums/excel-general/41643-can-a-subroutine-be-called-within-a-function

You can write the a function:

Function MyFunc()
    Msgbox "Function started"
    Call Copy
    Application.Run "MySub"

End Function

Then you can write your formula like this:

=IF(OR(AND($D2="ABOVE", $F2>$E2, $H2="YES"), AND($D2="BELOW", $F2<$E2, $H2="YES")),MyFunc(),"")

Note: If it doesn't work like this, try first to type in a cell:

=MyFunc()

And see if it returns anything

Pierre44
  • 1,711
  • 2
  • 10
  • 32
  • @D.De Villiers My answer didn't work? or why did you unaccept it? – Pierre44 Jun 22 '18 at 11:11
  • Hi Pierre44, I tried your answer but it does not seem to work that way. I tried creating the Function MyFunc and then calling Copy sub but the IF statement does not seem to run the MyFunc – Dean Jun 22 '18 at 11:14
  • Can you try again by adding a msgbox at the beginning of the Function?, then you can try if the function is launched or if it stops working later – Pierre44 Jun 22 '18 at 11:21
  • I tried using the MsgBox, but it does not launch at all? – Dean Jun 22 '18 at 11:52
  • I just edited my answer. You can try step by step. First with your whole formula and then only the function in a cell without the IF to see where it stops (with the msgbox like in my example to be able to see the issue – Pierre44 Jun 22 '18 at 11:59
  • When I try type =MyFunc() in a new cell it returns #NAME? and a message box does not appear. – Dean Jun 22 '18 at 12:06
  • Did you create a FUnction like this: Function MyFunc(). Because I wrote the name before I saw that you put the same name for your sub. – Pierre44 Jun 22 '18 at 12:13
  • 1
    Also the function must be declared as public and be in a normal module not a worksheet module – Harassed Dad Jun 22 '18 at 12:20
  • Okay, so I have done what you have said above, and the MsgBox does seem to be popping up from both the Public Function and the Sub, however, the code in the Sub is not being executed. Again, if I run the Sub in the VBE it executes and does what is needed but it is not, for some reason executing through the Public Function? – Dean Jun 22 '18 at 12:56
0

So an answer to post the if inside your vba code. 

Sub Test()

If (Range("D2").Value = "ABOVE" And Range("F2").Value > Range("E2").Value And Range("H2").Value = "YES") Or (Range("D2").Value = "BELOW" And Range("F2").Value < Range("E2").Value And Range("H2").Value = "YES") Then

MsgBox "CLEAR"

' HERE YOUR CLEARING CODE

End If

End Sub

Note: I don't delete the other answer as I think it was a possible alternative that also answers the question you asked.

Pierre44
  • 1,711
  • 2
  • 10
  • 32
  • I have gone a different route using Worksheet_Calculate to eliminate the need for the IF() within the cell, and then I call my macro through Worksheet_Calculate. Thanks for the help @Pierre44 – Dean Jun 25 '18 at 08:25