Extracting values from A1, A11, A21

**allstarbiotech** · 03-29-2011, 08:41 AM

Hi,

I am trying to write a vba code to find values from A1, A11, A21 and so on. It would be great if someone could help me with that.

The variables that I know are:

Range (A1:A21000)

and primarily looking to find A1, A1 + 10.

I am attempting to use VBA after a long time and it would be useful to get some help.

Thanks,

**romperstomper** · 03-29-2011, 08:44 AM

For example:

Please Login or Register  to view this content.

**6StringJazzer** · 03-29-2011, 08:44 AM

Welcome to the Forum!

What do you mean by "find values"? You mean search for values, or just iterate through all those cells?

This will iterate through all those cells:

Please Login or Register  to view this content.

Edit: I like romperstomper's a little better

**allstarbiotech** · 03-29-2011, 08:53 AM

Thanks for the replies so far.

I need to print the integer values in a new column.

**romperstomper** · 03-29-2011, 08:59 AM

Where? Can you not just use formulas instead of VBA?

**allstarbiotech** · 03-29-2011, 09:05 AM

I can't find any formula that would find and 'print' integer values from rows A1, A11 and so on.

By printing I mean, to get the values from these particular cells to another column.

I am basically looking to reduce a data set from ~20000 numbers to about 2000 and thus want to select every 10th cell in the column. Hope this makes it better.

**romperstomper** · 03-29-2011, 09:09 AM

In b1:
=INDEX(A:A,(row()-1)*10+1)
and copy down to B2000.

**allstarbiotech** · 03-29-2011, 09:47 AM

Thanks. this made it much simpler. Can you tell what does the row()-1, the [reference] parentheses are for?

**romperstomper** · 03-29-2011, 09:57 AM

ROW() returns the row number of the cell containing the formula.
Since we want every tenth row starting at row 1, we subtract 1 from the row, multiply by 10 and add 1, so this part:
(row()-1)*10+1
in row 1 evaluates to: (1 - 1)*10 + 1 = 0*10 + 1 = 1
in row 2 evaluates to: (2 - 1)*10 + 1 = 1*10 + 1 = 11
and so on.

**allstarbiotech** · 03-29-2011, 08:53 PM

Thanks man, appreciate all the help. It worked out fine. I am looking forward to writing a simple code to test this using vba as well.

Cheers

Extracting values from A1, A11, A21

LinkBack

Thread Tools

Rate This Thread

Display

Extracting values from A1, A11, A21

Re: Extracting values from A1, A11, A21

Re: Extracting values from A1, A11, A21

Re: Extracting values from A1, A11, A21

Re: Extracting values from A1, A11, A21

Re: Extracting values from A1, A11, A21

Re: Extracting values from A1, A11, A21

Re: Extracting values from A1, A11, A21

Re: Extracting values from A1, A11, A21

Re: Extracting values from A1, A11, A21

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions