Using VBA to enter data into a cell

[patlax02]

Can I use VBA to write a code that would take a data set on one worksheet and enter one of the values into a formula cell on another worksheet - then takes the output of the formula cell and pastes it onto another worksheet - Repeat this process down the data set.

Not sure if that makes senses but I've been trying to record a macro for it today and haven't had any luck.

 

[alansidman]

Post the code you have recorded and advise what needs to be changed.

 

[patlax02]

Sub AutoFill()
'
' AutoFill Macro
'


'
ActiveCell.FormulaR1C1 = "=Inputs!RC[-5]"
Range("C8").Select
Selection.Copy
Sheets("Inputs").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
Range("U2").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Summary").Select
Range("G2").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "=Inputs!R[1]C[-5]"
Range("C8").Select
Selection.Copy
Sheets("Inputs").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
Range("U3").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub




When I run the code the previous cell that I copied/pasted changes to #N/A. But the last cell has the correct number.

 

[alansidman]

I have streamlined your recorded macro to eliminate some of the extraneous steps that the recorder placed in the code. I would remove ActiveCell from the code and replace it with the actual range that your wish for the formula to be placed in, ie. Range("A2") as an example. Without seeing what your worksheets look like I am unable to offer any further assistance.

Sub AutoFill()
'
    ActiveCell.FormulaR1C1 = "=Inputs!RC[-5]"
    Range("C8").Copy
    Sheets("Inputs").Range("U2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
                                              xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Sheets("Summary").Range("G2").ClearContents
    ActiveCell.FormulaR1C1 = "=Inputs!R[1]C[-5]"
    Range("C8").Copy
    Sheets("Inputs").Range("U3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
                                              xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

Please use Code tags when posting VBA code as it make it easier to see and understand.