• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using VBA Macro to enter even a basic formula down a range of cells gets wrong and unexpected results?

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:
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:
How about
Code:
    Range("A2:A" & lastRow).FormulaR1C1 = "=IF(Data_Import!RC<>"""",""NOT BLANK"",""BLANK"")"
R[-3] is looking at 3 rows up from the cell with the formula as it starts in row 2 3 rows "Up" is the last row in the sheet as it "Loops" over
C[-1] is one column left of the formula & so that "loops" round to the last column in the sheet
 
How about
Code:
    Range("A2:A" & lastRow).FormulaR1C1 = "=IF(Data_Import!RC<>"""",""NOT BLANK"",""BLANK"")"
R[-3] is looking at 3 rows up from the cell with the formula as it starts in row 2 3 rows "Up" is the last row in the sheet as it "Loops" over
C[-1] is one column left of the formula & so that "loops" round to the last column in the sheet
Hi Fluff

Thank you for your kind reply and suggestion which works perfectly.

What I do not understand is why I can not use =IF(A1<>"","NOT BLANK","BLANK" and when I record myself in a macro entering that the macro then converts in incorrectly?
 
If you put
=IF(A1<>"","NOT BLANK","BLANK")
Into A1 then you have a circular reference as the cell is looking at itself.
If you put that formula in A2 & filled down, then A3 & below would all say "Not Blank"
When referencing a cell on another sheet, you need to include the sheet name, like
=IF('Sheet1'!A1<>"","NOT BLANK","BLANK")
 
This very confusing because if I do
Code:
Sub TestFormula2()
    Selection.Copy
    Range("L3").Select
    ActiveSheet.Paste
End Sub

Where L3 has =IF(Data_Import!A1<>"","NOT BLANK","BLANK") the macro will copy and correctly paste the formula exactly as I write it.

I even tried with my most complicated formula and the above works.
Code:
=SUBSTITUTE(IF(ISNUMBER(SEARCH("wav",Data_Import!A1)),MID(Data_Import!A1,SEARCH("kHz",Data_Import!A1)+4, SEARCH("Wav",Data_Import!A1)-SEARCH("KHz",Data_Import!A1)-5),IF(ISNUMBER(SEARCH("flac",Data_Import!A1)),MID(Data_Import!A1,SEARCH("kHz",Data_Import!A1)+4, SEARCH("flac",Data_Import!A1)-SEARCH("KHz",Data_Import!A1)-5),IF(ISNUMBER(SEARCH("mp3",Data_Import!A1)),MID(Data_Import!A1,SEARCH("kHz",Data_Import!A1)+4, SEARCH("mp3",Data_Import!A1)-SEARCH("KHz",Data_Import!A1)-5),IF(ISNUMBER(SEARCH("mogg",Data_Import!A1)),MID(Data_Import!A1,SEARCH("kHz",Data_Import!A1)+4, SEARCH("mogg",Data_Import!A1)-SEARCH("KHz",Data_Import!A1)-5),"")))),"_"," ")
 
I would far prefer to use old style formulas but VBA will not allow me to do so.

So I have tried selecting R1C1 reference style in Excel Options so I can see the R1C1 versions of my formulas and this is the result.

This is an auto counter that looks to cell A1 of the Data_Import worksheet and if the cell has data it inserts a counter in the cell that this formula is entered.
Old style formula - =IF(Data_Import!A1<>"",TEXT(ROW(Data_Import!A1),"000"),"")
R1C1 Style = "=IF(Data_Import!R[-1]C<>"",TEXT(ROW(Data_Import!R[-1]C),"000"),"")

So I create a sub to call as a macro to insert the R1C1 formula in Cell J" of the "Pack" worksheet

Code:
Sub InsertFormulaTest()

Sheets("Pack").Select
Range("J2").Select
ActiveCell.FormulaR1C1 = "=IF(Data_Import!R[-1]C<>"",TEXT(ROW(Data_Import!R[-1]C),"000"),"""")"

End Sub
But it gives the error

Compile error: Syntax error and hightlights the 000 part of the formula

I have also tried..

Code:
Sub InsertFormulaTest()

Sheets("Pack").Select

Range("J2").Select
Range("J2").FormulaR1C1 = "=IF(Data_Import!R[-1]C<>"",TEXT(ROW(Data_Import!R[-1]C),"000"),"")"

End Sub
and...

Code:
Sub InsertFormulaTest()

Sheets("Pack").Select

Range("J2").FormulaR1C1 = "=IF(Data_Import!R[-1]C<>"",TEXT(ROW(Data_Import!R[-1]C),"000"),"")"

End Sub

which gives the same error

It does not make any sense to me that it is throwing an error on its own code
 
You need to double-up the quotes inside the formula like
Code:
ActiveCell.FormulaR1C1 = "=IF(Data_Import!R[-1]C<>"""",TEXT(ROW(Data_Import!R[-1]C),""000""),"""")"
 
Is that a hard rule or is there a way of knowing where to double-up the quotes inside a formula?

I have to learn this.

This is a cut down example of one of my more complicated formulas in standard format (it is actually twice as long)
Code:
=SUBSTITUTE(IF(ISNUMBER(SEARCH("wav",Data_Import!A8)),MID(Data_Import!A8,SEARCH("kHz",Data_Import!A8)+4, SEARCH("Wav",Data_Import!A8)-SEARCH("KHz",Data_Import!A8)-5),IF(ISNUMBER(SEARCH("flac",Data_Import!A8)),MID(Data_Import!A8,SEARCH("kHz",Data_Import!A8)+4, SEARCH("flac",Data_Import!A8)-SEARCH("KHz",Data_Import!A8)-5),"")),"_"," ")
Then with R1C1 selected in Excels options
Code:
=SUBSTITUTE(IF(ISNUMBER(SEARCH("wav",Data_Import!R[-1]C[-6])),MID(Data_Import!R[-1]C[-6],SEARCH("kHz",Data_Import!R[-1]C[-6])+4, SEARCH("Wav",Data_Import!R[-1]C[-6])-SEARCH("KHz",Data_Import!R[-1]C[-6])-5),IF(ISNUMBER(SEARCH("flac",Data_Import!R[-1]C[-6])),MID(Data_Import!R[-1]C[-6],SEARCH("kHz",Data_Import!R[-1]C[-6])+4, SEARCH("flac",Data_Import!R[-1]C[-6])-SEARCH("KHz",Data_Import!R[-1]C[-6])-5),"")),"_"," ")
If I double all of the the quotes it throws an error
Code:
=SUBSTITUTE(IF(ISNUMBER(SEARCH(""wav"",Data_Import!A8)),MID(Data_Import!A8,SEARCH(""kHz"",Data_Import!A8)+4, SEARCH(""Wav"",Data_Import!A8)-SEARCH(""KHz"",Data_Import!A8)-5),IF(ISNUMBER(SEARCH(""flac"",Data_Import!A8)),MID(Data_Import!A8,SEARCH(""kHz"",Data_Import!A8)+4, SEARCH(""flac"",Data_Import!A8)-SEARCH(""KHz"",Data_Import!A8)-5),"""")),""_"","" "")
 
Hi Fluff

I wasn't hinting for you to convert if for me as I really do need to learn as I can not keep asking in this forum every time I need a formula to work but as you asked this is the code. I've kinda jumped in the deep end with R1C1 and find it completely confusing.

Code in normal mode:
Code:
'=SUBSTITUTE(IF(ISNUMBER(SEARCH("wav",Data_Import!A1)),MID(Data_Import!A1,SEARCH("kHz",Data_Import!A1)+4, SEARCH("Wav",Data_Import!A1)-SEARCH("KHz",Data_Import!A1)-5),IF(ISNUMBER(SEARCH("flac",Data_Import!A1)),MID(Data_Import!A1,SEARCH("kHz",Data_Import!A1)+4, SEARCH("flac",Data_Import!A1)-SEARCH("KHz",Data_Import!A1)-5),IF(ISNUMBER(SEARCH("mp3",Data_Import!A1)),MID(Data_Import!A1,SEARCH("kHz",Data_Import!A1)+4, SEARCH("mp3",Data_Import!A1)-SEARCH("KHz",Data_Import!A1)-5),IF(ISNUMBER(SEARCH("mogg",Data_Import!A1)),MID(Data_Import!A1,SEARCH("kHz",Data_Import!A1)+4, SEARCH("mogg",Data_Import!A1)-SEARCH("KHz",Data_Import!A1)-5),"")))),"_"," ")
Code when R1C1 is enabled in Excel options:
Code:
'=SUBSTITUTE(IF(ISNUMBER(SEARCH("wav",Data_Import!R[-1]C[-6])),MID(Data_Import!R[-1]C[-6],SEARCH("kHz",Data_Import!R[-1]C[-6])+4, SEARCH("Wav",Data_Import!R[-1]C[-6])-SEARCH("KHz",Data_Import!R[-1]C[-6])-5),IF(ISNUMBER(SEARCH("flac",Data_Import!R[-1]C[-6])),MID(Data_Import!R[-1]C[-6],SEARCH("kHz",Data_Import!R[-1]C[-6])+4, SEARCH("flac",Data_Import!R[-1]C[-6])-SEARCH("KHz",Data_Import!R[-1]C[-6])-5),IF(ISNUMBER(SEARCH("mp3",Data_Import!R[-1]C[-6])),MID(Data_Import!R[-1]C[-6],SEARCH("kHz",Data_Import!R[-1]C[-6])+4, SEARCH("mp3",Data_Import!R[-1]C[-6])-SEARCH("KHz",Data_Import!R[-1]C[-6])-5),IF(ISNUMBER(SEARCH("mogg",Data_Import!R[-1]C[-6])),MID(Data_Import!R[-1]C[-6],SEARCH("kHz",Data_Import!R[-1]C[-6])+4, SEARCH("mogg",Data_Import!R[-1]C[-6])-SEARCH("KHz",Data_Import!R[-1]C[-6])-5),"")))),"_"," ")
 
Do you mean the complete macro code? I have not really got past the basic stage because as soon as I entered the formula in the VB editor it threw a wobble so I am trying to get VB to accept the formula first. This is the basic macro code ran with the cell selected :
Code:
Sub Enter_TestFormula()
Range("J2").Formula = "=SUBSTITUTE(IF(ISNUMBER(SEARCH("wav",Data_Import!A1)),MID(Data_Import!A1,SEARCH("kHz",Data_Import!A1)+4, SEARCH("Wav",Data_Import!A1)-SEARCH("KHz",Data_Import!A1)-5),IF(ISNUMBER(SEARCH("flac",Data_Import!A1)),MID(Data_Import!A1,SEARCH("kHz",Data_Import!A1)+4, SEARCH("flac",Data_Import!A1)-SEARCH("KHz",Data_Import!A1)-5),IF(ISNUMBER(SEARCH("mp3",Data_Import!A1)),MID(Data_Import!A1,SEARCH("kHz",Data_Import!A1)+4, SEARCH("mp3",Data_Import!A1)-SEARCH("KHz",Data_Import!A1)-5),IF(ISNUMBER(SEARCH("mogg",Data_Import!A1)),MID(Data_Import!A1,SEARCH("kHz",Data_Import!A1)+4, SEARCH("mogg",Data_Import!A1)-SEARCH("KHz",Data_Import!A1)-5),"")))),"_"," ")"
End Sub
 
Thanks for that, if you double up all the internal quotes, then that should work.
Code:
Range("J2").Formula = "=SUBSTITUTE(IF(ISNUMBER(SEARCH(""wav"",Data_Import!A1)),MID(Data_Import!A1,SEARCH(""kHz"",Data_Import!A1)+4, SEARCH(""Wav"",Data_Import!A1)-SEARCH(""KHz"",Data_Import!A1)-5),IF(ISNUMBER(SEARCH(""flac"",Data_Import!A1)),MID(Data_Import!A1,SEARCH(""kHz"",Data_Import!A1)+4, SEARCH(""flac"",Data_Import!A1)-SEARCH(""KHz"",Data_Import!A1)-5),IF(ISNUMBER(SEARCH(""mp3"",Data_Import!A1)),MID(Data_Import!A1,SEARCH(""kHz"",Data_Import!A1)+4, SEARCH(""mp3"",Data_Import!A1)-SEARCH(""KHz"",Data_Import!A1)-5),IF(ISNUMBER(SEARCH(""mogg"",Data_Import!A1)),MID(Data_Import!A1,SEARCH(""kHz"",Data_Import!A1)+4, SEARCH(""mogg"",Data_Import!A1)-SEARCH(""KHz"",Data_Import!A1)-5),"""")))),""_"","" "")"
 
Hi Fluff

I wasn't hinting for you to convert if for me as I really do need to learn as I can not keep asking in this forum every time I need a formula to work but as you asked this is the code. I've kinda jumped in the deep end with R1C1 and find it completely confusing.

Code in normal mode:
Code:
'=SUBSTITUTE(IF(ISNUMBER(SEARCH("wav",Data_Import!A1)),MID(Data_Import!A1,SEARCH("kHz",Data_Import!A1)+4, SEARCH("Wav",Data_Import!A1)-SEARCH("KHz",Data_Import!A1)-5),IF(ISNUMBER(SEARCH("flac",Data_Import!A1)),MID(Data_Import!A1,SEARCH("kHz",Data_Import!A1)+4, SEARCH("flac",Data_Import!A1)-SEARCH("KHz",Data_Import!A1)-5),IF(ISNUMBER(SEARCH("mp3",Data_Import!A1)),MID(Data_Import!A1,SEARCH("kHz",Data_Import!A1)+4, SEARCH("mp3",Data_Import!A1)-SEARCH("KHz",Data_Import!A1)-5),IF(ISNUMBER(SEARCH("mogg",Data_Import!A1)),MID(Data_Import!A1,SEARCH("kHz",Data_Import!A1)+4, SEARCH("mogg",Data_Import!A1)-SEARCH("KHz",Data_Import!A1)-5),"")))),"_"," ")
Code when R1C1 is enabled in Excel options:
Code:
'=SUBSTITUTE(IF(ISNUMBER(SEARCH("wav",Data_Import!R[-1]C[-6])),MID(Data_Import!R[-1]C[-6],SEARCH("kHz",Data_Import!R[-1]C[-6])+4, SEARCH("Wav",Data_Import!R[-1]C[-6])-SEARCH("KHz",Data_Import!R[-1]C[-6])-5),IF(ISNUMBER(SEARCH("flac",Data_Import!R[-1]C[-6])),MID(Data_Import!R[-1]C[-6],SEARCH("kHz",Data_Import!R[-1]C[-6])+4, SEARCH("flac",Data_Import!R[-1]C[-6])-SEARCH("KHz",Data_Import!R[-1]C[-6])-5),IF(ISNUMBER(SEARCH("mp3",Data_Import!R[-1]C[-6])),MID(Data_Import!R[-1]C[-6],SEARCH("kHz",Data_Import!R[-1]C[-6])+4, SEARCH("mp3",Data_Import!R[-1]C[-6])-SEARCH("KHz",Data_Import!R[-1]C[-6])-5),IF(ISNUMBER(SEARCH("mogg",Data_Import!R[-1]C[-6])),MID(Data_Import!R[-1]C[-6],SEARCH("kHz",Data_Import!R[-1]C[-6])+4, SEARCH("mogg",Data_Import!R[-1]C[-6])-SEARCH("KHz",Data_Import!R[-1]C[-6])-5),"")))),"_"," ")

belinea2010, please check the below line of VBA Code.
Code:
Activecell.Formula="=SUBSTITUTE(IF(ISNUMBER(SEARCH(""wav"",Data_Import!A1)),MID(Data_Import!A1,SEARCH(""kHz"",Data_Import!A1)+4, SEARCH(""Wav"",Data_Import!A1)-SEARCH(""KHz"",Data_Import!A1)-5),IF(ISNUMBER(SEARCH(""flac"",Data_Import!A1)),MID(Data_Import!A1,SEARCH(""kHz"",Data_Import!A1)+4, SEARCH(""flac"",Data_Import!A1)-SEARCH(""KHz"",Data_Import!A1)-5),IF(ISNUMBER(SEARCH(""mp3"",Data_Import!A1)),MID(Data_Import!A1,SEARCH(""kHz"",Data_Import!A1)+4, SEARCH(""mp3"",Data_Import!A1)-SEARCH(""KHz"",Data_Import!A1)-5),IF(ISNUMBER(SEARCH(""mogg"",Data_Import!A1)),MID(Data_Import!A1,SEARCH(""kHz"",Data_Import!A1)+4, SEARCH(""mogg"",Data_Import!A1)-SEARCH(""KHz"",Data_Import!A1)-5),"""")))),""_"","" "")"
 
Thank you fluff and S,Das

Both methods work but in S.Das's case I had to add Range("J2").Select which is no problem. If one method better than the other?
Fluff so it is a case of going through each formula and doubling up all internal quotes within my formulas?
 
Back
Top