PDA

View Full Version : formula using VBA



cmccabe1
05-21-2014, 02:39 PM
Currently I use a few formulas and manually calculate data. I would like to automate that process and need some expert help in doing so.

The formulas are: Formula 1
=IF(COUNTIFS( A$2:A$6713,F2,B$2:B$6713,"<="&G2,C$2:C$6713, ">="&G2),VLOOKUP(G2,$B$2:$D$6713,3,1),"No")
A B C D E


EpilepsyHomopolymer

chr

start

end

description



2

11

791787

791791

4C



3

11

791787

791792

5C



4

11

791817

791821

4G



…6713


I have put the workbook here as it is too large to attach.
https://onedrive.live.com/redir?resid=5D59E448AFE6FFD!144&authkey=!AP0nkuaNe-I2alM&ithint=file%2c.xlsm

Examples of each would be:
Formula 1 (EpilepsyHomopolymer): If Chr and Start in annovar match chr in panel and is between start and end in panel, Then whatever is in the description columns is copied to Homopolymer in annovar. I hope this makes sense and helps. Thank you very much.

So in Annovar chr is 1 and start is 40556922. This coordinate does not not fall in between the start and end of chr 1 in panel, so the Homopolymer cell in annovar would be "No".

westconn1
05-23-2014, 02:46 AM
not available to download

cmccabe1
05-23-2014, 06:40 AM
sorry, here it is:

https://onedrive.live.com/redir?resid=5D59E448AFE6FFD!147&authkey=!AGOg2zPzyS-5AEg&ithint=file%2c.xlsm

cmccabe1
05-24-2014, 10:33 AM
I have a cleaner copy on box.net. Thanks.

https://app.box.com/s/s3yflwuefuem0jm4b90w

mancubus
05-25-2014, 12:00 PM
why not post your workbook here?

cmccabe1
05-25-2014, 12:14 PM
The workbook exceeds the attachment limit. Thanks.