Solved

how to add a Formula in VBA

Posted on 2014-11-25
3
141 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:
Simon 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 49

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:Simon
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I will show how to use the Ribbon IDs Tool Window to assign the built-in Office icons to a ribbon button.  This tool will help us to find the OfficeImageId that corresponds to our desired built-in Office icon. The tool is part of…
As more and more people are shifting to the latest .Net frameworks, the windows presentation framework is gaining importance by the day. Many people are now turning to WPF controls to provide a rich user experience. I have been using WPF controls fo…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
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…

776 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