Posts by mar0507

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: Looping VBA


    I have changed the code to:

    cell.Offset(0, 1) = Application.IfError(Application.VLookup(cell.Offset(0, -1), tbl, 2, False), "")

    A subtle change by removing worksheetfunction

    Re: Looping VBA

    Thank, I solved the problem using

    cell.Offset(0, 2) = Application.WorksheetFunction.VLookup(cell.Offset(0, -1), tbl, 2, False)

    However I am now trying to sort the issue where vlookup crashes.

    I am using:

    cell.Offset(0, 1) = Application.WorksheetFunction.IsError(Application.WorksheetFunction.VLookup(cell.Offset(0, -1), tbl, 2, False), 0)

    but I still get a 1004 run-time.

    Any suggestion please.

    Re: Looping VBA

    I have realised my problem, the range in the vlookup is A2, so how do I force the code to move to A3 when I am looping down (from B2 to B??)

    Re: Looping VBA

    Thank you, I have changed to cell.offset, but now it is putting the first set of data in all cells, rather than looking up the next cell in range (ie from column b3 and so forth).


    I am trying to loop through a range of cells (B2:B32) (although that range could extend).

    With that loop I am trying to then do a vlookup, and copy the data from that to offset columns 1,2, and 3.

    My code is as follows:

    But it appears not to loop the program only does the first row

    I need the data from tbl (columns 2,3, and 4 to in essence go to C2, C3, and C4, and downwards is the loops.

    Also, if the vlookup is not found in tbl, I get an error, I would then like column 2, 3 an 4 (dependant on what row) would give a 0.


    Re: Validatelist in VBA

    Thankyou that is perfect, however, I am trying to get it to default to the 1st value in the validatelist, I am using the following code (after your kind coding):

    rCl.Value = Replace(ActiveCell.Validation.Formula1, "=", "").Cells(1, 1).Value

    However I am getting an invalid qualifier error.

    Re: Validatelist in VBA

    Quote from royUK;692143

    Try this

    Note also that your code has typos

    Thank you for your code - however

    Set rCl = .Range("A21").End(xlDown).Offset(1)

    says an object is required.

    I am sorry, I am reasonably new at this VBA.



    I am trying to force VBA to move to a cell and populate it with a validation list.

    When I actively tell VBA what the cell is it works fine

    Private Sub CommandButton1_Click()
    With Range("D2").Validation
     .Add xlValidateList, xlValidAlertStop, xlBetween, "=Name"
     .InCellDropdown = True
    End With

    however when I try and use activecell, it fails:

    I get an argument not optional error.

    I wish to then move along yet another cell etc

    Can anyone help please?

    Re: Combobox based on another Combobox

    I have go the code for combobox1 working now, by adding at part of the user form initialise sequence, but still the code for Combobox2 does not work, please can someone help me?

    Re: Combobox based on another Combobox


    I am changing the way to do it, I have the following code

    neither of these codes work.

    Any ideas?

    Re: Combobox based on another Combobox

    I am sorry, I got a bit lost in all that coding, I am quite new to this.

    My namedrange is called "Name" (Personnal or Business)

    How do I get combobox2 to populate the associated. ie PCH, PLP, or the other choices would be BCH, or BLB

    Thanks again

    Hi There

    I am trying to enable a text box if check box is ticked.

    I currently have this code disabling the textbox at userform startup

    Private Sub UserForm_Initialize()
    TextBox1.Visible = False
    End Sub

    I have the following code when the check box is ticked:

    Private Sub CheckBox1_Click()
    TextBox1.Enabled = CheckBox1
    End Sub

    Somehow though the enabling textbox bit does not work. I have set the property of the textbox to enabled = false.

    Please can someone help?


    Re: Named Ranges in Formula

    Thank you Rory,

    What I have are several sheets, all relating to different financial years, and a summary sheet.

    The individual sheets have all the different companies within the group and the respective figures, and what I want on the summary sheet is to select the relevant info for the year that is quoted on the Summary Sheet.

    As the formula is using SUM, INDEX and MATCH, and the number of years is goign to increase, is there another way other than the IF, as I will have more than 7 (which I beleive to be the max number of IF's).

    Hope that makes sense


    I have created a list of Named Ranges within a workbook, however is there a way to force a formula to select which Named Range to use depending on the value of another cell.

    What I have are named ranges for varying financial years, and therefore I need the formula to select the named range based on the given financial year. Currently i am having to change the formula each time the financial year changes.

    Many Thanks for your help

    Re: Multiple Condition Sum


    This is the problem I will have multiple occurances of the country.

    Maybe I could create a separate formula to club them all together by each individual month and then use your Sum, offset formula.

    Thanks[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Daniel

    Sorry just noticed your merged post - thanks works a treat[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Daniel

    Sorry just noticed your merged post - thanks works a treat[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Daniel

    The SUMPRODUCT formula works a treat, however is it possible to work back (currently it calculate lookign forward: Columns C through to D, E, F etc, However I now want to start at say Column F, and calculate columns back E, D, C etc. I know within OFFSET you just put a minus in, but this does not seem to work.

    Thanks again

    Re: Multiple Condition Sum

    File Attached.

    In cell B11, I would require the sum of all UK (A11) from April through to June (B10). Currently I have all uk for June only.

    What I need, is to be able to sum all relevant country up to the relevant month (B10). Therefore if B10 is May, only April to May for Uk (or whatever the chosen country is)