CODEWITHSUNDEEP
vbaexcelexcel-formulaoffset
Jon mark
Jon mark

Reputation: 9

how to vba put a value in adjacent columns for a specific value

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

Upvotes: 0

Views: 1721

Answers (1)

genespos
genespos

Reputation: 3311

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

Upvotes: 0

Related Questions