Error with index match function

[mcbrocks]

Hey all!
My code looks up a user input value and returns a value from a different column a row below. This works great when I use VLookup until I realized I couldn't use the OFFSET function in VBA. So after looking through the forums, I switched to using INDEX-MATCH combination, which throws me a Error 1004, unable to find Index property.

VLookup:

CableSizeTable = Worksheets(WorksheetName).Range(Worksheets(WorksheetName).Cells(10, Column), Worksheets(WorksheetName).Cells(26,78))
CableColumn = 79-Column
CableSize = WorksheetFunction.VLookup(Val(LoadCurrentText), CableSizeTable, CableColumn)

INDEX-MATCH:

CableColumn = Columns(1)
ConductorRow = Worksheets(WorksheetName).Range(Worksheets(WorksheetName).Cells(10, Column), Worksheets(WorksheetName).Cells(26, Column))
j = 1
CableSize = WorksheetFunction.Index(CableColumn, WorksheetFunction.Match(Val(LoadCurrentText), ConductorRow, 1), -j).Value

I've tried changing

CableColumn = Columns(1)
CableColumn = Range(Columns(1))
CableColumn = Range("A:A")

but there's no difference.
Thanks in advance!

 

[ukmikeb]

deleted

 

[ukmikeb]

Hi

Here is the Index/Match formula working :-

CableSize = WorksheetFunction.Index(CableSizeTable, WorksheetFunction.match(Val(LoadCurrentText), ConductorRow, 1), 1)

giving the same result as the VLookup.

hth

NB If you post on other Forums for any related problem you should include a link on BOTH Forums - view here http://www.mrexcel.com/forum/excel-questions/127080-guidelines-posting-updated-22-nov-2006-a.html for the rules. I'm not a bit overjoyed at the thought that I could have been wasting my time because a solution may have been provided at the other Forum.

 

[mcbrocks]

Hi ukmikeb,
thanks for the solution, but it still doesn't work, the popup box shows <empty>. Just a question, what if I change CableSizeTable to a single column, do I still need a '1' at the end of the line? This '1' refers to the first column, or offset by 1 row? I'm a bit confused at this part. And I read elsewhere that placing a '-1' to the end of the Match function is to offset the row, but I tried this and it returns me a value from a cell higher in the column.

NB Thanks for alerting me on this. I'll update the thread on the other forum as well. And yes it's unfair to both ends if I do not notify them possible solutions provided, I'm terribly sorry about that. I'll definitely post on both threads when a solution has been found.

 

[ukmikeb]

Hi

What pop-up box?

Have you assigned WorksheetName to the worksheet that is displayed on opening your workbook.

Rather than worry about CableSizeTable being one column, get the Index/Match to work and not to use Reserved words as variable names, particularly Column.

The definitions I took for CableSizeTable and ConductorRow were the ones from your OP. I assumed the value of Column was 1 and LoadCurrentText was 200.

Believe me that statement does work ok, as is.

hth

 

[mcbrocks]

It works now! I was having so much trouble with either the index property or the match property earlier on. Realized I left out the last ',1' of the index property. Thanks so much ukmikeb!

I have another problem, when I click enter again, isn't the entire chunk of codes supposed to run again? After getting the first value, I keyed in another value but the code doesn't seem to be working. Other parts(those that require no calculations) are 'refreshed' but those that need calculations just displays the old value.

And what if the data has '-' in it and throws the index match loop off? Is there a way to skip these cells?
I posted this in the other thread http://www.mrexcel.com/forum/excel-questions/763250-checking-empty-cells.html

I meant the quick info box <-- pop up box

 

[mcbrocks]

Please ignore the 2nd paragraph. I finally understand how this whole index match works!! Took me long enough. I always thought that index match worked VERY differently from VLookup in regards to the references. Now I just have to concentrate on the empty cells. Any ideas?

 

[ukmikeb]

It works now! I was having so much trouble with either the index property or the match property earlier on. Realized I left out the last ',1' of the index property. Thanks so much ukmikeb!

I have another problem, when I click enter again, isn't the entire chunk of codes supposed to run again? After getting the first value, I keyed in another value but the code doesn't seem to be working. Other parts(those that require no calculations) are 'refreshed' but those that need calculations just displays the old value.

And what if the data has '-' in it and throws the index match loop off? Is there a way to skip these cells?
I posted this in the other thread http://www.mrexcel.com/forum/excel-questions/763250-checking-empty-cells.html

I meant the quick info box <-- pop up box

Hi

Pleased to hear you have it working now.

As regards your subsequent problem, since I have only seen a few lines of your code I can't make a judgement on what the code is supposed to do.

Likewise, keying another value and the code doesn't seem to be working doesn't tell me what the value is or what result you expect.

What I have seen so far of your code is that you appear to have several definitions of the same area of the worksheet and that you also refer to that same area or part of it outside of those definitions. If that approach doesn't confuse you it certainly confuses me and I'm sure other members who are looking at the problem.

