A VBA script in excel to read row by row

Hi

With excel vba we are trying to read an excel worksheet (“sheet1”) row by row and access the specific cell contents (row/column) and write it to another worksheet (“sheet2”).  Please advice.
rayluvsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
Can you give more details?

For example which columns/rows do you want to look at on Sheet1 and what do you want to write to Sheet2?
0
Fabrice LambertFabrice LambertCommented:
Depend on the amount of data you want to manipulate, it might be better to lead everything in an array, that will be faster.
Dim wb As Excel.Workbook
Set wb = ThisWorkbook

Dim ws As Excel.Worksheet
Set ws = wb.Worksheets(1)

Dim rng As Excel.Range
Set rng = ws.Range("A1:L500")

    '// retrieve range data in a 2D array
Dim data() As Variant
data = rng.Value

Dim i As Long
Dim j As Long
For i = Lbound(data, 1) To Ubound(data, 1)
    For j = Lbound(data, 2) To Ubound(data, 2)
        '// Manipulate your data here
    Next
Next

    '// write data back to the worksheet
rng.Value = data

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
You might be able to do what you want with Advanced Filter.

This enables you to filter the source data on one or more columns and copy the filtered results to another sheet. The copied results can be all or just certain columns from the source and can be in a different sequence to the source.

A sample file showing the source data and the results would be useful.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

rayluvsAuthor Commented:
for example,
- sheet1 has 9 columns and and 5 rows (A1:I5).
- the first row of columns hold values to be used to compare as we read from row 2 to 5
- start read first of data row1, columnA thru row1, columnA 
- we hold the values of said row/columns to compare with everything else to be read
- start reading the rest...
  - read row2, columnA
  - compare the contents with "row1, columnA" (for example if "row2, columnA" contents = "row1, columnA")
  - if true, write "row1, columnA" to sheet2 "row1, columnA"
  - then read the next
  - read row2, columnB
  - compare the contents with "row1, columnB" (for example if "row2, columnB" contents = "row1, columnB")
  - if true, write "row2, columnB" to sheet2 "row2, columnB"
  - do these for all columns

Open in new window



Fabrice,

your code seems to hold the range of columns/rows and do the exat for/next of, what would be the statements to compare the row/columns and write them to sheet2?
0
rayluvsAuthor Commented:
maybe transferring the cells to an array and working from there?
0
rayluvsAuthor Commented:
Is there a more straight forward to copy the cells to a array table?

I have:
Dim vTable(700, 700)

For c = 1 To 5
 For r = 1 To 9
   vTable(c, r) = Cells(c, r)
 Next
Next

Open in new window

0
Fabrice LambertFabrice LambertCommented:

Fabrice,

your code seems to hold the range of columns/rows
Nope, it doesn't !
The array hold the range's values, not the range objects themeselves, do not confuse range objects with range values. And transfering the range values to the array is done with a single statement (line 12 of my previous answer).

Transfering an array back to the worksheet is done with a single statement as well (line 23 of my previous answer).
Only thing my code sample isn't showing, is that you must ensure that the targeted range is the same size (width and length) of the array, else results will be "surprising".
0
rayluvsAuthor Commented:
Understood... I have incorporated your code in a sample of data... I have colored the cells as to show the results... based on your code, what instructions I have to use to compare cells & transfer to the "output" sheet?

Based on what I am trying to accomplish:
- sheet1 has 9 columns and and 5 rows (A1:I5).
- the first row of columns hold values to be used to compare as we read from row 2 to 5
- start read first of data row1, columnA thru row1, columnA
- we hold the values of said row/columns to compare with everything else to be read
- start reading the rest...
    - read row2, columnA
    - compare the contents with "row1, columnA"
      (for example if "row2, columnA" contents = "row1, columnA")
    - if true, write "row1, columnA" to sheet2 "row1, columnA"
    - then read the next
    - read row2, columnB
    - compare the contents with "row1, columnB"
     (for example if "row2, columnB" contents = "row1, columnB")
    - if true, write "row2, columnB" to sheet2 "row2, columnB"
    - do these for all columns

eeVBA.xlsm
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.