Excel VBA index/match formula to skip value already returned.

vhdhfox

New Member
Joined
Aug 6, 2020
Messages
36
Office Version
  1. 2013
Platform
  1. Windows
I'm currently designing a multiple userform excel sheet and have come across an issue when returning values for 4 text boxes (labourbox1,labourbox2,labourbox3,labourbox4) as they are returning value from the same column using the same criteria reference it only displays the first cell in that column using that reference.
this userform is displayed once a button is pressed on another userform and will then use the value from "ContainerBox1" as the criteria for the second userform from there the text boxes in the second userform are populated partly from the first userbox as well as the data sheet.

VBA Code:
Private Sub UserForm_Initialize()

    Dim A As Variant, B As Variant, C As Variant, D As Variant
   

    Me.ContainerCMBVF.Text = QueryForm.ContainerBox1.Value
    Me.ClientBoxVF.Text = QueryForm.ClientBox1.Value
    Me.AgencyBoxVF.Text = QueryForm.AgencyBox1.Value
    Me.TaskBoxVF.Text = QueryForm.TaskBox1.Value
    Me.DateBoxVF.Text = QueryForm.DateBox1.Value
   
    A = ContainerCMBVF.Value
    Sheets("Planning").Activate
   
    If A = "" Then
        B = ""
    Let LabourBox1VF.Text = B
        C = ""
    Let LabourBox2VF.Text = C
        D = ""
    Let LabourBox3VF.Text = D
   
        Else
       
    B = Application.WorksheetFunction.Index(Sheets("Planning").Range("D2:D100"), Application.WorksheetFunction.Match(A, Sheets("Planning").Range("E2:E18"), 0))

        Let LabourBox1VF.Text = B
   
    C = Application.WorksheetFunction.Index(Sheets("Planning").Range("D2:D100"), Application.WorksheetFunction.Match(A, Sheets("Planning").Range("E2:E18"), 1))

        Let LabourBox2VF.Text = C
   
    D = Application.WorksheetFunction.Index(Sheets("Planning").Range("D2:D100"), Application.WorksheetFunction.Match(A, Sheets("Planning").Range("E2:E18"), 1))

        Let LabourBox3VF.Text = D
   
    End If
   
           
End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,161
Office Version
  1. 2013
Platform
  1. Windows
@vhdhfox I cannot pretend that I fully envisage your setup/scenario. However, my observations would be as below.

In the expressions for B, C & D, why index D2:D100 if the MATCH of A in E2:E18 can only return a max value of 18 ?

If you are wanting to match three different occurrences of A then, I think you need to be expressing the ranges with an offset/reduction in some way, following each match?

Eg OS = 0
Result = Match(A,Sheets( "Planning") .Range( "E" & 2+OS & ":E18"),0) + OS would be 3 if there was a match in row three. Use Result for the index row for B
OS = Result
Result = Match(A,Sheets( "Planning") .Range( "E" & 2+OS & ":E18"),0) + OS would be 3 if there was a match in row three. Use Result for the index row for C Etc.
OS = Result
Hope that makes sense and is a help.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,732
Messages
5,638,042
Members
417,000
Latest member
JasonWilliam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top