complex vlookup and if situation..help!!!

[eddy23]

I HAVE A VERY COMPLEX SITUATION
I want a formula or a vb code that could perform the following.


Basically
On sheet named D Cell 'D'A1 is sum of two cells = D!I10 + 'variable'!A5. this variable is represented by the names A to Z.
I have range of cells 'main'A20:J32 on sheet called 'main'. This range contains those variables A to Z arranged in pairs in adjacent cells e.g. cell A22=A, Cell B22=D and cell I23=F and Cell J23=G. These pairs are unique and only occur once in the range.
The letters represents worksheets named A,B,C,D....Z respectively.

I need a formula or Vb code that would Lets say look letter D in the range cells 'main'A20:J32 then identify the adjacent letter that could be either in the immediate right or left cell. Then whatever letter that is go to its worksheet take the value from cell A5 from that worksheet so that
'D'A1= D!I10 + variable!A5

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
E.g. lets say D is found to be in the range at Cell B22 hence the neighbouring variable could either be in A22 or D22, if this letter is A then go to sheet named A and cell A5..take its value and add it to the Cell 'D'A1.
<o></o>

If anyone could help me on this i would honestly treat him/her.

 

[eddy23]

hmmm please can som1 help

 

[Peter_SSs]

This range contains those variables A to Z arranged in pairs in adjacent cells e.g. cell A22=A, Cell B22=D and cell I23=F and Cell J23=G. These pairs are unique and only occur once in the range.[/SIZE][/FONT]





E.g. lets say D is found to be in the range at Cell B22 hence the neighbouring variable could either be in A22 or D22, if this letter is A then go to sheet named A and cell A5..take its value and add it to the Cell 'D'A1.
<o></o>

I'm not sure I'll have an answer anyway, but I don't uderstand the two large red bits. The top part of your post that I have quoted here says that the pairs occur in adjacent cells, but B22 and D22 are not adjacent.

Also, if we are looking for 'D' and find it in B22, what do we do if there is, say, an 'A' in adjacent cell A22 and a 'C' in adjacent cell C22? Which one to choose?

 

[eddy23]

I'm not sure I'll have an answer anyway, but I don't uderstand the two large red bits. The top part of your post that I have quoted here says that the pairs occur in adjacent cells, but B22 and D22 are not adjacent.

Also, if we are looking for 'D' and find it in B22, what do we do if there is, say, an 'A' in adjacent cell A22 and a 'C' in adjacent cell C22? Which one to choose?

NO we can only have either A in cell A22 or C in C22 not both of them at the same time i.e 'D' will only one adjacent cell containing value and both 'D' and the adjacent value are unique they only occur once in the range

 

[Peter_SSs]

See if this is on the right track.

Note that, if so, the code could be simplified if the 'main' sheet could have a new column A inserted so that the table was in columns B:K instead of A:J and also if we knew that the next column right after this table had nothing in it (at least in rows 20:32). Would that be possible?


<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> VariableSheetLookup()<br>    <SPAN style="color:#00007F">Dim</SPAN> LookupRange <SPAN style="color:#00007F">As</SPAN> Range, LetterFound <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> myLetter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, myNewLetter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> LookupRange = Sheets("main").Range("A20:J32")<br>    myLetter = "D"<br>    <SPAN style="color:#00007F">Set</SPAN> LetterFound = LookupRange.Find(What:=myLetter, LookIn:=xlValues, _<br>                        LookAt:=xlWhole, SearchFormat:=False)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> LetterFound <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> LetterFound<br>            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> .Column<br>                <SPAN style="color:#00007F">Case</SPAN> LookupRange.Column<br>                    myNewLetter = .Offset(, 1).Text<br>                <SPAN style="color:#00007F">Case</SPAN> LookupRange.Column + LookupRange.Columns.Count - 1<br>                    myNewLetter = .Offset(, -1).Text<br>                <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN><br>                    myNewLetter = .Offset(, -1).Text & .Offset(, 1).Text<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> Sheets(myLetter)<br>            .Range("A1").Value = _<br>                .Range("I10").Value + Sheets(myNewLetter).Range("A5").Value<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN><br>        MsgBox myLetter & " not found"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[eddy23]

See if this is on the right track.

Note that, if so, the code could be simplified if the 'main' sheet could have a new column A inserted so that the table was in columns B:K instead of A:J and also if we knew that the next column right after this table had nothing in it (at least in rows 20:32). Would that be possible?

yes i can certainly do that though at the moment i have some data in column K but i can surely move it, if that would simlyfy the code and yes i can move the table columns B:K.

also how about if i break up the range into two halfs. A20:B32 and I20:J32 because the remaining Columns i.e. C,D,E,F,G,H, never contain anything in them.

 

[Peter_SSs]

... the remaining Columns i.e. C,D,E,F,G,H, never contain anything in them.

