Solved

how to add a Formula in VBA

Posted on 2014-11-25
3
135 Views
Last Modified: 2014-11-26
I have a spreadsheet that I want to add a formula to when i run some VBA.   Basically, i want this formula to be entered when i run the code:

=IFERROR(INDEX(SHEET1!A:C,MATCH(Q2,SHEET1!A:A,0)+COUNTIF(SHEET1!A:A,Q2)-1,2),"")

I know how to write it manually, i just don't know how to do it via VBA.

I tried this:

Sheets("This Sheet").Select
Dim lastrow as Long
lastrow = [Q8000].End(xlUp).Row
For i = lastrow To 1 Step -1
If i = 1 then
GoTo Done
End If

If Cells(i, "Q").Value <> "" Then
Cells(i, "B").Value = "=IFERROR(INDEX(SHEET1!A:C,MATCH(Q2,SHEET1!A:A,0)+COUNTIF(SHEET1!A:A,Q2)-1,2),"")"
End If

Next i
Done :

As you probably know, that didn't work.    I have the same problem with vlookup like this:

=vlookup(I2,sheet1!A:AP,5,False)

That didn't work either.  I am not good at the formula thing in VBA.

Thanks in advance
0
Comment
Question by:farmingtonis
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
SimonAdept earned 250 total points
ID: 40465808
You need to set the .Formula property rather than the .value property.

Cells(i, "B").Formula = "=IFERROR(INDEX(SHEET1!A:C,MATCH(Q2,SHEET1!A:A,0)+COUNTIF(SHEET1!A:A,Q2)-1,2),"")"

But, you still have some work to do, because you probably need to refer to the current row in the formula rather than "Q2".

You can do this by inserting your i value into the formula for each row instead of the static value "2".
Cells(i, "B").Formula = "=IFERROR(INDEX(SHEET1!A:C,MATCH(Q" & i & ",SHEET1!A:A,0)+COUNTIF(SHEET1!A:A,Q" & i & ")-1,2),"")"

The above should work. Others may post a faster method though... thinking of filtering the range for non-empty values first and filling down?
0
 
LVL 48

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 250 total points
ID: 40466229
Instead of a loop

pls try the " char inside a string must be doubled see formula

Cells(2, "B").Formula = "=IFERROR(INDEX(SHEET1!A:C,MATCH(Q2,SHEET1!A:A,0)+COUNTIF(SHEET1!A:A,Q2)-1,2),"""")"
Range(Cells(2, "B"), Cells(LastRow, "B")).FillDown

Regards
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40466286
I didn't actually test the code yesterday. Yes, it does need doubled quote marks within the formula.

The loop method is slower than fill down, but more configurable (if you only want the formula on lines where a particular condition exists).
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This tutorial is about how to put some of your C++ program's functionality into a standard DLL, and how to make working with the EXE and the DLL simple and seamless.   We'll be using Microsoft Visual Studio 2008 and we will cut out the noise; that i…
For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now