Cell Automated Population using Excel macro

135 pts.
Tags:
Excel VBA
Microsoft Excel 2003
Microsoft Excel macros
Visual Basic
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
Asked: Last updated:
Related Questions
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

The sample code is here: try if it helps you!

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, you can change as your 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: You 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–

Discuss This Question: 10  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Eron09
    Hi, I'm having an error... Compile error: Invalid Next control variable reference.
    135 pointsBadges:
    report
  • Eron09
    it did'nt work for me.
    135 pointsBadges:
    report
  • carlosdl
    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.
    84,580 pointsBadges:
    report
  • Eron09
    Thank you so much Carlosdl!
    135 pointsBadges:
    report
  • 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 If
    This 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.
    84,580 pointsBadges:
    report
  • carlosdl
    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 Sub
    In the Visual Basic editor, right click on "Sheet1", select "View code", and put this code in there. Let us know how it goes.
    84,580 pointsBadges:
    report
  • Subhendu Sen
    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.
    130,760 pointsBadges:
    report
  • carlosdl
    "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 ?
    84,580 pointsBadges:
    report
  • Subhendu Sen
    Oh, I am sorry, I forgot to mention the link here !
    130,760 pointsBadges:
    report
  • linecp123
    i need to have 3 columns, title, tier, and rate, i would like to create a sheet for eg
    if i enter the title, then the tier automatically comes up and then the rate populates it self in the 3rd column
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: