Excel - converting IF statement to Select Case

[markozzz]

Hi,
i have a rather easy problem, but i cant seem to work it out correctly..

I have a looong IF-statement, which i have to convert to an SelectCase statement. The statement should apply to all rows in a distinct column (H), or at least 10000 rows

The IF-statement i originally made, was like this;

=IF(Sheet6!H2="LOC500";VLOOKUP(VLOOKUP(Sheet6!A2;Sheet2!A;4;FALSE);Sheet4!G:H;2;FALSE);IF(Sheet6!H3="LOC501";VLOOKUP(VLOOKUP(Sheet6!A3;Sheet2!A;4;FALSE);Sheet4!G:I;3;FALSE)IF(..........))

and it continues, where "LOCxxx" is a variable (total of 10 varibles), and the last part of the vlookup (table array); "...Sheet4!G:I;3;FALSE" where "I" increases one letter and the number "3" increases with one pr IF-statement

Can somebody help me putting this into VBA please ?
thanks,
martin

 

[mikej336]

Sorry, But I am totally confused to the usefullness of the original formula...

If I undersand correctly. You are placing this formula in a cell and you want it to look down column (H) for at least 10000 rows looking for a value similar to "LOCxxx" and if a particular value exist in a paritcular cell exist then you want it to do 2 vlookup's to ultimately return a value....

Why don't we know what and where a value is in H?
Are values randomly appearing in H then disappearing?

What if H2="LOC500" and H3="LOC501"?

Perhaps you could explain the process of what you are trying to do?


Uncle Mike

 

[markozzz]

Hi,

I understand that you are confused, and that IF-statement isn`t really good..

In general, whats a bit special is that if one value in H-column matches e.g "LOC500" in sheet1, then i want to lookup a value in an other column in that same row. but i cant lookup the value im looking for in sheet2 directly. i have to go through a third sheet, concatenating two values to make the lookupvalue unique. This is because its a many-many relation between "LOCxxx" and the value i want to end up with.

Still confusing ? hehe.

That IF-statement is long gone now, i have started doing this a way easier way in vba, but im a beginner so im sure its an even easier way.


Sub myRow()

Dim myRow As Long

For myRow = 1 To 10000
If Cells(myRow, 8).Value = "LOC500" Then
Cells(myRow, 5).Interior.Color = vbGreen
End If
Next myRow

End Sub

This is the principp i made. Obviously, i dont want anything painted green.. I just need the lookup-statement to fit in..

 

[SkipVought]

Hi,

If you give vague information, you will not get answers, but more questions.

Whay do we have to guess at what this means?

"...if one value in H-column matches e.g "LOC500" in sheet1, then i want to lookup a value in an other column in that same row. but i cant lookup the value im looking for in sheet2 directly. i have to go through a third sheet, concatenating two values to make the lookupvalue unique"

Do you think that that is enough information for someone who cannot see into your head, or see what's in your workbook, to come up with a meaningful answer?

With that level of detail, the best I could do is suggest that you check out functions like INDEX, MATCH, VLOOKUP, possibly OFFSET, COUNTIF, maybe LEFT, RIGHT, MID. Or you might be able to use MS Query.

Sorry, I'm out of vague solutions.



Skip,

_[red][/red]

 

[markozzz]

Whatever, i figuered out myself..

In case anyone wonders;

Dim myRow As Long
Dim LOCno_hit As String
Dim Valve As String

'Definerer aktivt område å jobbe i
For myRow = 1 To 10000

'Definerer tagno som string
Valve = Cells(myRow, 1)

If Cells(myRow, 8).Value = "LOC500" And Left(Valve, 1) = "W" Then
    LOCno_hit = Application.WorksheetFunction.VLookup(Cells(myRow, 1), Sheets("Kesys_output").Range("A:D"), 4, False)
           Cells(myRow, 5).Value = Application.WorksheetFunction.VLookup(LOCno_hit, Sheets("LOC2_Valves").Range("G:H"), 2, False)

'Hvis LOC501 inntreffer og Tag_No begynner med "W"

ElseIf Cells(myRow, 8).Value = "LOC501" And Left(Valve, 1) = "W" Then
    LOCno_hit = Application.WorksheetFunction.VLookup(Cells(myRow, 1), Sheets("Kesys_output").Range("A:D"), 4, False)
           Cells(myRow, 5).Value = Application.WorksheetFunction.VLookup(LOCno_hit, Sheets("LOC2_Valves").Range("G:I"), 3, False)

And so forth.. Im sure it can be done prettier, but it works and im happy

 

[mikej336]

Mark,

I did not have time to get this working so this is just psueodo code.... Based on your examples you code might look something like this...

Your first lookup only varied with the row #, which you know.

Your second lookup varied by the answer of the first lookup, the column off set and number of columns returned, but if you include all columns in the search then you just need the return the correct offset of columns.


Sub myRow()

Dim myRow As Long
Dim myloc as text
dim myoffset as int
dim ans1 as text
dim ans2 as text
dim lookup1 as text

For myRow = 1 To 10000

myloc=Cells(myRow, 8).Value

If left(myloc,3) = "LOC" Then
'take the number of the LOCxxx and increase it by the offset # of columns
myoffset=int(right(myloc,2))+3

lookup1 ="Sheet6!A" & str(myRow)
ans1=VLOOKUP(lookup1;Sheet2!A;4;FALSE)
ans2=VLOOKUP(ans1;Sheet4!G:z;myoffset;FALSE)
Cells(myRow, 5).Interior.value = ans2
End If
Next myRow

End Sub

Hope this helps...

Uncle Mike

 

[markozzz]

Thanks mike!

Its all working now.

Rgds,
Markz