evaluate

  1. V

    How to use VBA to autopopulate index match formula in a range of cells

    How do I use VBA to insert this index 3 match formula into a range of a column every time we refresh the source data? I think I need to use the Evaluate function but I haven't found an example of it producing a formula instead of a value. Formula: =IFERROR(INDEX(Source!$D$1:$D$81, MATCH(1...
  2. C

    Poker hand analyzer out of a 7 card hand

    3 questions Okay so I'm trying to make a sheet that represents a 100 full table of players and their hands and then summarizes the results for analysis I've got an almost complete. I used the forums to find formulas that work. This in the spreadsheet that I attached. the way I got the cards to...
  3. M

    Debugging Evaluation function

    Hello,, I have a syntax error in [YELLOW LINE] Sub Rectángulo4_Haga_clic_en() Dim Rango As Range Dim Celda As Range Dim Lista As Range Set Rango = Worksheets("Print").Range("B2:B24") Set Lista = Evaluate(Rango.Validation.Formula1) <--- THIS IS YELLOW LINE For Each Celda In Lista...
  4. wsnyder

    Evaluate Variable At Run-Time

    Hi, Using Excel 365. How do I evaluate a variable at run-time? The code below is returning sp1, sp2, sp3 I am trying to evaluate the variable as Alpha, Bravo, Charlie, Thanks -w Sub foo2() Const sp1 As String = "Alpha" Const sp2 As String = "Bravo" Const sp3 As String =...
  5. S

    How to get Multiple items and sum up the same using GetPivotData?

    Sub Reconcile() Dim InputworkBook As Workbook Dim ReportsToReconcile As Variant ReportsToReconcile = Array("Report1", "Report2") Dim ReconcilingReport As Variant Dim ReportIndex As Integer ReportIndex = 0 Set InputworkBook = Application.Workbooks.Open(Application.GetOpenFilename, ReadOnly:=True)...
  6. S

    How to GetPivotData passively?

    This works... .GetPivotData(PivotEntity) But Not this... Evaluate(".GetPivotData(" & PivotEntity & ")") How to make this work? If not... Is there any other alternative command available. Please Help. Thank You
  7. DRSteele

    Function SEQUENCE is producing errors.

    The new Excel function SEQUENCE seems to be producing errors. I want a sequence of values from -50% to 50% with a 5 point step. Put this in a cell and evaluate it in Edit mode by pressing f9. =SEQUENCE(21,,-0.5,0.05) Not only is the 0 a wonky exponent, some of the other values near zero are...
  8. M

    Passing an integer variable into VBA evaluate SumProduct

    I'm trying to pass a variable into my evaluate statement and I can't get the syntax correct. Let me know if you can help This works: Labor = Evaluate("=sumproduct(--(g10:g50000="Forecast"), --(j10:J50000="CWIP"), --(y10:y50000=2020), --(z10:z50000=4),(aq10:aq50000))") but I want to change the...
  9. C

    Trim, Clean using Evaluate running into 256 character limit

    Hello I have large datasets that I run automated processes on that I need to trim and clean and have been using: Function CleanSheets(arrShtNames As Variant, startRow As Long) Dim ws As Worksheet Dim rng As Range Dim LR As Long, Lc As Long For Each ws In Worksheets(arrShtNames) With ws...
  10. T

    Evaluate math formula from Equation editor into cell

    Hello MrExcel community, this is my first post, so if there is anything about this post that isn't posted correctly feel free to tell. OK so going into the main thing, In my Excel sheets I use plenty of long and complex mathematical equations, that when trying to replicate form paper into...
  11. T

    Evaluate formula

    Assume I have this data on my worksheet from cell A1 through to B6: <colgroup><col width="64" style="width:48pt" span="2"> </colgroup><tbody> 1 a 2 b 3 c </tbody> If In cell D1 I type: =MATCH(1,(A1=A1:A3)*(B1=B1:B3),0) and enter it "normally", I...
  12. J

    speeding up loop

    Hi everyone I have a loop that is already working. I was just thinking if there is a way to select all the cells that is zero and deleting it at one go instead of sorting it using a loop 1 by 1 by vba. this have to be done in vba as this is just part of the code in a very long line of codes...
  13. pbornemeier

    Why is Columns:=aryDeDupe not the same as Columns:=(aryDeDupe)

    Working to answer this thread: https://www.mrexcel.com/forum/excel-questions/1106189-all-combinations-multiple-columns-without-duplicates.html I needed a way to remove duplicates from a variable number of columns VBA recorded for remove duplicates in H:J of columns A:J is...
  14. JackDanIce

    Evaluate SUMIF error?

    Hi, I have indexed data in K1:K7 and values in L1:L7 (incl. header row) I'd like to extract unique values from K1:K7 into column G and in column H list SUMIF values. I have the following but it returns the value for the first unique value in K2 on all rows in column H. Can someone suggest, I...
  15. P

    If function to evaluate another formula

    I have the following in cell M2: =IF(($J$29-$J$28)<=7,$X$4+$X$2,$X$2) I would like to create the following which references cell M2: =IF(M2<>"1610.59","602.8","") The problem I believe I am having is the 2nd If statement is evaluating a formula, rather than the actual cell value. How do I...
  16. S

    Evaluate any UDF arguments as is, as a cell in excel would do

    How do I evalute myudf as excel evaulates its own in-built functions? many experts has said it is imposible , VBA doesnt have native ability to this, and to basically "Go and learn Python or C" instead. And there are many ways to do this. But I think VBA can do this, becuause some UDF's...
  17. L

    The Evaluate function drops a leading zero

    Hi is there a way to use the VBA Evaluate function and have it retain a leading zero, if the zero is there? I don't want to add leading zeros I just want it to be kept if it already exists. Thanks L
  18. L

    Conditional Formatting Rule Not Working Properly

    I'm trying to conditionally format a column based on three criteria. I'm using the AND function to evaluate three different cells in the same row and if all three are "true" then I want to conditionally format the first of those 3 cells. When I run the AND function down the side in a helper...
  19. B

    Rid of value error

    i am trying to extract alphanumeric data like EXAMPLE: 785abc25d so i tried so please help me i am using excel 2010 version =INDEX(ROW(INDIRECT("1:"&LEN(A36))),N(IF(1,MID(A36,ROW(INDIRECT("1:"&LEN(A36))),1)+0))) after i pressed f9 key to evaluate so it is showing...
  20. T

    Evaluate with SUMPRODUCT and variable

    Hi Everyone! I'm attempting to calculate the below formula in VBA. However, it returns a type mistmatch error. Any help would be greatly appreciated =) dblMyVal = Evaluate("SUMPRODUCT(($H$2:$H$1000)*($A$2:$A$1000=A" & rw & ")*($B$2:$B$1000=B" & rw & ")*($D$2:$D$1000=D" & rw &...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top