Solved

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

Posted on 2012-03-24
8
290 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

733 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