Solved

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

Posted on 2012-03-24
8
298 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 34

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
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!

 

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 34

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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

707 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