Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

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
0
bvanscoy678
Asked:
bvanscoy678
  • 4
  • 2
  • 2
1 Solution
 
NorieData ProcessorCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
NorieData ProcessorCommented:
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now