belinea2010
New Member
I want to use a VBA Macro to enter a formula in a range of cells if cells on a different worksheet have data.
I have tried many examples found on google but only one has worked but it gives unexpected and wrong results.
I will have 7 formulas to enter in 7 Columns but I just want to get it working for one column to begin with.
I have two worksheets named "Data_Import" and "Pack"
In "Data_Import" only Column A has data.
I need the formula to look at "Data_Import Column A and for each Cell that has data, enter the formula in "Pack" worksheet" Column A Starting at Cell A2 and ending when there is no data in "Data_Import"Column A. The formula cell references should auto increment just as they do if you copy a formula down a range.
As a test I have 4 entries in Column A cell A1 to A4 of Data_Import worksheet.
When I run the macro the results are strange.
The formula that the macro actually enters are:
and the results that each cell displays are:
My macro code is:
I should add that VBA reject my formula so to get VBA to accept it I recorded myself entering the formula
What am i doing wrong?
I have tried many examples found on google but only one has worked but it gives unexpected and wrong results.
I will have 7 formulas to enter in 7 Columns but I just want to get it working for one column to begin with.
I have two worksheets named "Data_Import" and "Pack"
In "Data_Import" only Column A has data.
I need the formula to look at "Data_Import Column A and for each Cell that has data, enter the formula in "Pack" worksheet" Column A Starting at Cell A2 and ending when there is no data in "Data_Import"Column A. The formula cell references should auto increment just as they do if you copy a formula down a range.
As a test I have 4 entries in Column A cell A1 to A4 of Data_Import worksheet.
When I run the macro the results are strange.
The formula that the macro actually enters are:
Code:
(A1) =IF(XFD1048575<>"","NOT BLANK","BLANK") when it should be =IF(A1<>"","NOT BLANK","BLANK")
(A2) =IF(XFD1048576<>"","NOT BLANK","BLANK") when it should be =IF(A2<>"","NOT BLANK","BLANK")
(A3) =IF(#REF!<>"","NOT BLANK","BLANK") when it should be =IF(A3<>"","NOT BLANK","BLANK")
(A4) =IF(#REF!<>"","NOT BLANK","BLANK") when it should be =IF(A4<>"","NOT BLANK","BLANK")
and the results that each cell displays are:
Code:
(A1) BLANK
(A2) BLANK
(A3) #REF!
(A4) #REF!
My macro code is:
Code:
Sub TestFormula()
Dim lastRow As Long
Sheets("Data_Import").Select
'Where is last row of data?
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
'Basic formula to test that, where there is data in the adjacent cell in Column A,
'the formula is copied into and down Column of the Pack Worksheet.
'For this test there are 4 dummy entries in the Data_Import Worksheet.
'Apply formula =IF(A1<>"","NOT BLANK","BLANK")
Sheets("Pack").Select
Range("A2:A" & lastRow).FormulaR1C1 = "=IF(R[-3]C[-1]<>"""",""NOT BLANK"",""BLANK"")"
End Sub
I should add that VBA reject my formula so to get VBA to accept it I recorded myself entering the formula
=IF(A1<>"","NOT BLANK","BLANK")
in a macro which VBA transformed into =IF(R[-3]C[-1]<>"""",""NOT BLANK"",""BLANK"")"
.What am i doing wrong?
Last edited: