Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for 0, then offset down 310, left 4, Check for = 0
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, DONT CALL THE SUB. TIA! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for 0, then offset down 310, left 4, Check for = 0
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 -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for 0, then offset down 310, left 4, Check for = 0
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for 0, then offset down 310, left 4, Check for = 0
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. Thats what I want to do. Also, and I know this complicates things, but I want to call a private sub. I looked he http://www.your-save-time-and-improv...ivate-sub.html Seems like it cant 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "OssieMac" wrote: 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 -- Regards, OssieMac |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for 0, then offset down 310, left 4, Check for = 0
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 -- Regards, OssieMac |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for 0, then offset down 310, left 4, Check for = 0
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 -- Regards, OssieMac |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for 0, then offset down 310, left 4, Check for = 0
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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "OssieMac" wrote: 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 -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop through column(s) to check values, perform action based on check | Excel Programming | |||
Increase size of a Forms Check Box (click on to enter check mark) | Excel Discussion (Misc queries) | |||
check.offset | Excel Programming | |||
Check if Conditional Format is True or False / Check cell Color | Excel Worksheet Functions | |||
Can Vlookup check a cell to the left? | Excel Worksheet Functions |