VBA Question

[MRoss]

Hi guys,

So I've been working on a simple linear interpolation code. Basically it takes a matrix of timedata and interpolates from the lowest time to the highest. Everything runs smoothly. What I would like is for my VBA code to spit out a matrix with the original values HIGHLIGHTED.

Keep in mind what my code does:

1. It accepts the input data
2. It creates a variant called MAT and fills it with dates and Xs for data
3. It interpolates and changes the Xs for the interpolated values
4. It sets the function (LinearInterpolation() ) to MAT

Now I would just use:CellName.Interior.ColorIndex = 4, but I know that will not work because at that point I am working on the virtual matrix MAT rather then the actual workbook. Does anybody have a solution for this? I know I can scan through the cells, but I want to keep this as dynamic as possible. In other words I want the function to do this without knowing where the interpolation is taking place.


This is what I have:
!

...and this is what I want:

Thanks!

 

[alain]

use conditional formatting. One possibility is to iterate over the result Range and add conditional formatting to the matching cells.

 

[MRoss]

I admit you had me Googling for some time there... but alas I still cannot figure it out

Here is a snipet of code:

Function LinearInterpolation(TimeData As Range, m As Integer, T As Integer) As Variant
 
...BODY OF CODE MANIPULATES A MATRIX CALLED MAT...
 
'FINAL OUTPUT TO WORKSHEET
 
LinearInterpolation = Mat
 
End Function

Arrrgh I don't know how to post VBA code here properly, sorry.

 

[suprabhatv]

the highlighting issue comes once you paste MAT on the worksheet.

you may try this For loop that goes from the first cell to the last cell of MAT and highlights if it matches the time given.

For i = 1 to ubound(MAT,1)
chk = worksheetfunction.match(MAT(i,1), cells( 3,1).resize(4,1),0)
if isnumeric(chk) then
cells(i+1,6).resize(1,2).cells.interior.colorindex = 4
end if
next i

i have written this looking at your attachment. You can easily make the match function dynamic.

 

[MRoss]

the highlighting issue comes once you paste MAT on the worksheet.

This is not a sub its a function. Meaning, I highlight the cells beforehand and do a CTRL+SHFT+ALT to compute the function as a matrix. Also, MAT never makes it into the workbook. MAT is copied into LinearInterpolation which is the function called. Regardless, you seem to know more than me so I will play around with your suggestion tomorrow.

Thanks!

 

[Marcos Paterson]

hey MRoss. this is what you need...
select from F2:G(line your table ends)
if you are using excel 2007 on the home ribbon click on Conditional Formating > New Rule > Under select a rule type choose "Use a formula..." > Under "Edit the rule..." type in a formula like: "vlookup($F2;$A$3:$A$6;1;false) > Edit the format and voila...

now you can add a dynamic edge to your spreadsheet, like adding a dynamic name to the region "F2:G(line your table ends)" and doing the same in the region "$A$3:$A$6" of the lookup formula.

Do this by going to the ribbon Formula > Name Manager > New > Add a name and under "refers to" use the formula: =offset(F2;0;0;counta(F:F)-1;1) - in other words =offset("where to start";"how many cells down/up +/-";"how many cells right/left +/-";"how many rows to include";"how many columns to include"). Go in the Conditional Formating window, go in the menu "Manage rules", and change where it says "applies to" to the name you specified like: =example

Add another name, =offset(A3;0;0;counta(A:A)-2;1) - On the vlookup formula above, instead of using "$A$3:$A$6", just use the name you created last.

That should be it. Sorry I made it a little confusing.

 

[MRoss]

hey MRoss. this is what you need...
select from F2:G(line your table ends)

Thanks Gekko.

I know I can use vlookup but that would require me to know what cells I am using. What I would like is that no matter where on the spreadsheet I decide to interpolate, the old values will be highlighted on the fly. The only way to do this would be to input some sort of code before the VBA function ends.

I am beginning to think that this is impossible.

 

[MRoss]

Allow me to reword my question:

Since a VBA function unlike a sub allows you to select the cells you want, place the function in the bar on top, and then press CTRL+SHFT+ENTR to populate, is there a way for VBA without actually exiting the code to figure out which cells were populated?


For example, would I be able to run a sub right before exiting the function which would determine which cells have been populated?


Thanks!

 

[Tom Maloney]

I think the VLOOKUP suggestion is your best bet. Leave VBA to number crunching, and prettify your worksheet manually.

 

[MRoss]

Thanks guys,

I redid this as a sub and got some pretty nifty results.

1. Input is dynamic (you can put a 2,000 line TS in if you want)
2. Output is dynamic
3. Graph is dynamic
4. Cells dynamically get formatted (boxed in)
5. Similarities get highlighted
6. There is a "Clear" Sub which clears the exact amount of cells as was interpolated

Since I'm no VBA Pro I'm pretty excited about this. Thanks to all for your advice!