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, DON’T CALL THE SUB.

TIA!
Ryan--


-- 
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
0
Utf
12/9/2009 12:11:01 AM
excel.programming 6508 articles. 2 followers. Follow

6 Replies
602 Views

Similar Articles

[PageSpeed] 16

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


0
Utf
12/9/2009 12:58:01 AM
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
0
marcus
12/9/2009 1:01:39 AM
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---


-- 
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
> 
> 
0
Utf
12/9/2009 5:31:01 AM
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

0
Utf
12/9/2009 6:41:01 AM
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


0
Utf
12/9/2009 6:46:01 AM
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
> 
> 
0
Utf
12/9/2009 4:01:01 PM
Reply:

Similar Artilces:

merge MSN Bill Pay & Checking Acct
I want to merge my checking account with MSN Bill Pay but only see that I can merge MSN Bill Pay with my checking account (i.e., you lose all of your checking account information). Does any one have any suggestions? -- cheers, Tom G ...

How do I create Check list
1.I am new to excel and want to create a simple check list. I would like to have the first column check boxes and then the following columns discription and notes. Can someone give me a step by step on how to do this. I have created spread sheets but am not familiar with check boxes. 2.Also, Can someone tell me the difference between Access and Excel? Geek 1) Not sure what you're aiming to do with the checkboxes, but if you right click on the toolbar area, you will get a list of available toolbars. You want either the 'forms' toolbox or the 'controls' toolbox. Genera...

Documentation CD of MSCRM 3.0
Hi there, I've downloaded MSCRM 3.0 beta and in the SDK there's a sample code of dynamicpicklists indicating to be in the documentation CD. Where can i download that cd for this version? or is there any place else to find that code? thanks in advance, Diogo Maya Monteiro If you check the readme in the doc folder, it has links to the SDK online. This download has the samples you are looking for. -- Matt Parks MVP - Microsoft CRM "Diogo Monteiro" <DiogoMonteiro@discussions.microsoft.com> wrote in message news:123A6D9A-8C14-4777-BC19-F824B7677491@microsoft.com....

Authorizing checks with RMS
I recently removed PCCharge and setup my syetm to work with a preferred merchant provider to allow native processing of credit & debit cards. Since check verification/authorization was handled by PCCharge to Telecheck, check authorization is now not being done. How is check authorization accomplished using a preferred provider? Thanks, Richard Unfortunately, it's not. PC Charge is the only option for check processing in RMS as far as I know. TPI or some other vendor may support it through an add-in, but RMS as shipped has no way of doing both checks and debit... -- -- Gle...

Check names not working for multiple address books / contacts
Hello! I have upgraded from outlook 2003 to outlook 2007, my contacts are stored in pst. I used to be able to search for a name via check names and it displayed me all the results from global address list and from my contacts, but now it only does one. It does not matter which one I set in the checking order, whichever is top will be the one that provides the result. I would like it to show me both results, how would I go about to set that up? Thank you Deana More accurate and complete information would be helpful. The Global Address List is only available to those who c...

Outlook 6.0 as the default mail client
I am using Publisher 2007 to make an email merge. When I try to select my list from outlook contacts it tells me that I do not have a default mail client. It then tells me to run outlook express and make it my default mail. when I go to outlook it is already marked as default. Anybody know what I need to do or check next? "Meatman" <Meatman@discussions.microsoft.com> schreef in bericht news:16F0FE91-54A6-489E-8A58-553E34F7A4AC@microsoft.com... >I am using Publisher 2007 to make an email merge. When I try to select my > list from outlook contacts it tells me that I d...

Charts, HTML, offset
i have created a few charts that use =offset and comboxes to display different data depending on which name is chosen. a b a b a 1 day 1 day 2 day 3 day 4 2 John 12 16 15 17 3 Marie 11 13 15 16 4 Sandra 10 11 14 15 5 Alex 9 13 16 18 is there a way i can post the combo box with the chart in a html chart? i am trying to show improvement one person at a time... thanks Hi, It doesn't seem like you can publish a chart with interactive components, at least not from inside of Excel. Cheers, Shane Devenshire "Snadyeka" wrote: > i have created a few charts that use =offs...