Seems to contradict from post #1 ...

E.g. lets say D is found to be in the range at Cell B22 hence the neighbouring variable could either be in A22 or D22

and from post #4 ...

we can only have either A in cell A22 or C in C22

However, if ...

1. The letters are in A20:B32 or I20:J32 and

2. There is nothing in C20:H32 and

3. Otherwise my code seemed to do the right thing

... then there is in fact no need to re-arrange the 'main' sheet at all. Try this slight variation:

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> VariableSheetLookup()<br>    <SPAN style="color:#00007F">Dim</SPAN> LookupRange <SPAN style="color:#00007F">As</SPAN> Range, LetterFound <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> myLetter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, myNewLetter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> LookupRange = Sheets("main").Range("A20:J32")<br>    myLetter = "D"<br>    <SPAN style="color:#00007F">Set</SPAN> LetterFound = LookupRange.Find(What:=myLetter, LookIn:=xlValues, _<br>                        LookAt:=xlWhole, SearchFormat:=False)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> LetterFound <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> LetterFound<br>            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> .Column<br>                <SPAN style="color:#00007F">Case</SPAN> LookupRange.Column, _<br>                        LookupRange.Column + LookupRange.Columns.Count - 2<br>                    myNewLetter = .Offset(, 1).Text<br>                <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN><br>                    myNewLetter = .Offset(, -1).Text<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> Sheets(myLetter)<br>            .Range("A1").Value = _<br>                .Range("I10").Value + Sheets(myNewLetter).Range("A5").Value<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN><br>        MsgBox myLetter & " not found"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[eddy23]

Seems to contradict from post #1 ... and from post #4 ...

However, if ...

1. The letters are in A20:B32 or I20:J32 and

2. There is nothing in C20:H32 and

3. Otherwise my code seemed to do the right thing

... then there is in fact no need to re-arrange the 'main' sheet at all. Try this slight variation:


Sub VariableSheetLookup()
Dim LookupRange As Range, LetterFound As Range
Dim myLetter As String, myNewLetter As String

Set LookupRange = Sheets("main").Range("A20:J32")
myLetter = "D"
Set LetterFound = LookupRange.Find(What:=myLetter, LookIn:=xlValues, _
LookAt:=xlWhole, SearchFormat:=False)
If Not LetterFound Is Nothing Then
With LetterFound
Select Case .Column
Case LookupRange.Column, _
LookupRange.Column + LookupRange.Columns.Count - 2
myNewLetter = .Offset(, 1).Text
Case Else
myNewLetter = .Offset(, -1).Text
End Select
End With
With Sheets(myLetter)
.Range("A1").Value = _
.Range("I10").Value + Sheets(myNewLetter).Range("A5").Value
End With
Else
MsgBox myLetter & " not found"
End If
End Sub

since last night i have made few changes and now the table only occurs in RANGE A20:B32 and the code works.


BUT


i need to add anothre condition to above highlighted statement as follows:

basically which ever row row D is found if the corresponding cell in Column M in the same row has value ''load''

then


Sheets(myLetter)
.Range("A1").Value = _
.Range("I10").Value + Sheets(myNewLetter).Range("A5").Value

else

Sheets(myLetter)
.Range("A1").Value = _
.Range("I10").Value - Sheets(myNewLetter).Range("A5").Value


(e.g. if D is Cell 'main'B20 and THE corresponding M cell 'main'M20 has value ''Load'' then add else substract.)




please help me out with this and i will be very geatefull to you have already been a life saver.

 

[eddy23]

help!!

 

[Peter_SSs]

Try this modification:

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> VariableSheetLookup()<br>    <SPAN style="color:#00007F">Dim</SPAN> LookupRange <SPAN style="color:#00007F">As</SPAN> Range, LetterFound <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> myLetter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, myNewLetter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> myMult <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> LookupRange = Sheets("main").Range("A20:J32")<br>    myLetter = "D"<br>    <SPAN style="color:#00007F">Set</SPAN> LetterFound = LookupRange.Find(What:=myLetter, LookIn:=xlValues, _<br>                        LookAt:=xlWhole, SearchFormat:=False)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> LetterFound <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> LetterFound<br>            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> .Column<br>                <SPAN style="color:#00007F">Case</SPAN> LookupRange.Column, _<br>                        LookupRange.Column + LookupRange.Columns.Count - 2<br>                    myNewLetter = .Offset(, 1).Text<br>                <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN><br>                    myNewLetter = .Offset(, -1).Text<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>            myMult = IIf(.Parent.Cells(.Row, "M").Value = "Load", 1, -1)<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> Sheets(myLetter)<br>            .Range("A1").Value = .Range("I10").Value _<br>                        + myMult * Sheets(myNewLetter).Range("A5").Value<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN><br>        MsgBox myLetter & " not found"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>