How to Fill formula down till last row in column in VBA Excel?

Question

I need the expert help. I wanted to fill the Formula for whole column ("D") based on other column 'C' value start from 2nd row.

Formula is " =IF(ISBLANK(C2),"Still open",TEXT(C2,"mmm")) "

I have tried as below but But its fill down the bottom of the column irrespective of the No data on dependent column. If 'C' Column have 50 records. I wanted to fill till last valid row 50 (Example : no data in column 'C' after 50 row but column D fill row no 1048576) of the table

Dim Output_Sh As Worksheet
Set Output_Sh = ThisWorkbook.Sheets("Output")
Output_Sh.Range("D1").Value = "Month Closed"  ' Heading

Set Rng = Output_Sh.Range("D2:D" & Output_Sh.Range("C2").End(xlDown).Row)
    Rng.FormulaR1C1 = "=IF(ISBLANK(C2),""Still open"",TEXT(C2,""mmm""))"

HI, quick update :

I have tried the formola on colmn D then it's working fine. When I want to apply on Column "AK" then its populate only 1st two row.

Sub InsertAK()
    Dim n As Integer
    Dim Output_Sh As Worksheet
    Dim Rng As Range
 
    Set Output_Sh = ThisWorkbook.Sheets("Output")

    Output_Sh.Columns("AK:AK").Insert
    Output_Sh.Range("AK1").Value = "Month Closed"
    Output_Sh.Range("AK2").Value = "=IF(ISBLANK(C2),""Still open"",TEXT(C2,""mmm""))" ' "Month Closed"
    
     Set Rng = Output_Sh.Cells.Find("*", , , , , xlPrevious)
     Output_Sh.Range("AK3:AK" & Rng.Row).FormulaR1C1 = "=IF(ISBLANK(C3),""Still open"",TEXT(C3,""mmm""))"
End Sub

Output :

Use `xlUp` instead of `xlDown`. If that doesn't solve the problem please clarify if you want the formula in all cells of column D or only the last cell. In the first case you would enter it in D2 and then copy down in a separate instruction. I would ask why (presumably) the same formula would need to be written to many cells on every use of the procedure. In the second case the question arises if you need to refer to C2 from whatever that row will be. — Variatus, Jul 26 '21 at 09:00
And hence it is not advisable to use `xlDown`. You may want to see [How to find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) and then use that to fill the formula. — Siddharth Rout, Jul 26 '21 at 09:18
When I have execute the vba code the formula chnage to as below : =IF(ISBLANK(@$B:$B),"Still open",TEXT(@$B:$B,"mmm")) . I don't know why C2 change to @$B:$B in formula. The wrong value is coming. — skt, Jul 26 '21 at 09:18
Perhaps then you want `Rng.Formula` and not `Rng.FormulaR1C1` then? — shrivallabha.redij, Jul 26 '21 at 09:20
@skt Yes,C2 change to @$B:$B in formula because you use `.FormulaR1C1`. If you change to `formula`, then it will be correct — GGG, Jul 26 '21 at 09:51
@skt I think you don't know how to use `xlDown`.You can test my answer. It should be work fine because I test it myself — GGG, Jul 26 '21 at 09:59
I posted and answer. See if that is what you are trying to achieve? — Siddharth Rout, Jul 26 '21 at 10:07
`skt I think you don't know how to use xlDown.You can test my answer. It should be work fine because I test it myself – GGG 12 mins ago` @GGG: It is not about "How to use" but when to use and when not to use :). See my comment below your answer. — Siddharth Rout, Jul 26 '21 at 10:12

Siddharth Rout · Answer 1 · 2021-07-26T10:14:56.643

If 'C' Column have 50 records. I wanted to fill till last valid row 50

HI Siddharth thanks for reply. Actually I need it to apply on column "AK". Then I have replaced the word from D to AK then I have got the value only 1irst 2 rows only. I don't know where I am doing wrong ? – skt 6 mins ago

From the formula that you are using =IF(ISBLANK(C2),"Still open",TEXT(C2,"mmm")), it is evident that there will be blank values and hence I suggested in the comments above PLEASE do not use xlDown to find the last row.

Is this what you are trying?

Option Explicit

