1. D

    why giving me N/A?

    =IF(OR(G8=0,AB8>0),0,AB8) g8 is N/a....AB8 is 0.....shouldnt the if statement produce 0? it's giving me N/A
  2. jevi

    If a cell contains certain word.

    Hi All, I'm trying to do this formul but is not working. =IF(ISNUMBER(SEARCH("*LR 10*";A2));"M01";B2) so in column A2 I have the long desription of the loan and I want to find the ones that contain "LR 10", and if this is true to put in cell C2 "M01" otherwise the value that is in cell B2...
  3. E

    VBA Formula with Quotations

    Hi, I'm sure this is a silly one but I can't figure it out. I'm wanting to add the following formula using my macro: =IF(OR(C8>=6,D8>=6),"Strength","") When I convert that into my script it looks like this: Cells(r, 6).Formula = ("=IF(OR(" & Cells(r, 3).Address & ">=6," & Cells(r, 4).Address...
  4. B

    object variable or with block variable not set

    Good morning, I am a little bit lost here, the code was working fine, I don´t remember changing anything, and suddenly now is giving error. Already spend a few hours around this matter and I still don´t understand why is giving this error. Run-time error '91': Object variable or With...
  5. A

    Query Refresh - Why does it duplicate

    Hi, I have - Query1 = Excel.CurrentWorkbook() Below pic shows query properties. How do I refresh this so that only new rows are added. If I manually refresh, the 15,970 rows duplicates giving me 32,000 + rows. I only want the 15,970 rows plus the new rows...
  6. G

    Remove Formatting Runtime Error 1004

    Not the best or brightest using VBA but I've done this a 100 time before but now it's giving me grief. Keep getting runtime 1004 error. This is driving me crazy so any help would be appreciated. Thanks, Dim wc As Worksheet Set wc = Sheets("ABC") wc.UsedRange.Value = wc.UsedRange.Value
  7. A

    Vlookup another workbook and sheet using dynamic references

    Hi, I am trying to vlookup another workbook and sheet using cells as a reference. My equation is below and I can't seem to get it to work. It keeps giving a #REF ! error. =VLOOKUP($A2,INDIRECT("'["&K1&"]"&J1&"'!"&"$A:$BE"),8,FALSE) Please assist. Many thanks.
  8. T

    Error on formula (compound percentage)

    Hi, new on the forum, would appreciate some help, I've spent 7 hours trying to figure this out :banghead: I'm trying to insert a formula in excel for Mac 2011& 2016 The formula is: =IF(I2="",IF(F3>0,F3,""),IF(F3>0,(F3+1)*(I2+1)-1,"")) Keeps giving me an error. I'm tryint to reconstruct the...
  9. E

    incorporate date in IF/VLookup

    Hi there, I have a formula below that is working great so far. However, I wanted to add a date reference to it. For example if D1="03/29/19"....then apply the current formula below, if not just "" =IF(ISNA(VLOOKUP($B4,MARCH,10,FALSE))=FALSE,"NO","YES") It is not doing what I wanted to...
  10. J

    work around the 255 character limit in the left() formula

    if I have 400 characters in a cell for example a bunch of random letters (ANDGHJSK....) I want the first 300 so =left() is the right formula but it maxes out on 255 before giving an error on 256. any non macro workarounds?
  11. M

    sum of numbers with a specific letter

    I am attempting to get a combined total for a range of cells that contain a number and letter. Example 8v+3v with it giving the answer of 8v. Any ideas?
  12. M

    If Formula is not giving me results i am looking for.

    I want to assign a week number to correlate with a range of dates. If the date in cell A5 is between 01/01/2019 and 01/05/2019 I want it to say "1" If the date in cell A5 is between 01/06/2019 and 01/12/2019 I want it to say "2" If the date in cell A5 is between 01/13/2019 and 01/19/2019 I...
  13. H

    Sum Function

    Hi All, I am using the below array function to count the number of weeks which are meeting the criteria of >=100, from last 4 weeks which have scores and ignore the weeks containing 'NA'. However it is not giving me the desired answer. The formula is working fine if the criteria is <100 but...
  14. S

    Multi variable optimization through solver

    Hello everyone I am new to this group as well as working on excel marcos. I have made spread sheet where there are two equations (Q1=m1*c*dT and Q2=m2*c*dT) that needs to be balanced (Q1 =Q2) to get such a value of m1 (that utlimately L coming from m1 is same for both m1 and m2). I have made...
  15. A

    find dates vba problem

    I have the code that is suppose to find all dates older then 3yrs. However I'm confuse as to why is not giving me the intended results. The first code works as expected.... MsgBox WorksheetFunction.CountIf(Range("D15:D100"), "<11/30/2015") However, when I replace the "Hard" date with a...
  16. D

    VBA - Paste Special

    I have a macro, and at the end of it, I'd like to copy my selection and paste the data right where it is, but paste values only. I have this code: Range("J4:Y4").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy 'Range("J4").PasteSpecial Paste:=x1PasteValues...
  17. xeven_

    Percentage change

    I have this formula in column N and it is looking at price in column E. =(E2/$E$2-2) My objective is to keep a running tally of the stocks accumulated percent change from the first cell entry.When I enter this in excel 2016 and the first cell entry is 0.78 and the last is 13.55 it is giving me...
  18. K

    Nested if and formula

    Hi all, I am trying to use the below formula and it is giving me an formula error, can someone please help me correct it? =IF(AND($B$1<=6,$B$2<=5),"Good",IF(AND($B$1>6,$B$1<9,$B$2>5,$B$2<=8),"Average",IF(AND($B$1>=9,$B$2>8,$B$2<6),"Low",IF(and(B1>=9,b2<6,"Low",""))))) Thanks, Karthik
  19. J

    Help converting IF NOT OR AND to VBA IF statement

    Hello everyone, I am trying to convert the below working IF to VBA: =NOT(OR(AND(LEFT($C6,1)="T",LEN($C6)=9,ISNUMBER(-RIGHT($C6,8))),AND(ISNUMBER(-$C6),LEN($C6)=9))) What I have tried is: [Code] If Not ((Left(Cells(i + 1, 3), 1).Value = "T" AndLen(Cells(i + 1, 3)) = 9 And...
  20. mrsphxbabe

    Please help with this Formula

    Hi everyone. I am going out of my mind here, I got someone to help me with this formula, but for some reason when I paste it into my spreadsheet, its not doing what I expected. Some times it wont paste all of the formula, or it goes into a different cell them where I can trying to paste it, or...

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...