Help using variables in VBA code

[ssuresh98]

Hi Gurus,

Can you please help me with the following problem.


I am new to VBA and do not know how to use variables. I have a sheet (Rawdata) with fingerprints (generated by an assay) for each sample. Each sample has a parent ID linked to it and the fingerprint should match the parent fingerprint which is stored in another sheet (Master Database). I would like to calculate the correlation % of each sample with its parent. Right now I am doing this by using formulas. Can you please help me with a VBA code for this analysis?


The code needs to

1) Assign variables x and y

2) Find Sample Row # (x)

3) Find parent row# (y) for each sample on the Master Database

3) Find correlation for each sample by comparing the sample fingerprint (RawData Columns Dx:Hx) and Parent fingerprint (Master Database Columns Cy:Gy)


Eg. Formula = Correl(Dx:Hx, 'Master Database'!Cy:Gy)


Please find link to sample file below. I hope this is clear, if not please me let know. Thanks in advance for any help.


https://www.dropbox.com/s/sit0x7vg4dseukp/Correlation.xlsx

 

[Hui]

Ssuresh98


In Rawdata!C2: put

=CORREL(OFFSET($D$1:$H$1,MATCH(A2,$A$2:$A$16,0),0),OFFSET('Master Database'!$C$1:$G$1,MATCH(Rawdata!B2,'Master Database'!$B$2:$B$6,0),0))

Copy down


"Look Ma, No VBA"

 

[ssuresh98]

Hi Hui,

Thanks so much for the the formula, it works great. I have been using formulas (with helper columns) until now to do this analysis but not as efficient as yours. I would like to build up on my VBA skills and would like to accomplish this using variables but do not know how.


Can you please help with a sub?

TIA. I will be away for a couple of hours but will check back later.