Share the whole code and identify the area where the problem is and the inputs and expected results and perhaps we can move you forward to a solution.

 

[mcbrocks]

Here's the entire worksheet: https://www.dropbox.com/s/nzl3z2p733aniks/CableSizingSelection%2012Mar.xlsm
The main codes start at line 302.
Right now, I make use of the index match line twice.
The first time works just fine

VoltageDropDC = WorksheetFunction.Index(Table, WorksheetFunction.Match(Val(LoadCurrentText), ConductorRow, 1) + j, VoltageDropColumnDC)

The 2nd time, I'm making use of the value from ^. I could always reference similarly to ^ but I want to use a different approach.

CableSize = WorksheetFunction.Index(Table, WorksheetFunction.Match(VoltageDropDC, VoltageDCRow, 0), CableColumn)

I'm confused if I should use this instead.

CableSize = WorksheetFunction.Index(Table, WorksheetFunction.Match(VoltageDropDC, ConductorRow, 0), CableColumn)

I know this will work just fine though

CableSize = WorksheetFunction.Index(Table, WorksheetFunction.Match(Val(LoadCurrentText), ConductorRow, 1) + j, CableColumn)

These lines appear at line 373.

I also have a GoTo statement that I'm not sure if I'm doing it correctly or not. Line 369 GoTo Line 345.

If DC Then GoTo DCVoltageDrop
DCVoltageDrop:

I know others have said to avoid the GoTo statement like a plague, even the EXCEL VBA for Dummies mentions it but I can't think of any other way.

I confuse myself as well, especially when I had a load of comments from possible solutions and having almost similar variable names. I've deleted those useless stuff now so hopefully it's easier to see.

Inputs: quite a bit. Maybe you could run the userform and take a look?
Here's the list of it anw:
1) Option Button - Conductor
2) Option Button - Insulation
3) Option Button - Armour
4) Option Button - Core
5) Option Button - No. of Core
6) Option Button - DC/AC
7) Option Button / TextBox - Acceptance Loss
8) Option Button - Installation Method
9) TextBox - MotorRating
10) TextBox - CableLength
11) TextBox - LoadCurrent
12) TextBox - PowerFactor
13) TextBox - Efficiency

The other stuff you see on the userform, I have yet to make use of them.

Outputs:
1) VoltageDrop in percentage
2) CableSize

That's all for now.

 

[ukmikeb]

Hi

On clicking enable macros I get a message about altering ActiveX controls which I decline.

Possibly as a result I only see 4 Macros, the longest code being about 6 lines.

Please share the code, which I presume is attached to your User Form from line 302.

Thanks

 

[mcbrocks]

'__________________________________________________________Current Carrying Capacity Column____________'
    If TwoCoresCables Then Column = 2
    If ThreeFourCoresCables Then Column = 38
    
    If DC Then Column = Column + 0 'Column = 2
    If DC Then Multiplier = 1
    If SingleAC Then Column = Column + 8 'Column = 10
    If SingleAC Then Multiplier = 4
    If ThreeAC Then Column = Column + 0 'Column = 38
    If ThreeAC Then Multiplier = 4
    
    If Method1 Then Column = Column + Multiplier * 0
    If OneTrefoil Then Column = Column + Multiplier * 7
    If Method3 Then Column = Column + Multiplier * 1
    If Method4 Then Column = Column + Multiplier * 2
    If Method11 Then Column = Column + Multiplier * 3
    If ElevenTrefoil Then Column = Column + Multiplier * 8
    If Horizontal Then Column = Column + Multiplier * 4
    If Vertical Then Column = Column + Multiplier * 5
    If TwelveTrefoil Then Column = Column + Multiplier * 9
    If Method13 Then Column = Column + Multiplier * 6
