Posts by jvalente

    Hi All,


    I have some code that vlookups between two different workbooks. It works perfectly when there is no error handling (skips when finds an error).


    However, now I need to return a value from another column (Column A) when the error occurs. The code I wrote will get to the first error. Return the value I want, but then the values afterwards are wrong (ie not vlookuping correctly).


    My code is as follows:


    Re: VBA Lookup using Scripting Dictionary


    Hi all,


    I found some code Jindon posted online and altered it and it worked.


    Thanks sktneer, your code worked as well!


    The code is as follows for those new to this, like me. I put commentary in it to make it less esoteric, for the uninitiated:


    Re: VBA Lookup using Scripting Dictionary



    I tried this but it all game back as NAs, when it shouldn't be

    Hi All,


    I'm really trying to get my head around using a VBA scripting dictionary to do quicker vlookups.


    I'm using a simple test sheet with a small range to get my head around how this works.


    I am able to load values from a range "B1:B32" into the dictionary, and then retrieve them.


    I'm just stuck at how to compare this against another range, if the values match, then return the value. (ie perform like a regular vlookup)


    My code so far:


    Re: How to pass workbook name between subroutines


    Quote from cytop;772001

    There are 99 ways to do anything in VBA.


    Not that it matters for such a simple piece of code, but you already have a reference to the workbook in wb2. You can pass this directly to the procedure; so what's the point of storing the name in a string, passing the string and then using that string to 'find' the workbook in the called procedure? 2 extra steps, must waste all of 6 clock cycles :)


    Seriously though, it's more the principal of the thing - defining the string, storing the name of the workbook in the string, creating another variable to refer to the workbook in the called procedure and setting the reference to the workbook using the string are all unnecessary. Internally, both procedures will refer to the same object so why not simply pass the object to begin with?


    Your technique is much more elegant.


    The problem is that I'm learning, so I google my problem and I find something that suits my needs and I adjust the code and test.


    I googled, how to pass a workbook variable to another sub routine and found someone who used the string solution.


    if anyone knows a good way of knowing what every command and syntax does, let me know!

    Re: How to pass workbook name between subroutines


    Thanks Cytop!


    many different ways to skin a cat I suppose. I ended up defining string variables to capture the workbook name and then I passed the string variables in my call procedure.


    then in my called procedure, I set the workbook name to the workbook string variable.


    interesting that there's always other ways to get to the same solution

    Hi all,


    I have some code that opens a file, if the file name contains X or Y, it does an X defined macro and a Y defined macro.


    The issue I'm having, is how to pass the workbook name that I've defined between subroutines.


    For instance my code is as follows:



    Instead of the MSGboxs, I want the macro to take data from the file I open and copy and do further manipulation.


    The problem is, I don't want to re-open the file again for each new case to get the workbook name (ie wb2 variable). I want to pass that variable name between subroutines. Confused with the syntax so far....

    Re: Vlookup from row X to lastrow (not row 2)


    Must have been tired,


    I needed to use a loop


    Code
    For k = vRow To lastrow2
    
    
    sh.Range("V" & k).Formula = "=IFERROR(VLOOKUP(RC[-17],'Upload template - Settled'!C[-18],1,0), ""Not Converted"")"
    
    
    Next k

    Re: Need a macro for Paste


    I think you need to specify a place to paste.


    For instance,


    sheets("sheet1").range("A1").paste xlpastevalues


    After copying, will paste in sheet 1 under cell reference A1, pasting as values

    Re: Vlookup from row X to lastrow (not row 2)


    I previously had


    Code
    Set rng = Range(ActiveCell, ActiveCell.End(xlDown)) ' from the activecell to the end of the data we set our range
    
    
    
    
    rng.Formula = "=IFERROR(VLOOKUP(RC[-17],'Upload template - Settled'!C[-18],1,0), ""Not Converted"")"


    But I need the last row from another column of data, not the active cell column. Otherwise the formula won't paste down all the way as I desire.


    Hope that makes sense!

    Hi All,


    I'm trying to paste down a vlookup, but not across the whole data range. I have some code that has found one year prior to my data range, and then I want to apply the vlookup from there to the lastrow.


    My code so far:


    The last line is where my error happens


    I know I'm close.


    Thanks all

    Hi All,


    I'm writing code to store values from one sheet. Then loop against a larger sheet to check if those values correspond to the last sheet. If the value from sheet 1 is not found, then set the value of that to "MM".


    For some reason it's setting everything to MM"


    I'm not sure what I'm doing wrong.



    Would love some help!


    Cheers

    Hi All,


    I have an OLAP pivot table that I'm having trouble with. Basically I want to filter the pivot by a particular date by a defined range or variable.


    I recorded a macro and changed the code to simplify it. So far this works.


    Code
    pf.VisibleItemsList = Array("[Transaction Date].[By Transaction Date].[Transaction Month].&[2016]&[3]")


    This filters the pivot by 2016 and month 3 (March).


    My attempt to put my variables have failed as per below:


    Code
    pf.VisibleItemsList = Array("[Transaction Date].[By Transaction Date].[Transaction Month].&[theyear]&[themonth]")


    I get the error "The item could not be found in the OLAP cube"


    My entire code as follows:




    I know I'm not far with the syntex. Can anyone help please?

    Hi All, I'm trying to filter a pivot by date. Having some issues. I can filter if I specify the date, but when I use a date range I have problems.


    As you can see below, I have dates defined in a variable as below.