VB code to find recurring values and return x columns and x rows from found

**TravisJB** · 03-04-2013, 11:57 AM

Hi all,

I pulled some VB code from a topic I found here to help me do this, it sort of works but needs a small tweak. I am just learning about VB so I'm struggling with the necessary tweaks. What i want to be able to do is return the value x number of rows and columns from the found occurrence value, rather than just x number of columns from found.

Any help is greatly appreciated!

Please Login or Register  to view this content.

**nemo66ro** · 03-04-2013, 04:47 PM

Hi,
Actually, do you want to find the number of rows and columns in a certain table ?

**TravisJB** · 03-04-2013, 04:52 PM

Originally Posted by nemo66ro

Hi,
Actually, do you want to find the number of rows and columns in a certain table ?

No I want to return the result/contents from the cell x number of rows and columns from the found occurrence.

**nemo66ro** · 03-04-2013, 05:11 PM

I think you want the Cell (r,c).Value from a table but what exactly is "occurence". As I see it is a long value not a function...

**TravisJB** · 03-05-2013, 09:24 AM

Here is a sample spreadsheet. I have clipped the results for two of the "parts/sets of data" I need to extract data from. This data is in sheet 1. The red cell is the value I want to look up. The yellow cell is the result that I want to return. The VB code that I pasted in the original post works perfectly for look ups that have the data I want returned in the same row as the look up value. I run into trouble when I want to look up the phase test (i.e. "15Hz I/O SRS Phase") and then return the three test frequencies listed below in different rows. If I could tweak the VB code to allow me to lookup the value "15Hz I/O SRS Phase" then return the result 6 rows down and 8 columns right I should be able to make this work. This is a manufacturing setting and there is thousands of entries per day, that's why I am trying to automate it and put the data into a usable format for analysis.
Sheet 2 is the format in which I'm trying to return the results for analysis.

Thanks for all the input it is much appreciated.

**nemo66ro** · 03-05-2013, 11:27 AM

Hi Travis,
Now it is clearer what you want. It seems you are interested in the results of 4 categories : "Static Deflection Process Data", "Calculated Stiffness", "15Hz I/O SRS Phase" and "13Hz O/O Drive Away Phase". Let's say we will capture the yellow results, how would you want to pe displayed inside the table from the sheet2 ? Are you interested in the each category frequency?

**TravisJB** · 03-05-2013, 01:59 PM

Hi nemo, Yes you are correct. If you look at the table on sheet 2 I have a column for each of the three frequencies that are in each of the two phase categories.

**nemo66ro** · 03-07-2013, 12:28 PM

Hi Travis,
I am sorry, but it seems that your needs are bigger than main purpose of this site ...You cannot fit the data from the first sheet with the table criteria from the second sheet using a simple function. Actually you need a small specific application which would take time (1-2 hours) ... If you will not get a solution until this weekend I try to give you a help.