'_____________________________________________________________________Table / Conductor Row____________'
    If Aluminium Then
        Table = Worksheets(WorksheetName).Range(Worksheets(WorksheetName).Cells(10, Column), Worksheets(WorksheetName).Cells(26, 78))
        ConductorRow = Worksheets(WorksheetName).Range(Worksheets(WorksheetName).Cells(10, Column), Worksheets(WorksheetName).Cells(26, Column))
        VoltageDCRow = Worksheets(WorksheetName).Range(Worksheets(WorksheetName).Cells(10, 10), Worksheets(WorksheetName).Cells(26, 10))
        VoltageACRRow = Worksheets(WorksheetName).Range(Worksheets(WorksheetName).Cells(10, Column + VoltageDropColumnR), Worksheets(WorksheetName).Cells(26, Column + VoltageDropColumnR))
    End If
    If Copper Then
        Table = Worksheets(WorksheetName).Range(Worksheets(WorksheetName).Cells(28, Column), Worksheets(WorksheetName).Cells(49, 78))
        ConductorRow = Worksheets(WorksheetName).Range(Worksheets(WorksheetName).Cells(28, Column), Worksheets(WorksheetName).Cells(49, Column))
        VoltageDCRow = Worksheets(WorksheetName).Range(Worksheets(WorksheetName).Cells(28, 10), Worksheets(WorksheetName).Cells(49, 10))
        [COLOR=#ff0000]VoltageACRRow = Worksheets(WorksheetName).Range(Worksheets(WorksheetName).Cells(28, Column + VoltageDropColumnR), Worksheets(WorksheetName).Cells(49, Column + VoltageDropColumnR))[/COLOR]
    End If
'_________________________________________________________________________Cable Size Column____________'
    CableColumn = 79 - Column
'_______________________________________________________________________Voltage Drop Column____________'
    VoltageDropColumnDC = 11 - Column
    VoltageDropColumnR = 2
    VoltageDropColumnX = 3
'_________________________________________________________________________Empty Cells Check____________'
'_________________________________________________________________________Find Voltage Drop____________'
    j = 1
    If DC Then
DCVoltageDrop:
        [COLOR=#00ff00]VoltageDropDC = WorksheetFunction.Index(Table, WorksheetFunction.Match(Val(LoadCurrentText), ConductorRow, 1) + j, VoltageDropColumnDC)[/COLOR]
        DesignCurrent = MotorRatingText / ((EfficiencyText / 100) * DCText)
        CalculatedVoltageDrop = (VoltageDropDC * DesignCurrent * CableLengthText) / 1000
    End If
    If SingleAC Or ThreeAC Then
ACVoltageDrop:
       [COLOR=#00ff00] VoltageDropR = WorksheetFunction.Index(Table, WorksheetFunction.Match(Val(LoadCurrentText), ConductorRow, 1) + j, VoltageDropColumnR)
        VoltageDropX = WorksheetFunction.Index(Table, WorksheetFunction.Match(Val(LoadCurrentText), ConductorRow, 1) + j, VoltageDropColumnX)[/COLOR]
        Angle = WorksheetFunction.Acos(PowerFactorText)
        SineAngle = Sin(Angle)
        If SingleAC Then ACText = SingleACText
        If ThreeAC Then ACText = ThreeACText * (3 ^ (1 / 2))
        DesignCurrent = MotorRatingText / (PowerFactorText * (EfficiencyText / 100) * ACText)
        CalculatedVoltageDrop = (((VoltageDropR * PowerFactorText) + (VoltageDropX * SineAngle)) * DesignCurrent * CableLength) / 1000
    End If
'_______________________________________________________________________________Output Loss____________'
[COLOR=#ff8c00]    If DC Then InputVoltage = DCText
    If SingleAC Or ThreeAC Then InputVoltage = ACText
    VoltageLoss = CalculatedVoltageDrop / InputVoltage
    VoltageLossPercentage = VoltageLoss / 100
    
    If VoltageLossPecentage >= Loss Then
        j = j + 1
        If DC Then GoTo DCVoltageDrop
        If SingleAC Or ThreeAC Then GoTo ACVoltageDrop
    End If[/COLOR]
'___________________________________________________________________________Find Cable Size____________'
   [COLOR=#ffa07a] If DC Then CableSize = WorksheetFunction.Index(Table, WorksheetFunction.Match(VoltageDropDC, VoltageDCRow, 0), CableColumn)[/COLOR]
    [COLOR=#ff0000]If SingleAC Or ThreeAC Then CableSize = WorksheetFunction.Index(Table, WorksheetFunction.Match(VoltageDropR, VoltageACRRow, 0), CableColumn)[/COLOR]
'______________________________________________________________________________________Calc____________'
    Calc = Calc & vbNewLine & "VoltageLoss: " & VoltageLoss
    Calc = Calc & vbNewLine & "VoltageLossPercentage: " & VoltageLossPercentage & "%"
    Calc = Calc & vbNewLine & "CableSize: " & CableSize
    
    If DC Then Calc = Calc & vbNewLine & "VoltageDrop: " & VoltageDropDC
    If SingleAC Or ThreeAC Then
        Calc = Calc & vbNewLine & "VoltageDropR: " & VoltageDropR
        Calc = Calc & vbNewLine & "VoltageDropX: " & VoltageDropX
        Calc = Calc & vbNewLine & "Angle: " & Angle
        Calc = Calc & vbNewLine & "SineAngle: " & SineAngle
    End If
    
    Calc = Calc & vbNewLine & "DesignCurrent: " & DesignCurrent
    Calc = Calc & vbNewLine & "CalculatedVoltageDrop: " & CalculatedVoltageDrop
    
    Messages = Msg & Change & Changes & Calc
'_____________________________________________________________________________Final Display____________'
    Display9.Text = Messages
    Display9.MultiLine = True

The green index-match lines are working fine.
Under 'Find Cable Size', the lighter colored ones are working, the red ones aren't. There's a 'unable to find match property' error message.I've highlighted VoltageACRRow at the top which I think might be the one giving problems.
The Orange chunk at 'Output Loss' is supposed to check the 'VoltageDrop' value against another 'Loss' Value (usually 1.5 or 2.5). So if it's more than, it goes back to the GoTo statement, and does the index-match code again until it is more than 'Loss'. This whole chunk isn't working.