Checking for empty cells

[mcbrocks]

Hi all,
I am doing an index match lookup on a list of data. This list has some blank values (I put '-' instead of leaving it blank). How do I code it such that if there is a '-' I can move on to the next value down the list, and stop when the rest are '-'.
Thanks in advance!

Here's a look at the worksheet: https://www.dropbox.com/s/w280jdk5hxvutp6/CableSizingSelection 11Mar-2.xlsm

 

[oldbrewer]

=if(a1="-","",vlookup(a1,mytable,2))

 

[mcbrocks]

Thanks oldbrewer, could you explain the code? Does it mean if Cell a1 is '-' or <empty>, lookup a1 in range(mytable) and return the 2nd column?
I was looking for something more complicated (not the most ideal word to describe)
This is what I've got so far:

    If Aluminium Then NonBlankRow = Worksheets(WorksheetName).Range(Worksheets(WorksheetName).Cells(10, Column), Worksheets(WorksheetName).Cells(26, Column)).Cells.SpecialCells(xlCellTypeConstants).Count
    If Copper Then NonBlankRow = Worksheets(WorksheetName).Range(Worksheets(WorksheetName).Cells(28, Column), Worksheets(WorksheetName).Cells(49, Column)).Cells.SpecialCells(xlCellTypeConstants).Count
    If Aluminium Then StartRow = Range(Worksheets(WorksheetName).Cells(10, Column), Worksheets(WorksheetName).Cells(26, Column)).Find("-", Cells(Rows.Count, Column), xlValues, xlWhole, , xlNext).Address
    If Copper Then StartRow = Range(Worksheets(WorksheetName).Cells(28, Column), Worksheets(WorksheetName).Cells(49, Column)).Find("-", Cells(Rows.Count, Column), xlValues, xlWhole, , xlNext).Address
    EndRow = StartRow + NonBlankRow - 1

 

[mcbrocks]

I just realized I can't use my code as I have '-' in between my data at random intervals.

 

[oldbrewer]

my formula means if A1 is "-" then return a blank, otherwise look up the value of A1 in the lookup table

show us some of your data with random "-" and we can look for a work round

 

[mcbrocks]

Hi oldbrewer,
how do I upload the picture? I tried inserting the url but nothing happens.

-
-
10
20
30
-
40
50
-
-

<tbody>
</tbody>

This is roughly what it looks like. There's a index match line running through it, so when it hits a '-', it will skip on to the next value and not display an error.

 

[oldbrewer]

I will make up a little spreadsheet and devise some code for you in a couple of hours - basically you need a loop that visits each cell in the column in turn but if it finds a blank move on without doing anything - some thing like

for j=1 to 1000
if cells(j,1)="-" then goto 100
tot=tot+1
cells(tot,5)=vlookup(cells(j,1),mytable,2)
100 next j

don't rely on above it is to show the approach....

 

[Doreen17]

Didn't work for me I have a really old version though, can this be the reason?

 

[Jon von der Heyden]

I think it will help if you display tables as you have done in post #6

1. What is your INDEX MATCH formula (please post it)
2. What are the lookup value(s) used by MATCH
3. What result do you anticipate?

 

[mcbrocks]

Here's the index-match formula:

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

It looks up Val(LoadCurrentText) and returns an offset value from a column in Table. So if it's empty, it basically looks up the next value below. And if it has reached the end of the table, it returns the value from that last row, and displays a message to let the user know the value returned may be incorrect.