Solved

Pasting in these complicated formulas (part II)

Posted on 2014-04-13
7
352 Views
Last Modified: 2014-04-14
Hi All,

I asked how to paste in these formulas before in part I of the code and thought I had the answer:

(1)  "= IIf(Evaluate(Worksheets(""Tickets"").Cells(r1, ""A"").Value * Worksheets(""Tickets"").Cells(r1, ""B"").Value) < Evaluate(Worksheets(""OtherSheets"").Cells(r1, ""D"")), 1, 0)"

(2)"a = IIf(Evaluate(Worksheets(""Tickets"").Cells(r1, ""A"").Value * Worksheets(""OtherSheet"").Range(""$d$1"").Value) < Evaluate(Worksheets(""thisSheet"").Range(""$e$1"")), 1, 0)"

...and this works except I get the "answer" of 1 or 0 right away.  

The next step is to actually have these formulas pasted in so that they are evaluating.

In other words, after the operation is done I don't want to just to see a "1" or "0" in the cell.  I would like to see the whole formula.

In other, other words, I would like it in this form:

"= if(constants!AO" & i & ">constants!AM" & i & ", 1, 0)"


I ordinarily can't even do the simplest of these kinds of "paste ins" so I have zero percent chance right now with these things.

thanks for your help!
0
Comment
Question by:BostonBob
[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
7 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 39999880
Try putting a SINGLE QUOTE in front of the formula

' "= IIf(Evaluate(Worksheets(""Tickets"").Cells(r1, ""A"").Value * Worksheets(""Tickets"").Cells(r1, ""B"").Value) < Evaluate(Worksheets(""OtherSheets"").Cells(r1, ""D"")), 1, 0)"


mlmcc
0
 
LVL 35

Expert Comment

by:Bembi
ID: 40000162
You want to see the formula as text, or you want to see the formala as it is in the cell?
First question is answered by mimcc, second is just to switch from the results view to the formula view (either via File - Options) or you add the command to your ribbon bar to switch via button....
0
 

Author Comment

by:BostonBob
ID: 40000321
I would like to see the formula in the cell...ready to go.

In other words if i have it in this form to begin with:

(1)  =if(constantsAO10>ConstantsAM10, 1, 0)  for row 10
       =if(constantsAO11>ConstantsAM11, 1, 0) for row 11
       =if(ConstantsAO252>ConstantsAM252, 1, 0) for row 252

Will become when pasting in the formula (when triggered to do so):

(2) = "= if(constants!AO" & i & ">constants!AM" & i & ", 1, 0)"


So I would like my equation above to be in the form of (2).


Side note:  Either I did not ask the question properly above (probable) or there is another way to do what you (mlmcc) did above as what I am trying to do.  Please inform either what the correct way to "ask" the question or if there is indeed another way of doing the same formula as in my example in (2).

Thanks!
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 40000362
Assuming that you want the formula in a worksheet cell, you might do it like shown in the snippet below. I show two ways. In the first, the worksheet names are as given. In the second, the worksheet names are in variables.
Sub Formulator()
Dim r1 As Long
Dim frmla1 As String, frmla2 As String, sTickets As String, sOtherSheet As String, sThisSheet As String
r1 = 10
frmla1 = "=IF(Tickets!A" & r1 & "*Tickets!B" & r1 & "<OtherSheet!D" & r1 & ",1,0)"
frmla2 = "=IF(Tickets!A" & r1 & "*OtherSheet!$D$1<thisSheet!$E$1,1,0)"
Range("A10").Formula = frmla1
Range("B10").Formula = frmla2

sTickets = "'" & Worksheets("Tickets").Name & "'!"
sOtherSheet = "'" & Worksheets("OtherSheet").Name & "'!"
sThisSheet = "'" & Worksheets("thisSheet").Name & "'!"
frmla1 = "=IF(" & sTickets & "A" & r1 & "*" & sTickets & "B" & r1 & "<" & sOtherSheet & "D" & r1 & ",1,0)"
frmla2 = "=IF(" & sTickets & "A" & r1 & "*" & sOtherSheet & "$D$1<" & sThisSheet & "$E$1,1,0)"
Range("A10").Formula = frmla1
Range("B10").Formula = frmla2
End Sub

Open in new window

0
 
LVL 81

Expert Comment

by:byundt
ID: 40000385
If you don't know which row you'll be pasting the formula in, then you could use R1C1 notation instead of A1 when building the formula. With R1C1 addressing, RC1 is equivalent to $Axxx where xxx automatically updates to the number of the row containing the formula. This eliminates the need for variable r1 when building your formula.

Note that I needed to make the reference to column A absolute in the above example because I don't know which column will receive the formula. If I knew that column F would receive the formula then I could use RC[-5] to refer to cell Axxx with 100% relative addressing.

The distinction between relative and absolute column references is only important if you need to copy a formula across.

Assuming that the absolute references to the columns aren't a problem, then you could use the following snippet:
Sub Formulator()
Dim frmla1 As String, frmla2 As String, sTickets As String, sOtherSheet As String, sThisSheet As String
frmla1 = "=IF(Tickets!RC1*Tickets!RC2<OtherSheet!RC4,1,0)"
frmla2 = "=IF(Tickets!RC1*OtherSheet!R1C4<thisSheet!R1C5,1,0)"
Range("A10").FormulaR1C1 = frmla1
Range("B10").FormulaR1C1 = frmla2

sTickets = "'" & Worksheets("Tickets").Name & "'!"
sOtherSheet = "'" & Worksheets("OtherSheet").Name & "'!"
sThisSheet = "'" & Worksheets("thisSheet").Name & "'!"
frmla1 = "=IF(" & sTickets & "RC1*" & sTickets & "RC2<" & sOtherSheet & "RC4,1,0)"
frmla2 = "=IF(" & sTickets & "RC1*" & sOtherSheet & "R1C4<" & sThisSheet & "R1C5,1,0)"
Range("A10").FormulaR1C1 = frmla1
Range("B10").FormulaR1C1 = frmla2
End Sub

Open in new window

0
 
LVL 81

Expert Comment

by:byundt
ID: 40000408
BostonBob,
The reason that you and mlmcc weren't on the same page is because you posted the question in the VB Script TA instead of the Excel TA. Though derived from the same parent as VBA, VB Script is a different language. It is commonly used in scripting tasks, much like the old .bat files in DOS 4.0.

Even though I couldn't write the simplest bit of code in VB Script, I often tackle Excel questions that are miscategorized in the VB Script TA.

I've added the Excel TA to the question, acting in my capacity as Topic Advisor.

Brad
0
 

Author Comment

by:BostonBob
ID: 40000689
As usual Mr.  Byundt, your answers are "over-the-top," eye-poppingly, awesome!  Not only did you give me the answer, you gave me the answer two different ways while at the same time opening up a whole new branch of programming for vba!

I have printed these answers out and I am just staring at it thinking to myself that I have not even "scratched the surface" of what is possible in VBA when one of you (Experts on Expert's Exchange) divine something like this for me.

You're teaching me so much; I am so grateful!

Thanks doesn't say it all.  It couldn't even begin to.

Can I award you 50000 points?  

As for the VB Script comment....I had no idea!!! All this time I thought that "VB Script" was the "right choice" and here I find that isn't the case at all.  Excel TA all the way from here on in!
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

726 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