No value for x in my for/if loop

[Moonbeam111]

I'm kind of new to vba and I'm having trouble with this loop. I want to continously subtract 0.0625 from range("I27") everytime the condition is met but with an extra 0.0625 each time the condition is met again. But there is something wrong with the code. It never gives x any value other than 0 and I'm not sure why. I thought of using multiple if statements but I don't think that's necessary and it would make the code redundant. Can anyone help me here?

Dim x As Integer

If Range("H26") = "" Then
x = 0
End If

x = x + 0.0625

Range("m1") = x 'merely here to keep an eye on x's value


For x = 1 To 1 'not really sure what to put here
If Sheets("Sheet1").Range("H26") = "example text" And Sheets("Sheet1").Range("G41") = "example text" Then 'specific text criteria to match
Sheets("Sheet1").Range("I27") = WorksheetFunction.Max(0, Sheets("Sheet1").Range("I27") - x)
Sheets("Sheet1").Range("H28") = "example" & x & "% text"
Sheets("Sheet1").Range("H27") = Sheets("Sheet1").Range("H27") + 1 'count the number of times condition has been met
Exit For
End If
Next x

 

[jolivanes]

What is this meant to do where x = 0.0625?

WorksheetFunction.Max(0, Sheets("Sheet1").Range("I27") - x)

Maybe explain in a concise manner what you want to achieve.
Quite often macros that do not work are a hindrance because they don't always show what needs to be done.

 

[Moonbeam111]

WorksheetFunction.Max(0, Sheets("Sheet1").Range("I27") - x)

Range(I27) is a cell with a percentage value in it.
Worksheetfunction.max: This ensures Range I27 never goes below 0%
And x is the value that I would like to subtract (% wise) from Range I27 based on if the text criteria specified here is matched:

If Sheets("Sheet1").Range("H26") = "example text" And Sheets("Sheet1").Range("G41") = "example text" Then 'specific text criteria to match

 

[jolivanes]

Maybe explain in a concise manner what you want to achieve.

 

[Moonbeam111]

My apologies. I'm not sure what you mean. I want the variable I set as X to increment by 0.0625 every time I click it. Such that it's like this:

0.0625
0.125
0.1875
0.25
0.3125
0.375
0.4375
0.5
0.5625
0.625
0.6875
0.75
0.8125
0.875
0.9375
1


I tried a for each but that only grabs the entire column. And I tried a for each with an exit for but that only grabs the first number ("0.0625")

Is there a way I can achieve this? I'm trying to continuously subtract from cell I27. Such that I27 = I27 - x (x=0.0625) but each time I click the macro again it becomes I27 = I27 - x ( 0.125) etc...

I've spent over a day researchig this but i just cant find a solution that works.

 

[jolivanes]

Do you want to see how many times you have to subtract 0.0625 from Cell I26 Value to reach 0 (zero)?
Or when do you want to stop subtracting?

Sub Subtract_Value()
Dim i As Long, incr As Double
Range("I27").NumberFormat = "0.0000"
incr = 0.0625
    For i = 1 To 10    '<----- any number
        Range("I27").Value = Range("I27").Value - incr
        MsgBox "Check Cell I27 Value!"
    Next i
End Sub

 

[Moonbeam111]

I would like the code to run through the list I provided earlier number by number (one by one) in each loop or macro click. For example, I click the button once and it subtracts 0.0625, if i click it again it subtracts the entirety of 0,125. Basically trying to store the x as a value for excel vba to remember. I dont want it to subtract for more than one number at a time. I just want it to go-further one more number everytime I click the macro button. Sorry for the confusion. Like I said I tried a for each but that took the whole column and added it together. and I tried an "exit for", "for reach", but it doesnt' go past 0.0625.

I just tried the code you provided on a fresh workbook. It works fine but it doesn't go past 0.0625. I apologize again if I was misunderstood. I would like it to subtract the entirety of the next number every increment not just 0.0625.

For example:
First click = Range("I27") - 0.0625
Second click: Range("I27")- 0.125
Third click: Range("I27")- 0.1875.
etc..


But you're code is doing this:
First click = Range("I27")- 0.0625
Second click: Range("I27") - 0.625
Third click: Range("I27")- 0.0625
etc..

How would i go about doing it the first way?

 

[jolivanes]

Add "* i" after "incr", so it becomes

Range("I27").Value = Range("I27").Value - incr * i

 

[Moonbeam111]

Wonderful. That worked. I just had to tweak it a bit to fit into my current workbook. Guess I was overthinking it. I thought the end result would be larger code. Thanks

 

[Akuini]

Basically trying to store the x as a value for excel vba to remember.

You can declare a variable at the top of the module.
Example:

Dim x As Long

Sub try()
x = x + 1
MsgBox x
End Sub

Each time you run the code, the value in the message box is incremented by 1