VBA code to fill missing data

Hi, I am new to EXCEL VBA and have got a question on the usage of it.
I have a dataset containing a lot of missing values. I actually computed the linear regression for 2 variables to impute the missing data.
I would like to ask if there is any way that I can use VBA to help me automatically fill the data with regression equation of a cell under, say, column A, with the respective value under column B.

For example, the linear regression is y=0.7x+10
I have a missing data on cell B7, I would like to impute B7 with 0.7(cell C7)+10. And then repetitively do the same thing for the column A till it meets, say, cell B100.
Is there any way that I can use VBA to carry it out? Thank you!!

Put this sub in a standard Module and run it from the Tools >> Macro Menu

Code:

---

Sub fillBwithA_regressive()
Dim rw As Long
With Sheets("Sheet?") 'Edit as needed
For rw = Cells(Rows.Count, "B").End(xlUp).Row to Cells(Rows.Count, "A").End(xlUp).Row
Cells(rw, "B") = Cells(rw, "A") * .07 + 10
Next rw
End With
End Sub

---

Quote:

---

Originally Posted by SamT

Put this sub in a standard Module and run it from the Tools >> Macro Menu

Code:

---

Sub fillBwithA_regressive()
Dim rw As Long
With Sheets("Sheet?") 'Edit as needed
For rw = Cells(Rows.Count, "B").End(xlUp).Row to Cells(Rows.Count, "A").End(xlUp).Row
Cells(rw, "B") = Cells(rw, "A") * .07 + 10
Next rw
End With
End Sub

---

---

Thank you so much Sam!! I am so sorry that I missed out something in my description. Say for column A, form row 7 to 100, i wanna do the calculation for the cell that hv missing values but not those which have values on it already. How the code should be amended? Thanks!!

Quote:

---

Say for column A, form row 7 to 100, i wanna do the calculation for the cell that hv missing values

---

Say What? More detail please.

Quote:

---

Originally Posted by SamT

Say What? More detail please.

---

	A	B
1	1	2
2	3	3
3		6
4		8
5	5	10

Like this, there are missing values in cell A3 & A4. I would like to do impute A3 & A4 only. So I am wondering could there be some methods that VBA can help me to do that caculation (i.e. 0.7 * (respective cell B) + 10) only to cell in column A that have missing values. i have like around 1000 rows in my excel sheet. This would be a difficult work to check the missing values manually. Thanks so much!!

Code:

---

Sub fillAwithB_regressive()
    Dim rw As Long
    With Sheets("Sheet?") 'Edit as needed
for rw = 1To Cells(Rows.Count, "B").End(xlUp).Row
            If Cells(rw, "A") = "" Then Cells(rw, "A") = Cells(rw, "B") * .07 + 10
        Next rw
    End With
End Sub

---

Quote:

---

Originally Posted by SamT

Code:

---

Sub fillAwithB_regressive()
    Dim rw As Long
    With Sheets("Sheet?") 'Edit as needed
for rw = 1To Cells(Rows.Count, "B").End(xlUp).Row
            If Cells(rw, "A") = "" Then Cells(rw, "A") = Cells(rw, "B") * .07 + 10
        Next rw
    End With
End Sub

---

---

Thank you so much!! Deeply appreciated:bow::bow: