Check for >0, then offset down 310, left 4, Check for = 0

[ryguy7272]

I am trying to come up with a procedure that checks a Range(“E3:E300) and if
any value is >0 then Offset(310, -4) so I go down 310 rows and over to Column
A, and then look at the value in that cell. If the value in that cell is
equal to 0, then CALL A SUB in another module.

When the sub finishes, put a 1 in that cell, down 310 and over to the left
4. So, the next time I run this loop, if I checks a Range(“E3:E300) and if
any value is >0 then Offset(310, -4) so I go down 310 rows and over to Column
A, and then look at the value in that cell. If the value in that cell is
equal to 1, DON’T CALL THE SUB.

TIA!
Ryan--

 

[OssieMac]

Hi Ryan,

To the best of my testing CountIf(rng, ">0") > 0 only looks at numerics;
unlike testing for a cell >0 which returns all cells with anything at all in
them.

You did not specify from which cell you want the offset so I assumed E3.

Sub Call_If()

Dim ws1 As Worksheet
Dim rng As Range

Set ws1 = Sheets("Sheet1")

With ws1
Set rng = .Range("E3:E300")
End With

If WorksheetFunction.CountIf(rng, ">0") > 0 Then
If ws1.Range("E3").Offset(310, -4) = 0 Then
Call Asub
ws1.Range("E3").Offset(310, -4) = 1
End If
End If

End Sub

 

[marcus]

Hi Ryan

This should do what you are after. Obvo change the variables to
suit. I tested it on a smaller range. But the principle is the same.

Take care

Marcus

Option Explicit
Sub Move()
Dim i As Integer

For i = 2 To 10
If Range("E" & i).Value > 0 Then
If Range("A" & i + 10).Value = 0 Then
Range("A" & i + 10).Value = 1
'Call YOURMACROHERE
End If
End If
Next i

End Sub

 

[ryguy7272]

Thanks OssieMac! That kind of worked; it worked for the first cell, which
had data in it, but it didn't work for any subsequent cells. I say kind of,
because if I enter data in E4 and E12 and E20, I see a ‘1’ in cell A312, but
no subsequent cells, and no sequence, like this…>0 in E5, E20, & E22, and a
‘1’ in A316 A332, & A334. That’s what I want to do. Also, and I know this
complicates things, but I want to call a private sub. I looked here
http://www.your-save-time-and-improve-quality-technologies-online-resource.com/vba-private-sub.html

Seems like it can’t be done. Is it true? I have this:
Private Sub cmdSendBasket_Click()
‘stuff here…
End Sub

The private sub is in a sheet; linked to Active X button. Haw can I call
this from the Sub Call_If() macro? Or, how do I need to change my project to
make this work?

Thanks so much!
Ryan---

 

[OssieMac]

Hi again Ryan,

I think that I might have completely misunderstood the question. I thought
that you meant that if any cell in the range was greater than 0. If I now
interpret the question correctly you want to iterate through the cells and
check them one at a time and place the 1 in a cell offset from the cell being
tested. Is this correct? To do this, depending on whether all cells will be
numeric, you might need to test each cell if isnumeric as well as testing if

0 because cells with any character returns greater than zero. See code

below and see if that does what you want.

On the other question re calling a private sub. What I would do is place the
private subs code in a standard module and in the private sub have only one
line of code to call the sub in the standard module. You can then call that
code in the standard module from anywhere in your project.

Sub Call_If()

Dim ws1 As Worksheet
Dim rng As Range
Dim cel As Range

Set ws1 = Sheets("Sheet1")

With ws1
Set rng = .Range("E3:E300")
End With

For Each cel In rng
If IsNumeric(cel.Value) And cel.Value > 0 Then
Call Asub
cel.Offset(310, -4) = 1
End If
Next cel

End Sub

 

[OssieMac]

My apologies Ryan I forgot to include the test for the offset cell in the If
statement. Try this instead.

Sub Call_If()

Dim ws1 As Worksheet
Dim rng As Range
Dim cel As Range

Set ws1 = Sheets("Sheet1")

With ws1
Set rng = .Range("E3:E300")
End With

For Each cel In rng
If IsNumeric(cel.Value) And _
cel.Value > 0 And _
cel.Offset(310, -4) <> 1 Then

Call Asub
cel.Offset(310, -4) = 1
End If
Next cel

End Sub

 

[ryguy7272]

Perfect, perfect, perfect! I was going down the wrong path, but you got me
straightened out; headed in the right direction now.

Thanks so much!
Ryan---