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]

Hi

In Post #18 you said :-

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:

which from looking at your code you go back to the "middle" of an IF statement. Not that this is the source of some of your problems, but it could be.

Since the GoTo initiating the return is "If DC Then" and the line prior to the label is "If DC Then" it won't be a problem to move the label up one line.

This also applies to "If AC Then" and the relevant label for that process.

If DC and AC processes are mutually exclusive then would it not be appropriate to have sections of the code for each process rather than them mixed together with several If statements to segregate each element.

I may not be able to get back to this today as I'll be away from my computer.

 

[mcbrocks]

Hi ukmikeb,
I've moved the label up a row but there's no difference. I never thought of splitting the 2 processes, I'll probably tidy up when this whole project is done. Is there another way to loop back besides the GoTo method?

 

[mcbrocks]

Also, what happens if my value exceeds the index match
My value is 200

5

50

100

150

175

<tbody>
</tbody>

So far, I just get a 'Unable to find match property' error. How do I eliminate this and just return the last value '175'?

 

[ukmikeb]

Hi ukmikeb,
I've moved the label up a row but there's no difference. I never thought of splitting the 2 processes, I'll probably tidy up when this whole project is done. Is there another way to loop back besides the GoTo method?

I think its best for you to give me a breakdown of what the purpose of the process is.

As I see it your engineers put in a Current Carrying Capacity and somehow they are given a Cable Size according to the various "Methods" and the Cable Sizes are common for DC where you reference Columns B to J, SinglePhaseAc where you reference columns K to AK and ThreePhaseAC where you reference columns AL to BY and column BZ is a replicate of Column A. All references to the DataPVC1 sheet.

Also, what happens if my value exceeds the index match
My value is 200

5

50

100

150

175

<tbody>
</tbody>

So far, I just get a 'Unable to find match property' error. How do I eliminate this and just return the last value '175'?

It might be necessary to use free columns on the Worksheet to house the Match functions.

I can't see those values on the above-mentioned sheet, could you say which column you are referring to.

Thanks

 

[ukmikeb]

Hi

Further to the above.

You highlighted the VoltageACRRow calculation within Copper as being in error.

If you move the Cable Size Column and Voltage Drop Column settings to occur before you do the Table / Conductor Row calculations for Aluminium and Copper you are unlikley to have a problem.

At the moment it is possible for the Copper Calculation to be incorrect because VoltageDropColumnR is zero rather than a value.

hth

 

[ukmikeb]

Hi

Further still to the above.

Should this line :-

If VoltageLossPecentage >= Loss Then

be this :-

If VoltageLossPercentage >= VoltageLoss Then

If so I would recommend that you put this :-

Option Explicit

as the very first line of code for your module.

If not, where does Loss come from?

hth

 

[mcbrocks]

Hi ukmikeb! You got the process right in post #24. I've settled that using the multipliers so that the 'pointer' is at the Current Carrying Capacity Column of the various methods. And the table I've given is only an example, not from any worksheets.
I did 'redo' the whole code using If-Then-Else which is much more lengthy but more understandable to me, but now all my calculations are empty. Haha! I'll get back to the old worksheet (Index-Match) and try out the suggestions you've provided. Lucky for me I've done a daily backup.

Loss comes from around Line 313, determined by

    If MainSystem = True Then Loss = MainSystemText
    If SubSystem = True Then Loss = SubSystemText

VoltageLoss comes from (Calculations using data from worksheets) / Input Voltage.

I'm avoiding

Option Explicit

as I find them troublesome! But it does help with the run-time so I'll get to it by the end of the day.

With regards to VoltageDropX, I used

If VoltageDropX = Empty Then VoltageDropX = 0

PS. I'm not in the office during the weekends and I can't bring the file out of office due to regulations so I can only reply after the weekend is over.