Posts by jvalente

    Hi Guys,


    I want to add the following formula into VBA:


    =""&TEXT(Results!M1,"mmmm")&" CI Allocated %"


    trying to incorporate that into a VBA formula like:


    Worksheets("Results").Range("A19").Formula =""&TEXT(Results!M1,"mmmm")&" CI Allocated %"


    But that won't work....


    Can anyone help?


    Thanks guys

    Re: VBA Matching


    Just the header being highlighted. Works perfectly now!


    Thank you soooo much!


    Wish I could be at your skill level one day!

    Re: VBA Matching


    Jindon, that works 99% perfectly. I even tested it on duplicates not common on both columns and it worked!


    Thanks so much.


    Anyway I can get it to skip the first line or header? As this is just the header and not intended to be matched.

    Re: VBA Matching


    SNB,


    Your example works well. However, for aesthetic reasons I'd like it if both sides were highlighted. My only concern is that I'll be using Macros to format the data, and it would be nice to then have a macro to match.


    With conditional formatting, I'll have to set up the worksheet each time to do this function?


    I'm not 100% across what you did.

    Re: VBA Matching



    Hi KjBox,


    Your code is really close to what I want. It highlights both columns. However, it didn't highlight some data that is common on both columns for some reason. It did for some but skipped others...


    Please refer to attached.

    Re: VBA Matching


    Quote from snb;706647

    I think so.
    Let's await her/his reaction. ;)


    Thanks guys, I didn't think to use conditional formatting. However, I need it to not highlight duplicates. Only match like for like.


    So if there's two extra transactions of the same value, only highlight the ones that are common in both columns and leave the extra two not highlighted.


    I will test out the code when I get to work

    Hi Guys,


    I'm wondering if someone can help me with a matching VBA script?


    Basically the idea entails, comparing two different columns of data and match that they are the same value (to the decimal point).


    I also need it to compare the same number of transactions and not highlight any duplicates. (See point 2)


    I have a good example I found on the internet, however, I am struggling to get it to:


    1) highlight both columns (it only highlights one column)


    2) Highlight only values that match and are present in the spreadsheet. In the example I've attached I have 3 values of $3,873.61, however it highlights all 3. But I would like it to only highlight 1 (as both spreadsheets only have $3,873.61 matching once column to column)


    Thanks and really appreciate this guys!


    Regards,

    Re: VBA Vlookup help



    Smallman. You have just saved me another sleepless night. Thank you so much. On another note, do you always have to double up on quotation marks in VBA?


    Thank you once again.

    Hi All,


    I am having trouble converting a VLOOKup formula into VBA Code.


    The excel code that I have working is as follows that works in excel:


    =VLOOKUP(LEFT(B2,5)&"*",'Co-has CCN'!$B$2:$C$2965,2,FALSE)


    I want this to be coded in VBA so that we can have the formula paste down to the last row.


    I have tried to do something like this in VBA:


    Sub test()


    Worksheets("Co-missing CCN").Select



    With Worksheets("Co-missing CCN")
    .Range("C2:C" & .Cells(.Rows.Count, "B").End(xlUp).Row).Formula = "=VLOOKUP(LEFT(B2,5)&" * ",'Co-has CCN'!$B$2:$C$2965,2,FALSE)"
    End With

    End Sub


    However, I get a runtime error 13 on the line above. Can anyone please help?


    Thanks

    Re: Delete Data based on Date Range


    Quote from jindon;691674

    See if this is what you wanted.

    Code
    Sub test()
        Columns("f").Insert
        Range("e2", Range("e" & Rows.Count).End(xlUp)) _
        .Offset(, 1).Formula = "=day(e2)=day(today())"
        With Range("f1", Range("f" & Rows.Count).End(xlUp))
            .AutoFilter 1, False
            .Offset(1).EntireRow.Delete
        End With
        Columns("f").Delete
    End Sub


    Thanks Jindon! Works a treat!


    Thanks for your help too Glovenor!

    Re: Delete Data based on Date Range


    Quote from TheGlovner;691664

    Worked fine on mine, my guess would be that you have some data very low down in the sheet. Since the code looks for the last used row and runs until the working row reaches that point.


    Worked fine on mine but I just used a quick mock up of your jpg so I only had about 20 lines and definately nothing in the sheet after row 21.


    (Which is why it's a good idea to upload the sample workbook as I can't see what problems you are running into with my mock up)



    Please refer to the zip file with the excel spreadsheet inside it. This refers to the 15/11 iteration of our work documents. When I ran the macro, it just ran it in an endless loop.


    THanks again

    Re: Delete Data based on Date Range


    Quote from holycow;691621

    It's not hard to desensitise a company workbook. Also the picture supplied does not even show critical information such as Column header letters.


    True,


    I'll upload a version later tonight.


    Tiago

    Re: Delete Data based on Date Range


    Quote from royUK;691542

    Why attach an image instead of a workbook??


    It's a work document.


    TheGlovner, I ran the macro and changed the cell column E to Deal_Date as specified.


    I ran the macro, however, it seems to loop endlessly and after about 10 mins I have to task manager kill excel.

    Re: Delete Data based on Date Range


    Quote from TheGlovner;691524

    Luckily thats what I went for anyway with the above code.


    You just need to give the column with deal date (Column E in your .jpg) a Named Range of "Deal_Date" and the above code should do what you are after.


    I'm still dubious about deleting raw data rather than pulling out the desired data into another sheet, but if that's the way you want to go that code should suffice.


    Do you mean rename Column E "Deal_Date"?

    Hi All,


    I'm a novice in writing code in VBA. A lot of my experience so far is using macro recorder and googling my problems and finding a similiar problem and resolution.


    I need help in dealing with data with certain dates.


    Can someone please help me devise a macro code to delete data, that doesn't fall on a specific day? So for example, let's say we are dealing with the 12th day. I'm happy with the start date being the 12/01/13, 12/04/15, 12/06/17 etc etc, because I want to isolate the 12th day of any month or year. So delete anything else that doesn't reference the 12th.


    But I don't want data that has dates with 04/01/13, 08/10/14, 11/09/16 because they don't fall on the 12th day.


    The data stretches from cells A to R, and I'd like a macro to isolate days that only fall on the 12th for our example above.


    The defining factor for picking the date is today's date. Using a code like "=TODAY()" will give you that date. But I'm not isolating months of years, just the day.


    Hope that makes sense.


    Thanks!