Excel pulling data from certain cells

I have a file that I only want to extract cells B9, B19, B29, etc etc etc in a pattern throughout the entire file. I would preferably like it to be extracted to a different excel file or someway so that I can do stuff with only those cells in another excel worksheet.

Potentially, I may have several excel files that I may need to do this sort of thing so if there were a way where I had the same format throughout a lot of files that I could always extract cells B9, B19, B29 that would be great. any help appreciated

I looking for syntax if possible

EDIT

Was thin开发者_StackOverflowking if I could somehow make an excel IF statement saying if Row has a 9 in it and the row is B then print it somewhere but I want it printed in a column

EDIT 2 I just want column B not A like I mentioned before. B9, B19,B29,B39 through the whole file

---

Just in case you want to do it with code:

Sub Test()
  'Assumes Sheet1 has your values and Sheet2 will be the data extracted from every row ending in 9
  Dim iCounter As Long
  Dim newSheetRow As Long
  Dim aValue As String
  Dim bValue As String

  newSheetRow = 1
  'Start and nine and increment by 10 till you reach end of sheet
  For iCounter = 9 To Sheet1.Rows.Count - 1 Step 10 'NOTE:  You may not want to do it by RowCount, but just showing you could
      aValue = Sheet1.Range("A" & iCounter)
      bValue = Sheet1.Range("B" & iCounter)

      Sheet2.Range("A" & newSheetRow).Value = "We were on row: " & iCounter
      Sheet2.Range("B" & newSheetRow).Value = aValue
      Sheet2.Range("C" & newSheetRow).Value = bValue
      newSheetRow = newSheetRow + 1
  Next iCounter

  MsgBox "Done"
End Sub

---

You could use the INDIRECT function. It takes a cell reference as a text string and returns the value in that cell. So instead of using

=data!a9

to get the value in sheet "data" in cell a9, you use

=indirect("data!a9")

You can also use r1c1 notation, like this:

=indirect("data!r9c1",false)

From there you can use the ROW and COLUMN functions to go in steps of 10:

=INDIRECT("data!r"&-1+10*ROW()&"c"&COLUMN(),FALSE)

If you put this formula in A1 of your output sheet and then copy and paste it down and across, it will give you the values in data!A9, data!A19, data!A29,... in cells A1, A2, A3... Depending on how you want your output arranged, you might have to modify the cell reference string.

---

Depending on how often you want to do this depends on how you need to do it, if it's a one of them some simple excel commands might help.

e.g.

In Cell C1 put the following:

=MOD(ROW(),10)

then replicate this down to the bottom of your data. the command will return the numbers 1 through to 0. You can then filter the data on column C where value is 9 then select the visible rows and copy the data to a new sheet.

ROW()  ' this returns the ROW number of cell the command is in.
MOD(number, divisor) ' this basically divides one number by the other and returns the remainder. so row 9 / 10 = 0 remainder of 9, row 19 / 10 = 1 remainder of 9.

Hope this helps.