IIJHFII February 2016

how to change output location for each loop and run multiple loops

I have code here which loops through a list of files; opening them, extracting data and moving it into the main workbook. What i am looking to do get it so the data for abel is in columns c and d but then put varo in f and g etc. the problem that i see is that the placement code is inside the loop so for each i it will just write over the previous line instead of being in a different column all together!

Sub Source_Data()

Dim r
Dim findValues() As String
Dim Wrbk As Workbook
Dim This As Workbook
Dim sht As Worksheet
Dim i
Dim tmp
Dim counter
Dim c As Range
Dim firstAddress
Dim rng As Range

ReDim findValues(1 To 3)
findValues(1) = "abel"
findValues(2) = "varo"
findValues(3) = "Tiger"

counter = 0

r = Range("A1").End(xlDown).Row
Set rng = Range(Cells(1, 1), Cells(r, 1))
Set This = ThisWorkbook

For Each tmp In rng
    Workbooks.Open tmp
    Set Wrbk = ActiveWorkbook
    Set sht = ActiveSheet
        For i = 1 To 3
            With sht.Range(Cells(1, 1), Range("A1").SpecialCells(xlCellTypeLastCell))
            Set c = .Find(findValues(i), LookIn:=xlValues)
                If Not c Is Nothing Then
                    firstAddress = c.Offset(0, 2).Value
                    Do
                        This.Activate
                        tmp.Offset(0, 2).Value = tmp.Value
                        tmp.Offset(0, 3).Value = firstAddress
                        Set c = .FindNext(c)
                        counter = counter + 1
                    Loop While Not c Is Nothing And c.Value = firstAddress
                End If
            End With
        Wrbk.Activate
        Next
    Wrbk.Close
Next tmp
End Sub

**EDIT:**I know it can be done by adding a multiplier of "i" to the offset value but this makes things bigger than they need to be if i wish to search for 50 keywords

Answers


Dan February 2016

You can just change these two lines:

tmp.Offset(0, 2).Value = tmp.Value
tmp.Offset(0, 3).Value = firstAddress

To this

tmp.Offset(0, 2 + (i-1)*2).Value = tmp.Value
tmp.Offset(0, 3 + (i-1)*2).Value = firstAddress


Elbert Villarreal February 2016

Here is my answer, hope to help you, and as always, if you need an improvement, just tell me.

Sub Source_Data()
Dim r
Dim findValues() As String
Dim Wrbk As Workbook
Dim This As Workbook
Dim sht As Worksheet
Dim i
Dim tmp
Dim counter
Dim c As Range
Dim firstAddress
Dim rng As Range
Dim ColNum 'the columns number var

ReDim findValues(1 To 3)
findValues(1) = "abel"
findValues(2) = "varo"
findValues(3) = "Tiger"

counter = 0

r = Range("A1").End(xlDown).Row
Set rng = Range(Cells(1, 1), Cells(r, 1))
Set This = ThisWorkbook

For Each tmp In rng
    Workbooks.Open tmp
    Set Wrbk = ActiveWorkbook
    Set sht = ActiveSheet
        For i = 1 To 3
            With sht.Range(Cells(1, 1), Range("A1").SpecialCells(xlCellTypeLastCell))
            Set c = .Find(findValues(i), LookIn:=xlValues)
                If Not c Is Nothing Then
                    firstAddress = c.Offset(0, 2).Value
                    Do
                        This.Activate
                        Select Case i 'Test var i (the value)
                            Case "abel" 'in case the value (that is a string) is equal to...
                                ColNum = 1 'set the var, with the number of the column you want
                            Case "varo" 'in case the value...
                                ColNum = 2 'Set the column...
                            Case "Tiger"
                                ColNum = 3
                            Case Else 'In case that the i var not match with anyvalue take this column number
                                ColNum = 20 'the garbage!
                        End Select

                        tmp.Offset(0, ColNum).Value = tmp.Value 'Put the value in the selected columns
                        tmp.Offset(0, ColNum + 1).Value = firstAddress 'and put the value to the next column of the
                                                                       'selected column
                        Set c = .FindNext(c)
              

Post Status

Asked in February 2016
Viewed 2,794 times
Voted 10
Answered 2 times

Search




Leave an answer