Lookup using VBA

[Dowsey1977]

Hi,I have a spreadsheet that I am using that holds information completed by a user. Then, based on some of the content in this sheet, the information is copied to other sheets in another workbook.I've been tasked with trying to get this information copied across. I thought it would be a good idea, as there are several possible target sheets, to have the sheet pre-determined in the code. So, is there anyway of doing a lookup table in VBA to cover this?An example would be:In the user form, the 'Instrution Type' is 'Transfer', the 'Currency is 'GBP' and the 'Key' is '10', this would mean the information has to go to the sheet called '75 GBP (10)'. Assumin the instruction type and currency are the same, if the key became any of 45,50,51,94,96,99 the information would need to go to '44 GBP (50)'. Then, if the instruction type and/or currency changes a whole load of other possibilities arise.If you can help with how a 'lookup table' can be used in VBA, I could perhaps work on constructing it.Unfortunately i still can't post spreadsheets to this forum, and I have nothing I've started.Hope this is clear.

[mdmackillop]

Hi Dowsey,
Even a layout with sample text would be of assistance.
However,
If the key is the determinant, then you could use a Select Case method to specify the sheet name.
If more than one field specifies the sheet, these would need to concatenated into a string using the & operator and used as the Sheets index.
Regards
MD

[Dowsey1977]

Ok, thanks. I've just started looking at this and have got the below code so far...just as a test for my understanding.


Sub DataMove()

Select Case Sheets("Sheet1").Range("F4").Value
Case "0 - GBP"
Curr = 1
End Select
Select Case Sheets("Sheet1").Range("H4").Value
Case "10 - Dealing"
Key = 1
End Select

Select Case Curr & Key
Case 1 & 1
Bsheet = "75 GBP (10)"
End Select

Sheets(Bsheet).Select
End Sub


Three questions with this...
1. Is there anyway to make this better? (just using the above info for now)
2. On the first select, I am looking for the first case to be '=0 - GBP' and the 2nd case to be '<>0 -GBP'. As there are about 40 or so currencies, it is easier to say just equals or doesn't equal GBP as opposed to listing them all, is this possible?
3. How do I adapt it so that if one or both of the selects are blank or don't meet the criteria a message box is displays, just saying something like 'Error Found - Check Currency and Key"??

Thanks for your help on this.

[mdmackillop]

I think we're going to struggle here until we see a layout!
You are coding Case "0 - GBP" which is a string...or should this be a number? 0 - Range("GBP") ? I have no idea. What are Curr & Key? To be honest, I've no idea how to progress this on the information available.
Regards
MD