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]

Try this modification:


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

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
myMult = IIf(.Parent.Cells(.Row, "M").Value = "Load", 1, -1)
End With
With Sheets(myLetter)
.Range("A1").Value = .Range("I10").Value _
+ myMult * Sheets(myNewLetter).Range("A5").Value
End With
Else
MsgBox myLetter & " not found"
End If
End Sub

THANK YOU!!!!! soooooo much..

just a last one rather than ending it with msgbox not found i want Sheets(myLetter).Range("A1") to take value from cell I16 of the myLetter sheet.

can u please help me with this modification as well..

kinda regards

 

[Peter_SSs]

just a last one rather than ending it with msgbox not found i want Sheets(myLetter).Range("A1") to take value from cell I16 of the myLetter sheet.

You've just about written it yourself and probably should have just given it a try.

Try this:
Sheets(myLetter).Range("A1").Value = Sheets(myLetter).Range("I16").Value