Continue macro after encountering a blank row

[gomes123]

In Column O, starting from Cell O3, the value is =H3/$D$3. I then drag and drop the formula downwards. So Cell O4's value is =H4/$D$3 and so on etc..

But whenever I encounter a blank row, for example if Row 6 is a blank row, I would like the formula in Column O to "reset". So for Cell O7's value, I would like it to be =H7/$D$7, (instead of H7/$D$3) and then drag and drop the values until there is another blank row, then "resetting the value" in Column O again.

The problem with the excel macro below, is that it doesnt lock the Cell D values. So the output would be =H3/$D$3, =H4/$D$4, =H5/$D$5 etc.

How would I fix the macro so the output would be =H3/$D$3, =H4/$D$3, =H5/$D$3 etc. ?

Thanks.

Sub ResetFormulaOnBlankRow()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim denominator As Double
    
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change the sheet name as needed
    lastRow = ws.Cells(ws.Rows.Count, "H").End(xlUp).Row
    
    For i = 3 To lastRow
        If ws.Cells(i, 1).Value = "" Then
            denominator = ws.Cells(i, 4).Value
        Else
            ws.Cells(i, 15).Formula = "=" & "H" & i & "/" & "$D$" & i
        End If
    Next i

End Sub

 

[rlv01]

Perhaps something like this.

Sub ResetFormulaOnBlankRow()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim denominator As Double
    Dim FRow As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")            ' Change the sheet name as needed
    lastRow = ws.Cells(ws.Rows.Count, "H").End(xlUp).Row

    FRow = 3
    For i = 3 To lastRow
        If ws.Cells(i, 1).Value = "" Then
            denominator = ws.Cells(i, 4).Value
            FRow = i + 1
        Else
            ws.Cells(i, 15).Formula = "=" & "H" & i & "/" & "$D$" & FRow
            ws.Cells(i, 16).Value = "'=" & "H" & i & "/" & "$D$" & FRow
        End If
    Next i
End Sub

 

[gomes123]

Thanks

Perhaps something like this.

Sub ResetFormulaOnBlankRow()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim denominator As Double
    Dim FRow As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")            ' Change the sheet name as needed
    lastRow = ws.Cells(ws.Rows.Count, "H").End(xlUp).Row

    FRow = 3
    For i = 3 To lastRow
        If ws.Cells(i, 1).Value = "" Then
            denominator = ws.Cells(i, 4).Value
            FRow = i + 1
        Else
            ws.Cells(i, 15).Formula = "=" & "H" & i & "/" & "$D$" & FRow
            ws.Cells(i, 16).Value = "'=" & "H" & i & "/" & "$D$" & FRow
        End If
    Next i
End Sub

Thanks, most appreciated! It works perfectly, in addition to the extra column (P) that you provided!

I tried modifying the code above to do some extra tasks, and there seems to be a problem.
So in Column O, the cell O3 starts as =H3/$D$3, and dragging down it works perfectly.
I tried modifying the code to do the following:
Column P, cell P4 value to be =H4/$D$4, and drag it down (until there is a blank row)
Column Q, cell Q5 value to be =H5/$D$5, and drag it down (until there is a blank row)
And subsequently the same in Column R (and subsequent columns), Cell R6 = H6/$D$6, until there is a blank row.

So I modified the code

 ws.Cells(i + 1, 16).Formula = "=" & "H" & i + 1 & "/" & "$D+1$" & FRow

But it doesn't seem to work (seems to be a problem with $D+1$), and it continues down even when there is a blank row.

Most appreciated for any help. Thanks!

 

[rlv01]

Actually the line of code for col P was just something I put in for myself to write the formulas as text so I could more easily see if the col O formula was being created correctly. I just forgot to take it out when I posted it . I don't understand what you are trying to accomplish with your modification. Pehaps you can provide a visual example of the result you want, using XL2BB as I have done here.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2	Data Col 1	Data Col 2	Data Col 3	Data Col 4	Data Col 5	Data Col 6	Data Col 7	Data Col 8	Data Col 9	Data Col 10	Data Col 11	Data Col 12	Data Col 13	Data Col 14	Data Col 15	Data Col 16	Data Col 17	Data Col 18
3	342	662	701	477	327	597	653	881	928	540	499	213	167	672	1.84696017	657	645	551
4	664	134	923	943	615	227	200	491	518	266	782	455	994	465	1.0293501	896	976	630
5	912	859	358	421	687	486	371	592	897	486	966	703	921	556	1.24109015	540	415	353
6		445	343	534	912	802	513	613	280	576	619	890	907	648	882	132	720	109
7	671	161	764	414	386	123	203	518	112	713	936	717	413	262	1.25120773	379	826	859
8		522	555	370	183	993	473	484	239	702	291	187	890	946	917	243	332	905
9		288	403	411	920	647	403	884	913	298	607	788	685	186	188	573	673	160
10	331	925	797	410	766	496	853	880	893	819	298	128	472	786	2.14634146	991	487	913
11	435	206	685	772	223	977	964	595	122	220	601	765	719	616	1.45121951	295	663	914
Sheet1

Cell Formulas
Range	Formula
O3:O5	O3	=H3/$D$3
O7	O7	=H7/$D$7
O10:O11	O10	=H10/$D$10

 

[gomes123]

Actually the line of code for col P was just something I put in for myself to write the formulas as text so I could more easily see if the col O formula was being created correctly. I just forgot to take it out when I posted it . I don't understand what you are trying to accomplish with your modification. Pehaps you can provide a visual example of the result you want, using XL2BB as I have done here.

I see, thanks anyways! Yeah, I initially thought you put it out there so it'd be easier for me to visualise and see what is going on with the coding.

Thanks, this is basically what I'm trying to do. The macro you posted earlier works fine for Column O, now I'm just trying to get it to work for Column P, Q, R, S, etc.. and beyond.
Column P, cell P4 value starts at =H4/$D$4, and drag it down (until there is a blank row)
Column Q, cell Q5 value starts at =H5/$D$5, and drag it down (until there is a blank row)

If there is a blank row (see attached image), the macro "resets" and starts again from O, this time cell O8 would be =H8/$D$8, and drag it down.
Cell P9 = H9/$D$9 and drag down etc.

Most appreciated, thanks!