Get right cell macro

[Jean D. Conte]

Hello everyone,

Im new to this forum and new to excel as well, im very bad at visual basic, but a good programmer. I Kept my whole week working on a macro to get some information from another excel file, thats my situation: I got lots of excel files with lots of information on different cells (i.e.: file1 has name on cell B10, but file2 has the name on cell B39), and i need to pull all the information and organize it on another file. I gotta get all the names, ages, and addressess of the people and put in a separate excel file (I guess a separate workbook). My problem is that i dont know exaclty how to get the information from different cells, since i dont know how to find, select and pull the information. The tables are organized this way:

| B10 | C10 |
|Name | John |
|Age | 39 |
|Address | 5th Avenue |

I can find and grab the information on the cell "Name", but i want the information on the right cell, not the one i found...

I tried this code, but it doesnt seem to work:

Sub ImportarDados()
Dim Pasta As String
Dim Arquivo As String
Dim i As Integer
Dim row As Integer

i = 3
row = 1
Pasta = "C:\CSV\"
Arquivo = Dir(Pasta & "*.xlsx")
Do
While Len(Range("A" & CStr(row)).Value) > 0

If Range("E" & CStr(row)).Value = "Name" Then

Workbooks.Open (Pasta & Arquivo)

ThisWorkbook.Sheets("Plan1").Cells(i, 2).Value = [E348].Value

ActiveWorkbook.Close False

End If

LSearchRow = LSearchRow + 1

Wend

i = i + 1
Arquivo = Dir
Loop While Arquivo <> ""
End Sub


Anyway, does anyone knows a way to do that? i already though on grep + awk on linux, but i wanted to try a macro before going to linux..

Thanks in advance.

Jean Douglas Conte

[GaidenFocus]

Do the offset method.

rangeIFound.offset(<rows>, <cols>).value = whatever