Cant get my DO WHILE Loop to function - Help please

[tr41nor]

Hi,

I am trying to produce a function that will update formulas based on the value of i. At present my code will copy and update the formulas across a range (rng1) based on the manual input of i. what i wish to do now is insert a loop that will increase the value of i by one each time until the range (rng1) is full. Within the loop I will need an IF statement to produce an updated formula that takes into account if i is less than or greater than 10.

Please help. been on this for a good few hours now and keep getting Type Mismatch errors.

Many Thanks!!

[Richard Buttrey]

Hi,

Maybe

Sub FillData()

    Dim rng1 As Range
    
    anchor_cell = "B1" 'INSERT the Cell value of the first "Type_value"
    curr_row = Range(anchor_cell).Row
    curr_col = Range(anchor_cell).Column
    last_row = Range(anchor_cell).End(xlDown).Row
    last_col = Range(anchor_cell).Offset(0, 10).Column
    
    i = 1
    
    
    Set rng1 = Range(Cells(curr_row + 1, curr_col + 1), Cells(last_row, last_col))
     
     Do While i < 100 And i <= last_row - 1
     
       If i < 10 Then
            rng1.FormulaR1C1 = "=type0" + CStr(i) + "/Divider"
            i = i + 1
                Else: If i >= 10 Then _
                    rng1.FormulaR1C1 = "=type" + CStr(i) + "/Divider"
                   i = i + 1
        End If
     Loop
End Sub

[tr41nor]

Hi Richard,

Firstly thanks for your response.

It seems functioning correct in terms of the loop. However, I think the problem may be that I am filling the Range of cells each time it loops. What I want to do is fill row 1 of the range in the first loop, row 2 of the range in the second loop, row 3 in ther third loop... etc.

Any ideas on how I can generate a row update rather than a range? Slightly puzzled

Thanks

[Richard Buttrey]

Hi

In that case

Sub FillData()

    Dim rng1 As Range
    
    anchor_cell = "B1" 'INSERT the Cell value of the first "Type_value"
    curr_row = Range(anchor_cell).Row
    curr_col = Range(anchor_cell).Column
    last_row = Range(anchor_cell).End(xlDown).Row
    last_col = Range(anchor_cell).Offset(0, 10).Column
    
    i = 1
    
    
    Set rng1 = Range(Cells(curr_row, curr_col + 1), Cells(curr_row, last_col))
     
     Do While i < 100 And i <= last_row - 1
     
       If i < 10 Then
            rng1.Offset(i, 0).FormulaR1C1 = "=type0" + CStr(i) + "/Divider"
            i = i + 1
                Else: If i >= 10 Then _
                    rng1.Offset(i, 0).FormulaR1C1 = "=type" + CStr(i) + "/Divider"
                   i = i + 1
        End If
     Loop
End Sub