Posts by philb1

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Re: Why is Option Explicit optional?


    I'm a self taught VBA coder and when I first started it was hard enough fixing errors when Option Explicit wasn't in use. There was the occasional typo of my own doing, but it was mainly trying to figure out what all the different Dim declarations actually meant. ie, Long for numbers, Double for really big numbers etc etc. I often used to get them wrong or mix them up & many a time I thought of saying "to h*** with it" & use Variant for everything. Eventually it all sank in & now I use Option Explicit by default, it makes life so much easier, but not understanding the basics like ranges, objects etc made debugging a nightmare for me

    Re: Errors Keep Popping Up


    Hi ekfasy


    I've got your code working & I presume it's putting the copied data where you want it. I haven't changed anything like that.
    It's easier if you select the file you want from the dropdown before you start the macro, you won't have to stop the routine to do the check. I put a test in to compare the file name you selected to open with the name in the cell J1. If the J1 text is in the filename selected, it will run the routine, if not it will terminate.


    The causes of the errors were


    1) Matching of hyphens "-" and underscores "_". You had hyphens in the dropdown and underscores in the file names. The file you selected from the file open window won't match Cell J1 causing the Select Case part of the macro to never run & routine would exit every time.


    2) When copying the data from the HIJ_123 etc workbook, you didn't fully qualify where the code was supposed to be copied from. You used ".Range(.Cells(11, 1), .Cells(z, 1)).Copy" when it needed to be "Wb2.Worksheets("Quote").Range(.Cells(11, 1), .Cells(z, 1)).Copy"


    I changed a couple of things to make the code more readable, any questions, gimme a shout. The code is below & the file is attached for you to check out
    If you have the editor open place a break just after myFile = application.blah blah by putting the mouse cursor on the left hand frame of the window, right next to the next line down & left click, select the file you want to open in J1, press F5 to start the macro, select the file to open & the macro will stop at the break. You can then step through the macro by pressing F8 to check out the code. Apologies if you already knew how to do all that :)


    Cheers


    Re: Copying all non-empty cells from ws1 to ws2 and copy corresponding column title


    Hi dmi123
    I think I understood you. You wanted the column header for the first one found Q39 in B2 and 2 in C2 etc


    If so the code below will do that on the example file you provided


    Cheers


    Re: Copying X amount of names from list and enter in another column


    Hi
    Paste the code below into the module named Test in the VB editor & have a play. I think it's what you're after
    Cheers

    Re: Not understanding Error for ExT.Activate


    The code you're using forces the macro to override the error, I thought I'd work around using error control, but it's the same difference in the end
    The code below is the easy way to delete the contents of columns A & B on the Daily Bidding sheet


    Re: Not understanding Error for ExT.Activate


    Try this

    Re: macro with formula to be applied to specific columns


    Hi
    Paste the code below into a module & see if it's what you're after. If I can I'll attach a workbook, if not it'll be a dropbox link
    It put the formulas in columns M & R then deletes the contents of L & U
    If it's the wrong way round you can change the code to suit or I'll do it in the morning if you're stuck


    Cheers
    Phil

    Re: Macro to select all tabs that contain "PO" in the tab name


    Here's another version
    It looks for PO in upper case and there is a blank space before PO


    Re: Moving between columns (dynamic range?)


    Try the code below
    You had a zillion if's when you really needed just one. You also had quotation marks surrounding the values you were entering if the condition was met, doing that changes the number value to text.