CODEWITHSUNDEEP
excelvba
Xareyo
Xareyo

Reputation: 1377

VBA divide by a value then display value in relation to the divison

I'm trying to figure the cleanest way of showing, as an example, an initial value say 300 as x and a critical path say 1.5 as y. Both of these values can change, via input on the sheet.

Together with these values we have a resource of a and b. In this scenario a will fill 200 cells within a row and b will fill 100.

As I alluded to before, x and y can change, say, if y is 2, a fills 150 and b fills 150. And if y is 1 then only a fills 300.

Currently i'm using If statements, but I feel this is messy and could potentially lead to endless code in order to cover every possible outcome and I'm in need of a better solution.

Here is a simplistic example of what I'm currently achieving:

Private Sub Example()

    Dim ActiveWB As Worksheet
    Set ActiveWB = ActiveWorkbook.Sheets("Sheet1")
    Dim cell As Range
    Dim a, b, x, y As Double

    x = ActiveWB.Range("A1").Value
    y = ActiveWB.Range("A2").Value
    a = x / y
    b = x - a

        For Each cell In ActiveWB.Range(Cells(3, 1), Cells(3, a))

            If (cell.Column >= 0) And (cell.Column <> x) Then

                If (y = 1) And (a > 0) Then
                   cell.Value = "a"
                    a = a - 1

                ElseIf (y > 1) And (y < 2) And (a > 0) Then
                   cell.Value = "a"
                   a = a - 1
                   If (b > 0) Then
                        cell.Offset(1, 0).Value = "b"
                        b = b - 1
                   End If

               ElseIf (y >= 2) And (y < 2.5) And (a > 0) Then
                   cell.Value = "a"
                   a = a - 1
                   If (b > 0) Then
                        cell.Offset(1, 0).Value = "b"
                        b = b - 1
                   End If

                '..... and so on......

                End If

            End If

        Next cell

End Sub

Any suggestions would be much appreciated. Thank you for your time. Y.

Upvotes: 2

Views: 1763

Answers (2)

K_B
K_B

Reputation: 3678

First of all declaring types should be done for each variable separately:

Dim a, b, x, y As Double

Becomes:

Dim a As Double, b As Double, x As Double, y As Double

Or (this is what I prefer):

Dim a As Double
Dim b As Double
Dim x As Double
Dim y As Double

Second, if your a and b are only used for determining the range width, then they are preferably not of a floating point type. In stead use Integer (under 2^15) or Long:

Dim a As Integer
Dim b As Integer
Dim x As Double
Dim y As Double

Then your value assignment to a and b cannot stay the way they are now, but should read something like:

a = Int(x / y)
b = Int(x - a)

Then I hope your x and y values are restricted to values > 0 and x > y in your sheet. If not then first test for that in your script...

Now last (and your original question), you can assign a value to a complete range of cells if you like:

If a > 0 Then Range(Cells(3, 1), Cells(3, a)).Value = "a"
If b > 0 Then Range(Cells(4, 1), Cells(4, b)).Value = "b"

I dont understand why you take of 1 from a and b, so if that really add something, please elaborate a bit more on the general logic...

Upvotes: 5

Daniel
Daniel

Reputation: 13122

I probably don't completely understand the complexity, but based upon what you shared you can replace your for loop with the following to achieve the same result:

ActiveWB.Range(Cells(3, 1), Cells(3, a)).Value = "a"
If b > 0 then
    ActiveWB.Range(Cells(4, 1), Cells(4, b)).Value = "b"
End If

Upvotes: 2

Related Questions