Posts by TheGlovner

    Re: Resolved

    Quote from spencearini;686059

    The Glovener, I got it, thanks, I had not defined those two column names as you suggested, thanks again!!

    No problem, glad you got it working. Sorry for the delay in replying, was answering from my mobile when I had time and didn't see your replies until I got back in the office this morning.

    Re: Excel Move B to A on text find

    Just noticed from your jpg you've actually placed the code into a userform module.

    It should be placed directly into the VBA for the sheet the data is on or a standard module.

    Userform assumes you are building some sort of user GUI to trigger events/enter data. Which you aren't. So remove that one and do the above (either sheet or standard module) then try again.

    Re: Excel Move B to A on text find

    Quote from spencearini;686020

    I maybe doing something wrong, I got a range of object failed:

    As my previous post mentions I'm using named ranges instead of cell references. So you can either take my code and name your column B as "OtherDataCol" and column A as "WarehouseCol". Or you amend my code to reference columns A and B respectively.

    I believe coding standards say you should avoid direct cell references in order to take account of any movement in the sheet, but if your just doing a one off exercise it probably won't matter about future proofing it.

    Re: Excel Move B to A on text find

    Okay I misunderstood.

    I'm heading home now and doing this on the iPhone now, so rather than rewrite the code I'll just edit the one above (which currently drops the word warehouse from the string).

    That should be it fixed now.

    Re: Excel Move B to A on text find

    Something like this should work:

    With "OtherDataCol" being the named range given to your Column B and "WarehouseCol" being the named range given to your Column A.

    Also this assumes you are using Excel 2007 or later so selecting B1048576 is just the last cell on the sheet and then moving up to the last cell with something in it to identify how long your code has to run for, this then allows for blank cells in Column B.

    Hope that all makes sense, you may need to tweak it a bit though.

    Re: Excel Move B to A on text find

    So just so I understand correctly, is it the word Warehouse you are looking to move to column A, and then lose everything that is in column B. Or do you mean that you want Warehouse to be deleted, but the text following 'Warehouse' that was contained in Column B should be moved to column A, effectively making column A a 'Warehouse' Column?

    If that makes sense.

    Re: Copy one cell at a time from a range to another sheet

    I may be misunderstanding what you are hoping to achieve, but why don't you just use the vlookup function, but instead of holding the vlookup reference in the formula, reference another cell which will allow you to type in the reference you are interested in.

    I'll throw together an example and attach it.

    Re: Creating a dynamic named range

    Perfect, that's exactly the result I was looking for.

    Wasn't how I remember doing it (as I say I'm sure there was array formulas involved the first time). But this is a far more straightforward method for the same result.

    Thanks for the help!

    Hi folks.

    I've done this once before many moons ago but can't for the life of me remember how to do it.

    When I say dynamic I'm actually meaning one where the contents only appear based on the criteria of another range (rather than it just increases in size).

    I'm sure it uses array formula to achieve, if I remember correctly.

    So I basically have a list of numbers from 1-20 called "Data_LinkageID".

    This is referenced on the input sheet for a user input in the data validation option (so the user has a dropdown list that allows them to enter the numbers 1-20).

    But the numbers they can select are only valid when they have filled a reference in beside those same numbers on the input sheet. So if they only add a reference to numbers 1-3, then only numbers 1-3 should be selectable from the dropdown list.

    Hopefully that makes sense.

    I'm sure it involves creating a second list beside Data_LinkageID based on the criteria of the corresponding number having something in the cell beside it on the input sheet.


    Re: Return Corresponding Cell For Maximum In Another Column

    Just split out the previous formula (supplied by prashantmk) into two (obviously referencing your columns).



    To return the highest value of column C.

    Then use:


    To return the adjecent cell value of column B.

    I'm new here so not sure, but I also think you're not supposed to hi-jack other threads, you should have started your own to ask the question.


    (As an afterthought, remember this would only work where you don't have two maximum values in your data that are the same. In that situation it would only find the first one from the data and display that, in theory every cell after that in the value column could have the same "maximum" value and it wouldn't be returned)

    Re: Problem with Application.ScreenUpdating = False - screen keeps on flickering

    I'm not by any means advanced in my coding skills, but if I understand it correctly it looks like you are writing from a file of some sort into the spread sheet a line at a time.

    Is it possibly this interaction between two files/applications (one not being excel) that is causing the flicker?

    Instead of working through a line at a time, could you not take the full file into an array (or series of arrays) and then work from the array into the spreadsheet?

    This means you would only be going to the source file once and if that is what is causing the flicker it might solve the problem.

    Re: Common Numbered Variables in a For Loop

    I should add, it probably won't work with the test version of the code I posted above, since I believe CallByName only works with Classes and as the UserForm is essentially a crude class module (I believe) then it works nicely for my situation.

    If you aren't passing the values back from a userform though (or a class) then this may not work for you.

    Re: Common Numbered Variables in a For Loop

    I've managed to solve the problem.

    Here is the solution for anyone else that wants to do something similar:

    The problem was basically down to the fact that I was trying to reference a string (created by the concatenation of VARIABLE + INDEX) and the VBA wasn't recognising the resulting string as the variable name.

    My understanding of what I'm doing here is using the CallByName function to create a variable name on the fly (rather than a string) and as the variable "Index" is incremented on each pass of the loop, that is changing the variable name being called (by the CallByName function) to be passed into the array (into the corresponding position, based again on the variable "Index").

    Here is more info on CallByName:…ry/chsc1tx6(v=vs.90).aspx

    Re: Common Numbered Variables in a For Loop

    A fair point well made.

    Does this suffice:

    Re: Problem with Application.ScreenUpdating = False - screen keeps on flickering

    I appreciate that (that's why I said "Unrelated to the original question, but").

    Just a pointer that he may want to tidy up the end of his code.

    As in he turned off the display alerts so he may want to turn them back on. Also:

    Application.Calculation = True

    would think would either be:

    Application.Calculation = xlCalculationAutomatic

    If he wants to undo the VBA performace improvements he added at the start of the code, or:


    If he just wants to update the calculations and keep the calculation method on manual before the code ends.

    I concur with your original statement that it sounds more like his setup rather than the VBA/Excel causing the screen flicker.

    Re: Problem with Application.ScreenUpdating = False - screen keeps on flickering

    I think he tried to be fair, just didn't encapsulate the CODE and /CODE in [].

    Unrelated to the original question, but would you not want to turn the display alerts back on after running the code as well?

    And I think your calculation method should be set back to auto rather than True.

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    End Sub


    First post, long time reader.

    All self-taught through Google so I'll apologise in advance if my jargon isn't correct.

    Basically my problem is this. I'm trying to cut down on code by indexing the variables in a For loop, but I can't seem to get it to work, I'm sure I'm missing something and this should be possible.

    Here is the code I have (at my last attempt of doing this):

    So essentially a userform is used to allow the user to enter up to 10 entries per category. I'm then trying to read these back into the arrays, before validating and passing the data into a worksheet for use by various excel formulas.

    So every array has a complimentary numbered input in the userform, which I'm trying to move back into the array using the for loop.

    So where the value of "Index" is 1 for the first position in the array there is a corresponding Input for each array with the suffix 1. So my thinking is the Index variable could be used to keep the code a bit tighter rather than having to explicitly cover all 8 Arrays 10 separate times.

    Hope that makes sense and someone can help out on this one.