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

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
bvanscoy678Asked:
Who is Participating?
 
NorieVBA ExpertCommented:
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
 
FarWestCommented:
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
 
FarWestCommented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
bvanscoy678Author Commented:
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
 
NorieVBA ExpertCommented:
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
 
bvanscoy678Author Commented:
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
 
bvanscoy678Author Commented:
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
 
bvanscoy678Author Commented:
Thank you!
0
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.

All Courses

From novice to tech pro — start learning today.