Set a number then step down to 1

[Otawata]

Hi!

I know there has to be a more efficient way to write this and am looking for some help with my code to be cleaner and more dynamic.

For a worksheet named Q1,

• In cell C19, I will have an integer (3-8)
• I want cell C21 to start as an integer based off C19:
  • "4" if C19 >=4
  • the value in C19 if C19<4
• Next, I want cell C21 to adjust down from the initial setpoint based on the logic between cells C13 and Q35 as follows:
  • C21 starts at an initial value above
    • If C13 is 0, remain at the initial value
    • If C13 is not 0, check:
      • Is cell Q35 > C13?
        • If no, remain the same
        • If yes, step C21 down 1 integer
      • Is cell Q35 STILL > C13 after stepping down 1 integer?
        • If no, remain the same
        • If yes, step C21 down 1 integer
      • This logic check would then repeat down to a minimum value of "1"

My code currently works but is ugly and inefficient:

>>> use code - tags <<<

           If Q1.Range("C19") >= "4" Then
            Q1.Range("C21").Value = "4"
            End If
            If Q1.Range("C13").Value = "0" Then
                Q1.Range("C21").Value = "4"
            End If
            If Q1.Range("Q35").Value > Q1.Range("C13").Value Then
                Q1.Range("C21").Value = "3"
            End If
            If Q1.Range("Q35").Value > Q1.Range("C13").Value Then
                Q1.Range("C21").Value = "2"
            End If
            If Q1.Range("Q35").Value > Q1.Range("C13").Value Then
                Q1.Range("C21").Value = "1"
            End If

            If Q1.Range("C19").Value = "3" Then
                Q1.Range("C21").Value = "3"
            End If
            If Q1.Range("C13").Value = "0" Then
                Q1.Range("C21").Value = "3"
            End If
            If Q1.Range("Q35").Value > Q1.Range("C13").Value Then
                Q1.Range("C21").Value = "2"
            End If
            If Q1.Range("Q35").Value > Q1.Range("C13").Value Then
                Q1.Range("C21").Value = "1"
            End If

Thank you for any help!

 

[p45cal]

Presumably there is/are formulae affecting the value(s) in cells Q35 and/or C13 when C21 changes?
See if this goes some way towards your goal:

With Q1
  .Range("C21").Value = Application.Min(.Range("C19").Value, 4)
  Do Until Q1.Range("Q35").Value <= .Range("C13").Value Or .Range("C21").Value = 1
    .Range("C21").Value = .Range("C21").Value - 1
  Loop
End With

If this doesn't do it than attach a workbook with this sheet in it, also perhaps give us a bit of context as to what this is about - it always helps.

 

[Otawata]

Yes, that is correct.
This is perfect!! I apologize, I was working on a de-identified worksheet that could be used for context and my formulas kept breaking in the process.

Thank you kindly for your help - it helps me learn from all you experts