1

I need to be able to change the value of the cells offset to the left of a column containing a certain word. For example in COLUMNS C and D I have

    C     D    E
  1 dim   0
    dim   0
    car   0
    car   0
    dim   0
    car   0

I need to be able to make a VBA formula that would do pretty much what any excel if formula would do. So if I entered in excel ( not vba) if(C1=dim,D1=1,0) so I would get the 1 down the first second and 5th row in column D.

The reason I need to do this in VBA is fairly complicated but once I can refer to them all using an if function in vba then in theory I can refer to all that contain Dim and then have them display 1 for a millisecond like below but on an aggregate basis. somehow 

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
sub changeto1quickly()
range("D1").Value = 1
sleep(1) 
("D1").Value= 0 
End sub

So that I can assign a button in excel to perform this command (to change all those zeros with dim three cells to the right to 1 for a millisecond)for all those cell's in column C that have the corresponding Dim in the offset to the right cell by 3 cells.

I am not hugely advanced at VBA but if this can be done without activating or selecting cells I assume it would be much more efficient.

This is the best I can come up with so far 

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
sub changeto1quickly()
**if range(C:C)contains Dim.offset(3,0).value**
select.value(s)

range("D:D").**Values** = 1
sleep(1) 
("D:D").**Values**= 0 
End sub



Function ClosePosition(functionId As Long, broker As Long, orderId As
Long, execute As Long) As String   
 Application.ScreenUpdating = False
 If execute = 1 Then
 ClosePosition = prog.ClosePosition(functionId,broker,orderId)                    
Else
    ClosePosition = "NO EXECUTE"
End If
Application.ScreenUpdating = True
End Function

Unless I can change the function id in the function to reference all cells in a column with offset cells n another column equal to dim. This could work but it is entirely unlikely because it would have to change the function to closeposition(s) - then reference an if function or something which I don't think is possible because I'd still basically need the 0 to go to 1 very quickly somewhere

so I could actually see some variation working in this function without having to rely on the 0 changing to 1 quickly. If the order ID can reference a column whos adjacent values are the Dim or instrument value in question. Then to run multiple closepositions for all those that contain it. It would also have to reference the function ID which is the same as the order ID.

something like

 Function ClosePosition(functionId As Long, broker As Long, IF COLUMN D CONTAINS, RETURNS STRING OF VALUES) orderId As
Long, execute As Long) As String   
 Application.ScreenUpdating = False
 If execute = 1 Then
 ClosePosition = prog.ClosePosition(functionId FROM STRING ABOVE IF FUNCTOIN,broker,orderId FROM STRNG IF FUNCTION ) 

  close string               
Else
    ClosePosition = "NO EXECUTE"
End If
Application.ScreenUpdating = True
End Function

something like this maybe? 

Set Rng = Range("B1:B16").Find(What:="0", LookAt:=xlWhole,        LookIn:=xlValues)
Rng.Offset(, 1).Value = "LOW"
Jon mark
  • 9
  • 4
  • What seems to not work in your code? Do you get any error messages? And what exactly do you want to do with the "1"s? – Tom K. Mar 06 '15 at 12:22
  • The best I can come up with isn't going to work because it is guess work for a template. For the 1#s to revert back to zeros immediately like in that other post. ( not a duplicate of this one because this one is asking about how to a to refer to multiple form of that post). So to essentially turn them all to 1, wait a millisecond them return them to 0 but referring to them like you would in any other way through vba for any other sub/function. – Jon mark Mar 06 '15 at 12:28
  • 1
    I can try extend that other post though because I know the person who wrote it. – Jon mark Mar 06 '15 at 12:34
  • Would it be possible to just use another column, put the "1"s there, hide the column, and then use these values to do further calculations? – Tom K. Mar 06 '15 at 12:38
  • not sure what you mean. Actually yes maybe. Not sure what hiding the column would achieve though. Well if the ones appear then the function I am referring to the ones from will repeat which is unwanted but its a good idea ( I guess). Actually no it needs to be n the same column because the function referring to the ones is in every row of another column – Jon mark Mar 06 '15 at 12:43
  • Then let me rephrase: What do you need these values for and why only 1ms? Just as a visual effect? Do you want to do further calculations? And if so, you could write a VBA script to save all "1"s in - say - Column H and then just use these values for any further calculation. – Tom K. Mar 06 '15 at 12:47
  • I suspect the solution you need (although not the one you are asking for) is to modify whatever procedure it is that is checking every few milliseconds for these numbers. – Dave Mar 06 '15 at 12:50
  • @ Tom - no because - it is for trading and closing positions. The function that closes the positions refers to a cell in the same row which gets repopulated with a new trade everytime a trade is closed through this function. Hence I cant have it function for a long time before it is repopulated. What I am trying to do now is essentially closed every trade on a certain instrument( Dim ) which would still leave open trades vulnerable to being closed if the column still contains zeros. It needs to still change the original column( ie individually their closed and repopulated. – Jon mark Mar 06 '15 at 12:50
  • @dave It may be possible but I'd rather not alter this function which is fixed to the worksheet. I haven't made it I will post it above in the question to see if a duplicate can be made to the parameters. – Jon mark Mar 06 '15 at 12:59
  • @dave I edited the question to show you the function that refers to the 1. It could possibly be adjusted to account for multiple cells but I doubt it) – Jon mark Mar 06 '15 at 14:29

0 Answers0