Posts by mar0507

Re: Looping VBA

Solved.

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).

Hi,

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:

Dim tbl As Range
Dim n As Range
Set tbl = Range("H6:K30")
Set n = Range("B2:B32")
Worksheets("Nov").Select
Range("B2").Select
For Each cell In n
    ActiveCell.Offset(0, 1) = Application.WorksheetFunction.VLookup(Range("A2"), tbl, 2, False)
    ActiveCell.Offset(0, 2) = Application.WorksheetFunction.VLookup(Range("A2"), tbl, 3, False)
    ActiveCell.Offset(0, 3) = Application.WorksheetFunction.VLookup(Range("A2"), tbl, 4, False)
Next cell

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.

Thanks

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

Please find a test version, with much less data regarding my validatelist query

Thanks

Re: Validatelist in VBA

Quote from royUK;692143

Try this

Code

Dim rCl As Range
    With Sheets("Sheet1")
        Set rCl = .Range("A21").End(xlDown).Offset(1)


        rCl.Value = TxtOrderDate.Text
        rCl.Offset(0, 1) = TxtCustName
        rCl.Offset(0, 3) = TxtEmail
        With rCl.Offset(0, 4).Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                 xlBetween, Formula1:="=status"
            .InCellDropdown = True
            .ShowInput = True
            .ShowError = True
        End With
    End With

Display More

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.

Thanks

Hello

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
 
 Unload.UserForm
 
End With

however when I try and use activecell, it fails:

Sheets("Sheet1").Select
Range("A21").Select
Do
    If IsEmpty(ActiveCell) = False Then
    ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = TxtOrderDate.Text
ActiveCell.Offset(0, 1) = TxtCustName
ActiveCell.Offset(0, 3) = TxtEmail
With Range.Offset(0, 4) = .Validation
.Add xlvaldiatelist, xlalertstop, xlBetween, "=Status"
.InCellDropdown = True
End With
End Sub

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

Hi

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

Private Sub Combobox1_Change()
ComboBox1.AddItem "Personnal"
ComboBox1.AddItem "Business"
End Sub [\code]


And the second Combobox will be:
[code]


Private Sub ComboBox2_Change()
 
If ComboBox1.Value = "Business" Then
ComboBox2.items.Add ("BCH")
ComboBox2.items.Add ("BBH")
ElseIf ComboBox1.Value = "Personnal" Then
ComboBox2.items.Add ("PCH")
ComboBox2.items.Add ("POL")
ComboBox2.items.Add ("PPL")
End If
End Sub

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

I have two comboboxes on a userform, the 1st has a row source referring to a named range (Sheet1!A1:A10)

I want the second combobox to list the department.

Can anyone help please?

Re: Enable/Disable Text Box

Thanks = well spotted.

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?

Thanks

Re: Named Ranges in Formula

Thank you, INDIRECT is exactly what I am looking for, works a treat.

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

Thanks

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

Daniel.c.

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)

Thanks