Sub Sample()
    Dim Output_Sh As Worksheet
    Dim lRow As Long
    
    Set Output_Sh = ThisWorkbook.Sheets("Output")
    
    With Output_Sh
        lRow = .Range("C" & .Rows.Count).End(xlUp).Row
        
        .Range("AK1").Value = "Month Closed"  ' Heading
        .Range("AK2:AK" & lRow).Formula = "=IF(ISBLANK(C2),""Still open"",TEXT(C2,""mmm""))"
    End With
End Sub

Thanks you very much for your support – skt Jul 26 '21 at 10:14 — skt, Jul 26 '21 at 10:14

score 0 · Accepted Answer · answered Jul 28 '21 at 15:45

The below is the running solution for the above problme

Sub InsertAK()
    Dim n As Integer
    Dim Output_Sh As Worksheet
    Dim Rng As Range
 
    Set Output_Sh = ThisWorkbook.Sheets("Output")

    Output_Sh.Columns("AK:AK").Insert
    Output_Sh.Range("AK1").Value = "Month Closed"
    Output_Sh.Range("AK2").Value = "=IF(ISBLANK(C2),""Still open"",TEXT(C2,""mmm""))" ' "Month Closed"
    
     n = Output_Sh.Range("B2", Output_Sh.Range("B2").End(xlDown).Rows).Count
     Output_Sh.Range("AK3:AK" & n + 1).FormulaR1C1 = "=IF(ISBLANK(C3),""Still open"",TEXT(C3,""mmm""))"
End Sub

score -1 · Answer 3 · answered Jul 26 '21 at 09:18

-1

You'd get incorrect results with xlDown as described in comment. However, you may get incorrect results with xlUp if last few rows of column C are blank while other data rows are filled in.

Following approach will locate last filled row in the data and accordingly set formula.

Dim Output_Sh As Worksheet
Dim Rng As Range
Set Output_Sh = ThisWorkbook.Sheets("Output")
Output_Sh.Range("D1").Value = "Month Closed"  ' Heading

Set Rng = Output_Sh.Cells.Find("*", , , , , xlPrevious)
Output_Sh.Range("D2:D" & Rng.Row).FormulaR1C1 = "=IF(ISBLANK(C2),""Still open"",TEXT(C2,""mmm""))"

answered Jul 26 '21 at 09:18

shrivallabha.redij

5,832
1
12
27

`However, you may get incorrect results with xlUp if last few rows of column C are blank while other data rows are filled in.` The user wants the formula in Col D only till end of Col C. OP said `If 'C' Column have 50 records. I wanted to fill till last valid row 50` So `.Find` is an overkill in this case. `xlUp` is actually the right way to do it. – Siddharth Rout Jul 26 '21 at 09:20
@SiddharthRout, while that will most certainly be correct. But at times I have seen requirements slide from the original post so covering that scenario. – shrivallabha.redij Jul 26 '21 at 09:24
HI Siddharth thanks for reply. Actually I need it to apply on column "AK". Then I have replaced the word from D to AK then I have got the value only 1irst 2 rows only. I don't know where I am doing wrong ? – skt Jul 26 '21 at 09:59
I have updated my code on main request message at the end. I would like to apply on Different column on 'AK' but its work for 1st 2 row only. – skt Jul 26 '21 at 10:06
Thanks you very much for your support – skt Jul 26 '21 at 10:14

score -3 · Answer 4 · answered Jul 26 '21 at 10:12

Thanks for your support it's working now as below code:

Sub InsertAK()
    Dim n As Integer
    Dim Output_Sh As Worksheet
    Dim Rng As Range
 
    Set Output_Sh = ThisWorkbook.Sheets("Output")

    Output_Sh.Columns("AK:AK").Insert
    Output_Sh.Range("AK1").Value = "Month Closed"
    Output_Sh.Range("AK2").Value = "=IF(ISBLANK(C2),""Still open"",TEXT(C2,""mmm""))" ' "Month Closed"
    
     Set Rng = Output_Sh.Cells.Find("*", , , , , , , xlPrevious)
     n = Output_Sh.Range("C2", Output_Sh.Range("C2").End(xlDown).Rows).Count
     Output_Sh.Range("AK3:AK" & n + 1).FormulaR1C1 = "=IF(ISBLANK(C3),""Still open"",TEXT(C3,""mmm""))"
End Sub

I am sorry but this is the wrong way to solve your problem. Your code will fail if there are blank values in Col C. See my answer. But if you still want to go ahead with your code then be my guest :) — Siddharth Rout, Jul 26 '21 at 10:17

How to Fill formula down till last row in column in VBA Excel?

4 Answers4