Hiding #DIV/0!
I have a division formula and when there is nothing in one of the cells that is in the formula I get the #DIV/0! in the cell. Is there a way to hide this unless the cells in the formula have a value? Thanks. Todd One way: Say you want to divide A1 by B1. =A1/B1 gives you the error you got if B1 is blank or zero. Change the formula to: =If(B1=0,"",A1/B1) This gets you a blank cell if B1 is blank or zero. HTH Otto "Todd" <todd@yahoo.com> wrote in message news:045e01c3813d$12b18d80$a401280a@phx.gbl... > I have a division formula and when there is nothin...

Deploying Web Service SDK 3.0 Issue
I have a web service that I have developed. The web service interacts with CRM 3.0, creating and updating opportunities from an exteranl application. The service works great on my development machine. I can even call it from an external machine, and it works just fine. However, when I deploy it to my production server, I get the following error: File or assembly name Microsoft.Crm.MetadataService, or one of its dependencies, was not found. What am I doing wrong in the deployment? I never had any deployment issues with CRM 1.2. Have I not included something? Thank you in advance, Paul pjm...

Check text field value against underlying table or query of combo box
Hello, I have a form on which the user can enter new records or update existing ones. If the user enters a new record on the form, I would like to have an 'After Update' event on one of the text fields, which is the item# to check if the value entered already exists in the underlying table, tbl_item. How can I call the table from the 'After Update' event of the text field and check for that value in the table? Thanks in advance. Regards, A. Crawford antcraw@swbell.net wrote: >I have a form on which the user can enter new records or update >existing ones. >If the us...

Preventing multiple check boxes from being checked
I have a worksheet with over 20 rows of check boxes with 5 check boxes in each row. (from the forms toolbox) I would like to prevent the user from checking more than one box in each row. Is this possible? BTW, this forum has been a great help. I'm so glad that expert Excel users are so willing to share their wealth of knowledge. Thanks again. Jae Put them in a group box, again from the forms toolbar. Drag the group box onto the sheet, then drag the checkboxes in. -- HTH RP (remove nothere from the email address if mailing direct) "Jae" <Jae@discussions.micro...

Count If Formula #4
I need a formula that will figure the following: If column C = 3, count all times < 00:45 If column C = 4, count all times < 00:45 If column C = 3, count all times > 00:46 Thanks. Your description is a bit confused.You have different actions for the same value (C=3). And where are the times, in a different column than C? So, making many assumptions, perhaps =SUMPRODUCT(--(ISNUMBER(MATCH(C1:C200,{3,4},0))),--(D1:D200<TIME(0,45,0)))+S UMPRODUCT(--(C1:C200=5),--(D1:D200<TIME(0,46,0))) -- HTH RP (remove nothere from the email address if mailing direct) "Denise"...

