+ Reply to Thread
Results 1 to 9 of 9

Create a Macro that uses an IF loop

  1. #1
    Registered User
    Join Date
    03-29-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    19

    Create a Macro that uses an IF loop

    Hi,

    I'm trying to create a Macro that implements an IF loop.

    Here's what the loop would be like:

    a = low end of range ; b = high end of range ; x = salary ; y = employer's contribution ; z = employee contribution
    (a and b are 2 values that are always 19.99 apart. a can start from any value.)

    if a<x<b
    y = b * 0.13
    z = b * 0.11
    else
    a = a+20
    b = b+20
    (loop back to top)

    end

    So basically, I want the macro to check the value in a cell, see if it fits the range, find 13% of b and enter that into one cell and find 11% of b and enter that into another adjacent cell.

    Thanks

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Create a Macro that uses an IF loop

    I guess what you want is:
    Please Login or Register  to view this content.
    and adjust the count to the maximum number of iterations you want to do.

    Although I'm fairly sure:
    Please Login or Register  to view this content.
    Would give you the same thing without the loop

  3. #3
    Registered User
    Join Date
    03-29-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Create a Macro that uses an IF loop

    Thanks! I used the loop. I tried using your formula but it didn't really work for me.

    Here's what I used.

    Sub EPF_Calculator()

    With ThisWorkbook.Sheets("may")

    Dim l_range, h_range, eyer, eyee, count, salary As Double

    l_range = 1480.01
    h_range = 1500

    salary = (.Cells(9, 8).Value + .Cells(9, 9).Value)

    Do
    If l_range <= salary And salary <= h_range Then

    eyer = RoundUp(h_range * 0.13)
    eyee = RoundUp(h_range * 0.11)

    .Cells(9, 12).Value = eyer
    .Cells(9, 13).Value = eyee

    Exit Do


    Else
    l_range = l_range + 20
    h_range = h_range + 20
    count = count + 1
    End If

    Loop Until count = 1000

    End With


    How can I implement this macro for a specified range of cells? Do I use the .range function?

  4. #4
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Create a Macro that uses an IF loop

    Can you be a bit more specific? What is the range of cells? how are they layed out? do you want to loop through different cells? which variables do you want to take from the specified range of cells? Maybe upload an attachment with an example workbook?

  5. #5
    Registered User
    Join Date
    03-29-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Create a Macro that uses an IF loop

    For privacy reasons, I can't upload the file. It contains some details about people.

    The range that I want to apply to is from H9 to H25. The macro I posted above is for cell H9 alone. It adds H9 with I9 so, I'd need all the other cells in the range to add their corresponding cells in row I as well.

    The macro prints the results of the calculations in cells K and L9. The same should apply for my range of cells.

    This is what I tried but I got an error :

    Dim l_range, h_range, eyer, eyee, count, salary As Double

    l_range = 1480.01
    h_range = 1500

    salary = .Range("H9", "H25").Value

    Do
    If l_range <= salary And salary <= h_range Then

    eyer = RoundUp(h_range * 0.13)
    eyee = RoundUp(h_range * 0.11)

    .Range("L9", "L25").Value = eyer
    .Range("M9", "M25").Value = eyee

    Exit Do

    Else
    l_range = l_range + 20
    h_range = h_range + 20
    count = count + 1
    End If

    Loop Until count = 1000


    Thanks for the help!

  6. #6
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Create a Macro that uses an IF loop

    I think
    Please Login or Register  to view this content.
    I haven't tested it though..

  7. #7
    Registered User
    Join Date
    03-29-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Create a Macro that uses an IF loop

    Hi, thank you very much for all your help.

    I tried running your code but it unfortunately didn't work. It gave me the error message : Compile error: Sub or function not defined. It also highlights the function RoundUp. Hope that helps you to help me debug it.

    p.s I couldn't run it past the first line in debugger mode

  8. #8
    Registered User
    Join Date
    03-29-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Create a Macro that uses an IF loop

    I tried your code again, this time without the RoundUps, it made VB hang.

  9. #9
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Create a Macro that uses an IF loop

    You probably need:
    Please Login or Register  to view this content.
    where 0 is the number of decimal places to round to.

    I think it probably hangs because I didn't reset the count to 0 when it loops to the next row, so the loop until count = 1000 to make it stop if it goes to high doesn't work, try:
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1