0

When I refer to Dim I don't mean Dim in a function I mean like any random word like car, bus ectc

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 so that every cel in column B that has Dim I need to run the above sub to quickly changes the zeros to ones and then change them back again immediately

I need it to refer to the DIM in E1: i.e if column B has E1

    B    D    E
  1 dim   0    Dim
    dim   0
    car   0
    car   0
    dim   0
    car   0

I found this here: it says, ''http://www.quepublishing.com/articles/article.aspx?p=2021718&seqNum=8 Suppose you have a list of produce in column A with totals next to them in column B. If you want to find any total equal to zero and place LOW in the cell next to it, do this: ''

Set Rng = Range("B1:B16").Find(What:="0", LookAt:=xlWhole,        LookIn:=xlValues)
Rng.Offset(, 1).Value = "LOW"

Although I'd need it set out slightly differently not referring to column A or B from A but to a non adjacent column . I.e to check is D:D has Dim then put 1 in any cell that does in column C:C offset to column D:D and then changing it back to zero using sleep(1)

surely this can be adjusted for what I need.

my attempts below

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
sub pump_onall()

 Set Rng = Range("B1:B16").Find(What:="Dim", LookAt:=xlWhole,          LookIn:=xlValues)
Rng.Offset(0, 1).Value = 1
sleep(1)
Rng.Offset(0,1).Value = 0
End sub

I get the error on the set Rng line

Sub pump_onall()

Set Rng = Sheets("Account Details    --->").Range("DH1:DH50").Value.Find(What:="DQ3", LookAt:=xlWhole,   LookIn:=xlValues)
Rng.Offset(0, -7).Value = 1
Sleep (1)
Rng.Offset(0, -7).Value = 0
End Sub

Surely this can work 

Sub pump_onall()

Sheets("Account Details --->").Range("DH1:DH50").Value.Find(What:="DQ3",  LookAt:=xlWhole, LookIn:=xlValues)
Sheets("Account Details --->").Range("DH1:DH50").Offset(0, -7).Value = 1
Sleep (1)
Sheets("Account Details --->").Range("DH1:DH50").Offset(0, -7).Value = 0
End Sub

please help this should be easy

in response to genespose I tried to run the sub
but got an error on the line after else for below

Sub pump_on()
LastRowDH = Cells(Rows.Count, 50).End(xlUp).Row
For i = 3 To LastRowDH
If Cells(i, 50) = "DQ3" Then
Cells(i, -7) = 1
Application.Wait (Now + 0.000001)
Cells(i, -7) = 0
Else
Cells(i, -7) = 0
End If
Next i
End Sub

where I put "DQ3" above I think it would work if this referenced the cell DQ3 is this possible?

I have tried 

Sub pump_on7()
With ThisWorkbook.Sheets("Open Positions --->")
LastRowDH = .Cells(Rows.Count, 50).End(xlUp).Row
For i = 3 To LastRowDH
    If .Cells(i, 50) = Sheets("Open Positions --->").Range("DQ3").Value Then
        .Cells(i, -7) = 1
        Application.Wait Now + TimeValue("0:00:01")
        .Cells(i, -7) = 0
    Else
        .Cells(i, 32) = 0
    End If
Next i

End With End Sub

no luck

Jon mark
  • 9
  • 4
  • 1
    Again...? Duplicate number 3.. http://stackoverflow.com/questions/28898567/how-do-i-change-value-of-cells-offset-from-a-column-containing-a-certain-value?noredirect=1#comment46057233_28898567 – Tom K. Mar 06 '15 at 15:33
  • Or Duplicate http://stackoverflow.com/questions/28896954/vba-functionbutton-to-change-a-cells-value-for-a-few-milliseconds-then-revert – mrbungle Mar 06 '15 at 15:47

1 Answers1

0

I'm not sure that is what you want but...

With ThisWorkbook.Sheets("YourSheetName")
    LastRowC = .Cells(Rows.Count, 3).End(xlup).row
    For i = 1 To LastRowC
        If .Cells(i, 3) = "dim" Then
            .Cells(i, 4) = 1
            Application.Wait Now + TimeValue("0:00:01")
            .Cells(i, 4) = 0
        Else
            .Cells(i, 4) = 0
        End If
    Next i
End With
genespos
  • 3,211
  • 6
  • 38
  • 70
  • yes maybe. can you not put cells(i,4).value = 1 or use a range instead of cells and put a sleep after every IF. Or don't reference the same cell in else – Jon mark Mar 06 '15 at 15:36
  • Sorry, I really don't understand what you need. (It may be because I don't speak english so much). Can you easly explain what result you expect? – genespos Mar 06 '15 at 15:41
  • Would you run this as a sub? I am not sure it would work because I need those 1's to revert to 0 very quickly. It might work if the function was run twice because once the cell turns to 1 there will not be any more dims – Jon mark Mar 06 '15 at 15:43
  • Yes, the code works in a sub. Can you explain your goal? – genespos Mar 06 '15 at 15:46
  • so basically once the cells are turned to 1 the dim's would in theory disappear because they're linked to another function, in which case, this would work if the sub was run twice to revert them to 0 again. Then it would be a question of how quickly can I run the sub twice. It might be inefficient to do this. Oh wait I need to think again – Jon mark Mar 06 '15 at 15:48
  • no basically this wouldn't work because it can't put each cell straight back to zero. if it can be adjusted to do that it will definitely work! – Jon mark Mar 06 '15 at 16:00
  • I still don't understand but I modified the code... It waits a second and then changes to 0 the cells modified.maybe you don't need to wait ... test with and without "Application.Wait" – genespos Mar 06 '15 at 16:05
  • This is for 1/10 second: `Application.Wait (Now + 0.000001)` – genespos Mar 06 '15 at 16:20
  • thanks genespos but I just cant get it working - seem my original question for my attempts. Doesn't the' lastrow' have to reference the sheet as-well and be in quotations? – Jon mark Mar 06 '15 at 17:42
  • `LastRowC = .Cells(Rows.Count, 3).End(xlup).row` gets the last row of column "C" in the sheet you put in the `with` clause – genespos Mar 06 '15 at 17:50
  • I am not sure I want the last row – Jon mark Mar 06 '15 at 18:00
  • If you're not sure of what you need how can I help you? What can I say? – genespos Mar 06 '15 at 18:15
  • sorry. Is the last row being referred to as from first row i to last row? It should work like that I guess. – Jon mark Mar 06 '15 at 18:17