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

I guess what you want is:

do
  if a < x and x < b then
    y = b * 0.13
    z = b * 0.11
    exit do
  else
    a= a+20
    b= b+20
    count = count+1
  end if
loop until count = 1000

and adjust the count to the maximum number of iterations you want to do.

Although I'm fairly sure:

y = (b + 20 * Round(((x - a)/ 20) - 0.5)) * 0.13
z = (b + 20 * Round(((x - a)/ 20) - 0.5)) * 0.11

Would give you the same thing without the 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?

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?

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!

I think

Sub asd()
Dim l_range, h_range, eyer, eyee, count, count_1, salary As Double

For count_1 = 9 To 25
l_range = 1480.01
h_range = 1500
With Sheets("may")
salary = .Range("H" & count_1).Value
 
Do
If l_range <= salary And salary <= h_range Then

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

.Range("L" & count_1).Value = eyer
.Range("M" & count_1).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
Next
End Sub

I haven't tested it though..

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

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

You probably need:

application.worksheetfunction.roundup(h_range * 0.13,0)

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:

Option Explicit

Sub asd()
Dim l_range, h_range, eyer, eyee, count, count_1, salary As Double

For count_1 = 9 To 25
count = 0
l_range = 1480.01
h_range = 1500
With Sheets("may")
salary = .Range("H" & count_1).Value
 
Do
If l_range <= salary And salary <= h_range Then

eyer = WorksheetFunction.RoundUp(h_range * 0.13, 0)
eyee = WorksheetFunction.RoundUp(h_range * 0.11, 0)

.Range("L" & count_1).Value = eyer
.Range("M" & count_1).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
Next
End Sub