Solved

Create a loop to look at a row, then return a value

Posted on 2012-03-24
8
277 Views
Last Modified: 2012-06-22
I asked this question yesterday and was given a terrific, but terrifying array formula as a solution. Of course it worked perfect, but I really have no idea how it worked. Since I am trying to learn basic VBA, I wanted to take another stab at the problem using a loop.

I have a range called "Engines". I want to take the first value (F3), then search through my range "zones"  to see if it shows up in the first row (in this case, row 3). If it does show up in the row, then I want to copy the value in the A column, same row, then paste it into (G3). Then go to the next row in the range and search again. If is shows, copy it to H3, if not then do nothing. Once I have been through the entire "zone" range, then go to the next value in the engine range (this case, F4).

I have tried to show what this would look like in column G:BD for making sense of the project. What it will be doing is list each engine company and then to the right will list in the row, what zones they respond to.

I hope this makes sense. I think I would build a loop with an if statement, searching for the value and looping through to the next row until it has been through the range. Then it would start over again for the next engine until it has searched for all the engines and found what zones they respond.

Thanks for any help. I am more interested in how to do this, than the actual data. I could just enter this manually, but it seems to be a good project to learn.
Loop-attempt.xls
0
Comment
Question by:bvanscoy678
  • 4
  • 2
  • 2
8 Comments
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 37760945
Try this, hope it makes some sort of sense.
Dim rngZone As Range
Dim rngEngine As Range
Dim rngDst As Range
Dim cl As Range
Dim rw As Range

    Set rngZone = Range("zone")
 
    ' set a reference to first 'engine'   
    Set rngEngine = Range("F3")

    ' set a reference to where values from column A will be copied to
    Set rngDst = rngEngine.Offset(, 1)
 
    ' loop through 'engine'  range
    While rngEngine.Value <> ""
    
         ' loop through rows in 'zone' range
        For Each rw In Range("zone").Rows
            
            ' loop through each cell in row       
            For Each cl In rw.Cells
                
                ' if cell is equal to value in 'engine' range, copy value from column A
                If cl.Value = rngEngine.Value Then
                    rngDst.Value = rw.Offset(, -1).Resize(1, 1).Value
                     ' move a column to the right
                    Set rngDst = rngDst.Offset(, 1)
                End If

            Next cl
        Next rw

        ' move down to next engine
        Set rngEngine = rngEngine.Offset(1)

        ' reset destination to column to the right of engine
        Set rngDst = rngEngine.Offset(, 1)
        
    Wend

Open in new window

0
 
LVL 12

Expert Comment

by:FarWest
ID: 37760949
because I could not run the code (you did not post sample of original files), and test any modification on it here is my Idea

the "New Merged Workbook" sheet will have two columns
A: Engines
B:Areas

when you read you source files,

1- check if the engine in B3is in A // (use range.find)
2- if found concatenate B value in the same row with  C1 value //use cells(inrow,2).value & "," & C1
3- if not found add it // cells(your_newrow_counter,1) = B3
4- repeat 1-3 for C3,D3,G3

after completing the loop you can run cells().TextToColumns if you want to convert b value to separate cells










For Each FileItem In fld.Files
    If InStr(1, sExt, UCase(Mid(FileItem.Name, InStrRev(FileItem.Name, ".", -1) + 1))) > 0 Then
        Set wb = Workbooks.Open(FileItem)
        ThisWorkbook.Activate
        For Each sht In wb.Sheets
            r = wsTo.Cells(Rows.Count, 1).End(xlUp).Row + 1
           
 '            wsTo.Cells(r, 1) = sht.Range("C1")  ' no need for this

            wsTo.Cells(r, 2) = sht.Range("B3")
            wsTo.Cells(r, 3) = sht.Range("C3")
            wsTo.Cells(r, 4) = sht.Range("D3")
            wsTo.Cells(r, 5) = sht.Range("G3")
        Next sht
        wb.Close False
    End If
Next FileItem
0
 
LVL 12

Expert Comment

by:FarWest
ID: 37760955
sorry ignore the code after empty lines (cut & paste mistake)

and consider this as the last line of my comment

//
after completing the loop you can run cells().TextToColumns if you want to convert b value to separate cells
//
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

Author Comment

by:bvanscoy678
ID: 37760997
Imnorie,

I do follow what you are going. I placed your code and got an error "Compile Error: Invalid outside procedure on:
   Set rngZone = Range("zone")

Fryezz, Let me take a look at what you did. I did post an example with my original question. Do you see it?

Thanks
0
 
LVL 33

Expert Comment

by:Norie
ID: 37761007
Did you add Sub <SubName> at the start and End Sub at the end?

For example:
Sub FindEngines()

Dim rngZone As Range
Dim rngEngine As Range
Dim rngDst As Range
Dim cl As Range
Dim rw As Range

    Set rngZone = Range("zone")
 
    ' set a reference to first 'engine'   
    Set rngEngine = Range("F3")

    ' set a reference to where values from column A will be copied to
    Set rngDst = rngEngine.Offset(, 1)
 
    ' loop through 'engine'  range
    While rngEngine.Value <> ""
    
         ' loop through rows in 'zone' range
        For Each rw In Range("zone").Rows
            
            ' loop through each cell in row       
            For Each cl In rw.Cells
                
                ' if cell is equal to value in 'engine' range, copy value from column A
                If cl.Value = rngEngine.Value Then
                    rngDst.Value = rw.Offset(, -1).Resize(1, 1).Value
                     ' move a column to the right
                    Set rngDst = rngDst.Offset(, 1)
                End If

            Next cl
        Next rw

        ' move down to next engine
        Set rngEngine = rngEngine.Offset(1)

        ' reset destination to column to the right of engine
        Set rngDst = rngEngine.Offset(, 1)
        
    Wend

End Sub

Open in new window


PS Are there 2 workbooks?
0
 

Author Comment

by:bvanscoy678
ID: 37761011
Imnorie,

Okay, I seen my problem. I did not name the macro. Once I named it, it worked perfectly!

Freyezz,

I placed your code and ran it and came up with an error:

Run Time Error: 424, object required
For Each FileItem In fld.Files

I placed both in my example and uploaded again. Thanks for your patience, but I want to view both ways of doing it for learning.
Loop-attempt-ver-2.xls
0
 

Author Comment

by:bvanscoy678
ID: 37761016
There are not two workbooks. I think I had a module from a previous lesson that I should have deleted. It was a merge workbook macro that was used to compile this list.

Yes, once I named it, it worked perfect. I should have caught that right away.

If ok, I want to see what the other example is for learning. Not sure of the etiquette for this.


Thanks for the lesson. I will step through the code and back track to see what you did.

Brent
0
 

Author Closing Comment

by:bvanscoy678
ID: 37761384
Thank you!
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question