VBA code to fill missing data

[joycejoyce]

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!!

[SamT]

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

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

[joycejoyce]

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

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!!

[SamT]

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.

[joycejoyce]

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!!

[SamT]

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

[joycejoyce]

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: