Posts by WillAnt

    I am attempting to retrieve the value found in the last cell/row in column E of a dynamic range. The first row containing data will always be E12 and the last row fluctuates on a daily basis. The formula I am using invariably returns the value found in E12 only - so I am obviously doing something drastically wrong...


    I am using a Named Range to house the formula.


    Also, the data in column E is obtained via a VLOOKUP, which is why I'm using "value" in the formula.


    I am in a corporate setting - Macros / VBA Code not allowed...


    Any insight would be greatly appreciated!


    Thanks in advance...


    Code
    1. 'Named Range = Last_Roe_ColE
    2. =value($E$12:INDEX($E:$E,COUNTA($E:$E)))

    I am using a HLOOKUP (sample code below...) which is "keyed" to a value in a merged cell Y1 (Y1:Y2).


    The lookup is located in Y3 and covers the range D:X.


    The lookup WORKS FINE as it is - HOWEVER...


    It currently only searches on the word "Error" and Error alone (Y1 = Error).


    My dataset has changed, and I now need to be able to "detect" Error within a string (Filing Error, Document Error, Error entering Case Number, etc.).


    So, I am trying to incorporate "contains" within the lookup (isnumber search?, something else?).


    I have had no luck so far - any help would be GREATLY appreciated...


    Thanks in advance!


    Code
    1. 'THE ISNA PORTION PREVENTS "#N/A" FROM BEING RETURNED IF CONDITION IS NOT MET...
    2. Range("Y3").Formula = "=IF(ISNA(HLOOKUP(R1C25,RC[-21]:RC[-1],1,FALSE)),"""",HLOOKUP(R1C25,RC[-21]:RC[-1],1,FALSE))"
    3. 'CONDITIONAL AutoFill.
    4. If Last_Row_ColA > 3 Then
    5. Range("Y3").AutoFill Destination:=Range("Y3:Y" & Last_Row_ColA)
    6. End If

    A worksheet formula is fine as a solution.


    How do I get a formula WITHIN the framework of the VLOOKUP?


    [FONT="Verdana"]For example, I have my lookup value in cell A2 of worksheet "A".


    Iam placing a formula in cell B2 of worksheet "A".


    I am EVALUATING the content found in worksheet "B" - which is named "FilteredData" in this particular example.


    The results of this evaluation are then "brought back" to cell B2 - via the results of the VLOOKUP - and displayed either as an error based on the COUNTIF, or as a simple value if the condition "passes."[/FONT]


    Code
    1. =IF(VLOOKUP(A2,'Filtered Data'!AN:BB,15,FALSE)="","",VLOOKUP(A2,'Filtered Data'!AN:BC,16,FALSE)))
    2. 'I'd like to insert the countif condition right where the double quotes indicating blanks are in the line above - if the condition is not met, continue on with the lookup.
    3. 'I do not know how to do this.

    [FONT="Verdana"]The VBA-based code below is something I use with great frequency.


    At the end of the initial VLOOKUP I typically return a blank if the lookup cell is blank , if not then return whatever is in the cell.


    However, that spot (If...VLOOKUP="""","""",VLOOKUP) is where I wish to incorporate the COUNTIF's referenced above.


    I'm just not sure how to go about that...


    Code
    1. [/FONT][/COLOR]'Insert a VLOOKUP in Cell B3.'This VLOOKUP is for AN(40): DK(115) / 76 (AN = FULL_ACCT_NO - DK = PREVIOUS_USER) - Filtered Data WORKSHEET. Range("B3").Formula = "=IF(VLOOKUP(RC[-1],'Filtered Data'!C[38]:C[113],76,FALSE)="""","""",VLOOKUP(RC[-1],'Filtered Data'!C[38]:C[113],76,FALSE))"'CONDITIONAL AutoFill. If Last_Row_ColA > 3 Then 'Selection.AutoFill Destination:=Range("B3:B" & Last_Row_ColA) Range("B3").AutoFill Destination:=Range("B3:B" & Last_Row_ColA) End If

    This IS actually - hopefully - supposed to "become" VBA" in a much larger framework - I posed the question as a worksheet formula because I have LONG WAY to go to get this stuff straight in VB...


    Confidentiality clauses strictly restrict any kind of sample being included.


    What I am trying to do - using my question above as a baseline, is this - incorrect syntax notwithstanding.


    I'd much rather have a VBA solution, but I'm trying to work through this a step at a time.


    Code
    1. =VLOOKUP(A2,'Filtered Data'!AN:BA,14,FALSE)
    2. IF THE CONDITION BELOW IS NOT MET: =if(COUNTIF(A2,"=*L*")+COUNTIF(A2,"=*O*")>0,"ERROR","") RETURN: =VLOOKUP(A2,'Filtered Data'!AN:BA,14,FALSE)

    Well, other than the semantics...


    I am - attempting - to retrieve these results FROM the "Filtered Data" worksheet.


    So, the above seems to be pulling from the ACTIVE worksheet, then looking to "Filtered Data."


    My query is: Redirect to Filtered Data - if condition met, return ERROR, if condition NOT met, return existing value.


    Thanks for your rapid input!

    Hi there,


    I need to be able to incorporate / integrate a formula into a VLOOKUP that brings data from one worksheet over into another.


    If there is an "L" (late) or "O" (overdue) within the cell I need to bring over "ERROR" - which is essentially a delinquent payment. If neither are present - simply display whatever value that is actually present.


    I've included the relevant code snippets below


    Thanks in advance!


    Code
    1. 'The lookup:
    2. [FONT="Calibri"]=VLOOKUP(A2,'Filtered Data'!AN:BA,14,FALSE)
    3. How I'm evaluating the cell:[/FONT]
    4. [COLOR=black][FONT="Calibri"]=if(COUNTIF(A2,"=*L*")+COUNTIF(A2,"=*O*")>0,"ERROR","")

    [/FONT][/COLOR]

    I have to admit - I am at a total loss here.


    Taking the looping out of the equation - here's what I keep running up against:


    I have placed the value 1,000 in cell A1.


    This value is formatted as a number


    The code below FAILS when testing, by returning "No Comma".


    Any suggestions (by the way, you've been a great help thus far...)?


    Code
    1. If InStr("A1", ",") Then
    2. Range("B1").Value = "Comma Found"
    3. Else
    4. Range("B1").Value = "No Comma"
    5. End If

    Hi there,


    I'm looking for some guidance on how to use / code the VBA InStr function (I've never used this before...).


    I need to place the formula in cell H3 to detect a value ("dg") in cell G3.


    I also think that, at some time in the future, I may end up using this over a whole set of non-contiguous "pairings" (G3/H3, L3/M3, AA3/AB3, etc.) - and wonder if I would need to set up some sort of Loop schema for that?


    Thanks in advance!

    Hi there!


    I need to be able to have some latitude when SAVING a file with a specific DATE.


    I am using the SYSTEM DATE - NOT a date that will ever be keyed anywhere in a workbook / worksheet.


    In the code posted below I state that if today is SUNDAY (Weekday1), then save the file with a date 7 days in the past.


    If today is ANY OTHER day but SUNDAY, save the file with a date 1 day in the past (or, yesterday).


    The code - as I have written it - seems to entirely skip over the 1st clause, and repeatedly saves the file with yesterday's date...


    HELP - thanks in advance!


    Hi,


    I need to break a LONG line of code across multiple lines - specifically within the folder PATH shown in the code attached.


    The first break (following the Filename:=) works just fine.


    My problem is creating the subsequent breaks. I have tried adding immediately before OR after the backslash (""), but have not been successful.


    I realize that the folder structure is rather lengthy but, that's set in stone a little higher up the food-chain than me...


    Thanks in advance!


    Code
    1. ActiveWorkbook.SaveAs Filename:= _
    2. "M:[B]\Folder_Level1\Folder_Level2\Folder_Level3\Folder_Level4\Folder_Level5\Folder_Level6\Folder_Level7\[/B]File Name mm.dd.yy.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

    Thanks - I guess I was overthinking it (as a beginner...).


    I was expecting that it needed something like "ActiveCell.Formula=..." or "ActiveCell.FormulaR1C1.Formula=..., etc.


    Didn't realize that you could simply toss in a formula in that manner!

    Hi,


    I'd like to integrate a formula within a loop based on the result of the first condition.


    In other words, if the condition is not met, a formula is inserted and the loop continues onward.


    The code below should serve to illustrate...


    My biggest issue has been the format or syntax on how to get the formula in there following the "Else" clause.