Why this happen? is it a bug of mfc9.0
I write a program in vc++ 2008,and i want to make it support multiple language. so i write a dll with all the resource build in it ,then i use the API function : LoadLibrary to Load the resource dll. last i use AfxSetResourceHandle(hCurrLanguageInstanceHandle);still now the application can run. but when i change the visual style. for example i change the style to office 2007(blue style), the application will stop at line 7 below.. can someone help me. thanks !!!!!! 1 CTagManager tm; 2 if (!tm.LoadFromResource(GetStyleResourceID(m_Style), AFX_RT_STYLE_XML)) 3 { 4 #if !defined _A...

Printing Checks
I'm having a problem printing checks from Money 2003. I have three accounts that come up in one .mny file. Two of the accounts use 3-on-a-page checks and I want the name and address printed on them. One account uses voucher checks and needs to be nudged a little south. When I go to print checks I have to be aware of what account I'm in and change the settings by hand to make sure the checks print correctly. How can I make the check-print settings stick with the account I'm using. It's a pain to have to remember every time. I'm frequently forgetting and screwing up a pag...

Employee Check Format-Graphical
Does anyone know of a TK that shows how to convert one of the employee check from a text report to a graphical report? We do not have Direct deposit, I looked in the Report Downloads from Great Plains and did not see one there either. thanks -- Doug There's no secret here. Just un-check the "Text Report" option in the Report Definition window. And then modify the report so it looks like you want it. This will take some time. The reports don't transfer from text to graphical nicely. "Doug" wrote: > Does anyone know of a TK that shows how to convert...

Check Names = "Exchange Server computer is busy..."
Here's what I got: Active Directory on Server 2003 1 Domain Controller also running DNS and WINS No other DNS or WINS servers (I know, I know) Static IP Addresses...NO DHCP Exchange Server 2003 on a member server Client running W2K SP4 Outlook 2000 SP3 Exchange has 4 mailboxes set, but nobody is using it. I'm still testing things out. When I try to connect to Exchange via my Outlook 2000, I get the following message: "The function cannot be performed because the Microsoft Exchange Server computer is busy. Try again later" I've tried it from 3 different client comp...

Printing MICR checks
Hello, We are having an issue printing checks on the Select Payables Checks window. When we try to print the checks we receive the error message below: "Warning: You have MICR but are not in a MICR screen. To use MICR, abort this screen and set up security access." The user has access to this screen but I'm not sure what this error message means. There was nothing related to this in the Knowledge Base either. Has anyone else received this error message before and if so how did/do you resolve it? Thanks! The user has the third party mekorma check writing module ...

Check off boxes
Publisher 2003 in use. I am creating a questionnaire that will be included in a newsletter. I am using the publisher software to make this form. Somewhere, in the past, I saw a keyboard shortcut to inset checkoff boxes, however, I cannot remember the key strokes. Any help along this line will be greatly appreciated. Thank you and enjoy your day. Mike Mike Reshetar wrote: > Somewhere, in the past, I saw a keyboard shortcut to inset checkoff boxes, > however, I cannot remember the key strokes. Use Wingdings and the � or q characters. -- Ed Bennett - MVP Microsoft Publisher h...

budget #4
i am using money 2004:when i try to edit my budget the amount of the particular transaction gets doubled i.e for example i put in 500$ for rent ,the next time i edit that rent from 500$ to 600$ and when i take the printout under rent category i get ,1100$it adds up dont know what to do ...

CRM 3.0 Demo db ?
How can I get Demo data in my CRM env ? I used Adventure Work Cycle as my organization name..but everything is empty. Sam Hi Sam! "Sam" wrote > How can I get Demo data in my CRM env ? I used Adventure Work Cycle as my > organization name..but everything is empty. Read chapter 18 of the implementation guide: Microsoft CRM 3.0 Implementation Guide http://www.microsoft.com/downloads/details.aspx?familyid=1ff067f8-4f77-40f0-ae9c-68ada7d4f16a and have a look at the "SampleData"-folder on your server-installation medium. Cheers Arne Janning On the 3.0 serv...

check check check
afpoiadshfpaosid It is a feature, not a bug -- Message posted from http://www.ExcelForum.com ...

spam properties check
When I receive a suspecious email (spam), I right click, without opening the message, to check the properties to make sure it is spam before I forward to the "missed spam" software of my ISP. Some spam emails then ask me if "I want to save the changes" before I close the properties box. I am then not able to forward this email without replying yes to "save the changes?" I did not make any changes, I just checked the properties. Why is this happening? ...

tristate check box #2
I have a tristate check box.The default behavour of tristate check box in windows is that, the grayed check box will become unchecked after click on grayed check box. But i want , the grayed check box will become checked after click on grayed check box in VC++ .Pls help me its urgent. Well, for Win32, you can always SendMessage the button a BM_GETSTATE. I'm sure with MFC there's a function that will permit this; it could be as simple as button_name.GetState(). Well, for the event OnClick for that button, you can get the state of the button and then either run it through some if-...

CFile::Seek(LONGLONG offset,UINT nForm)
My version only supports a (long) offset. How do I get the correct version of the MFC to support the LONGLONG offset Nigel in the aerospace buisness wrote: > My version only supports a (long) offset. How do I get the correct version of > the MFC to support the LONGLONG offset To use a newer MFC version you have to have a whole new VC version. VC 2005 is expected soon, so you might want to wait before upgrading. An easy alternative is to call the SetFilePositionEx API. The required handle is available in CFile::m_hFile. -- Scott McPhillips [VC++ MVP] Many thanks for the quic...

Migrate data from 1.2 to 3.0
Hi Can i use the data migration framework to migrate data from 1.2 SBS to 3.0 Professional after doing all the required mappings ? Is there an easier way to have data from 1.2 SBS moved to 3.0 Professional? Unfortunately you cannot move data between deployments. You can only upgrade the existing CRM 1.2 deployment to 3.0. There is no 1.2 SBS Edition, so you may have to redeploy the 1.2 environment to a new place, then upgrade the system. -- Laszlo Kovacs MCSE+S, MCT, Microsoft Dynamics Certified Master "Jij" wrote: > Hi Can i use the data migration framework to migrate d...