Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how to add a Formula in VBA

Posted on 2014-11-25
3
Medium Priority
?
158 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 1000 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 52

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 1000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

zlib is a free compression library (a DLL) on which the popular gzip utility is built.  In this article, we'll see how to use the zlib functions to compress and decompress data in memory; that is, without needing to use a temporary file.  We'll be c…
For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
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…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

719 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