Cell Automated Population using Excel macro
Hi,
is there a way in macro wherein if a certain cell has value, the other cells will be automatically populated?
Example:
In cell E6 I put the date, cell A6, B6,C6 and D6 will automatically copy the values in A4, B4, C4 and D4. Same goes when I enter the date on cell E7.
Thank you so much in advance.
Software/Hardware used:
MS Excel
is there a way in macro wherein if a certain cell has value, the other cells will be automatically populated?
Example:
In cell E6 I put the date, cell A6, B6,C6 and D6 will automatically copy the values in A4, B4, C4 and D4. Same goes when I enter the date on cell E7.
Thank you so much in advance.
Software/Hardware used:
MS Excel
ASKED:
December 6, 2010 8:33 AM
UPDATED:
December 8, 2010 11:35 AM
Answer Wiki:
The sample code is here: try if it helps u !
sub Auto_Fill()
Dim row_range As Range
Dim ref_value As Variant
Dim o As Double
Set row_range = ActiveSheet.Range("A" & CStr(Application.Rows.Count)) _
.End(xlUp) (here I took A column, u can change as ur demand)
For o = row_range.Row To 1 Step -1
If ActiveSheet.Range("A" & CStr(n)) = "" Then
ActiveSheet.Range("A" & CStr(n)) = ref_value
Else
ref_value = ActiveSheet.Range("A" & CStr(n))
End If
Next n
End Sub
NOTE: U may use a specific worksheet name. The macro works back from the last cell containing data in column A to row 1 - edit row 1 if necessary, on this line: For o = .....
Thanks--
To see all answers submitted to the Answer Wiki: View Answer History.
Hi,
I’m having an error…
Compile error:
Invalid Next control variable reference.
it did’nt work for me.
Hi Eron09.
Rechil didn’t mention it but that code is NOT going to do what you want. It seems to be just an example of another operation that could be similar to what you need.
I’ll take a look into it a little later, and will post some code.
Thank you so much Carlosdl!
Hi Eron09.
Try this code:
Private Sub Worksheet_Change(ByVal Target As Range) Dim myRow As Integer If Left(Target.Address, 2) = "$E" Then myRow = Target.row Range(Replace("a0:d0", "0", myRow)).Value = Range(Replace("a0:d0", "0", myRow - 2)).Value End IfThis is what it does: When you change the value in Cell E6 it will copy the contents of cells A4:D4 to A6:D6. Actually it will do it when you modify any cell in column E. If you write something in E7 it will copy the values from A5:D5 to A7:D7, and so on.
Feel free to ask for clarification or to add any comments if needed.
Sorry, I missed the last line. This is the complete code:
Private Sub Worksheet_Change(ByVal Target As Range) Dim myRow As Integer If Left(Target.Address, 2) = "$E" Then myRow = Target.row Range(Replace("a0:d0", "0", myRow)).Value = Range(Replace("a0:d0", "0", myRow - 2)).Value End If End SubIn the Visual Basic editor, right click on “Sheet1″, select “View code”, and put this code in there.
Let us know how it goes.
I mentioned in Answer Wiki, this is a sample code and u have to change as per ur requirements. This is not the exact one that u want, but it helps u to clear ur concept that u want. Because it is not possible to guess accurately that what the question asker actual wants. Also I mentioned in “NOTES” that how to change according to the query.
“I mentioned in Answer Wiki, this is a sample code and u have to change as per ur requirements”
But the code needed to be replaced entirely.
Google will not always offer a solution for specific situations. Sometimes, if you really want to help, you have to build a solution and not just google it.
You took the code you posted from here:
Auto Populate cells from bottom to top vba
But, the description of the problem that code solves is this:
“HiGuys
i would like write a vba script to fills the blank cells with the value of the cell directly below it starting from top to bottom with a loop”
Which is quite different from what Eron09 asked, isn’t it ?
Oh, I am sorry, I forgot to mention the link here !