CODEWITHSUNDEEP
vbaexcel
Igor Osipov
Igor Osipov

Reputation: 157

Auto Fill Column

I have just started to use VBA. Just wanted to know how to fill Column that starting from range("K5") and the value taken from ComboBox1. For example: If range("K5") = 22 then range("K6") = 23... The final number is value of ComboBox2.

This is my code for now...

Dim temperature_1 As Integer                     'from temperature_1
Dim temperature_2 As Integer                     'to temperature_2
Dim k As Integer
Dim l As Integer
Dim range_degrees As Integer                      


temperature_1 = Sheet1.ComboBox1
temperature_2 = Sheet1.ComboBox2
range_degrees = temperature_2 - temperature_1

For k = range("K5") To range_degrees                     
    For l = temperature_1 To temperature_2                     
     ......
    Next l
Next k

Upvotes: 1

Views: 116

Answers (2)

user4039065
user4039065

Reputation:

If you truly wish to 'Auto Fill' the column, I would simply populate the start of the range and fill with a series.

with activesheet
    .range("K5") = temperature_1
    .range("K5").DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=temperature_2, Trend:=False
end with

If you wanted to increment in something like 0.05°, change the Step:=1 parameter to your desired increment. If you wanted to fill across a row instead of down a column, change the Rowcol:=xlColumns to Rowcol:=xlRows. Blocking in values like this with a series is much more efficient than looping through them.

Upvotes: 3

kitap mitap
kitap mitap

Reputation: 709

Private Sub FillRange()
    Dim temperature_1 As Integer                     'from temperature_1
    Dim temperature_2 As Integer                     'to temperature_2
    Dim k As Integer
    Dim l As Integer
    Dim range_degrees As Integer


    temperature_1 = Sheet1.ComboBox1
    temperature_2 = Sheet1.ComboBox2
    range_degrees = temperature_2 - temperature_1

    Range("K5").Select

        For l = temperature_1 To temperature_2
           ActiveCell.Value = l
           ActiveCell.Offset(1, 0).Select
        Next l
End Sub

Upvotes: 0

Related Questions