I am very new to the VBA scene, but am reasonably competent with using Excel.
After a long time thinking about automating the analysis of data files from our equipment I have come a bit unstuck with with this particular function. I have got it to work perfrctly in the spreadsheet, but am having trouble translating it to VBA. Any help will be most appreciated!
The actual formula in Excel is:
=IF(0=GETPIVOTDATA("SAMPLE ID",$A$3,"PLATE NAME",A5,"ASSAY NAME","HCV0.6ml"),"0",
IF(48>=GETPIVOTDATA("SAMPLE ID",$A$3,"PLATE NAME",A5,"ASSAY NAME","HCV0.6ml"),
IF(24>=GETPIVOTDATA("SAMPLE ID",$A$3,"PLATE NAME",A5,"ASSAY NAME","HCV0.6ml"),
24-GETPIVOTDATA("SAMPLE ID",$A$3,"PLATE NAME",A5,"ASSAY NAME","HCV0.6ml"),
IF(48>=GETPIVOTDATA("SAMPLE ID",$A$3,"PLATE NAME",A5,"ASSAY NAME","HCV0.6ml"),
48-GETPIVOTDATA("SAMPLE ID",$A$3,"PLATE NAME",A5,"ASSAY NAME","HCV0.6ml"),
IF(72>=GETPIVOTDATA("SAMPLE ID",$A$3,"PLATE NAME",A5,"ASSAY NAME","HCV0.6ml"),
72-GETPIVOTDATA("SAMPLE ID",$A$3,"PLATE NAME",A5,"ASSAY NAME","HCV0.6ml"),
IF(96>=GETPIVOTDATA("SAMPLE ID",$A$3,"PLATE NAME",A5,"ASSAY NAME","HCV0.6ml"),
96-GETPIVOTDATA("SAMPLE ID",$A$3,"PLATE NAME",A5,"ASSAY NAME","HCV0.6ml"),
IF(0=GETPIVOTDATA("SAMPLE ID",$A$3,"PLATE NAME",A5,"ASSAY NAME","HCV0.6ml"),"0","Wrong")))))))
Basically this formula takes the value from a pivot table and then checks to see if it is 0, <=24, <=48, <=72, <=96 and then return a the difference from the whole values. i.e. value from pivot table = 16, this values falls in the <=24 and therefore calculates that the value is 8.
Does anyone have any idea how to replicate this in Excel VBA?
Thanks for you help, from a very stumped newb.
Bookmarks