PDA

View Full Version : VBA Loop problem



elmnas
10-22-2016, 02:05 AM
Hello people,

I need a macro that makes following:

Looping through all used cells in Column H.


for each cell in column "H", open every file in a folder "C:\Data".

Loop each cell in the open file column "H" if the value is the same as the value from the first loop.
Copy the value same row from the open file but column "G" into the first file same row but column "G".
Close the file.


I have done a mistake somewhere in the logic I believe cause my result is skipping cells.

Could someone help me?




Sub test()
Dim wb As Workbook
Set Mwb = ActiveWorkbook
Dim folderPath As String
folderPath = "C:\DATA\"
Dim filename As String
filename = Dir(folderPath & "*.xlsx")
For i = 1 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
MioNr = Cells(i, "H").Value
Set wb = Workbooks.Open(folderPath & filename)
wb.Activate
For x = 1 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
CIonr = Cells(x, "H").Value
If CIonr = MioNr Then
Cells(x, "I").Copy
Mwb.Activate
Cells(i, "I").Activate
ActiveSheet.Paste
wb.Close
End If
Next x
Next i
End Sub



The problem is the macro is skipping the last iteration.

Thank you in advance

best regards

Daniel

mana
10-22-2016, 03:40 AM
Option Explicit

Sub test()
Dim Mws As Worksheet
Dim folderPath As String
Dim filename As String
Dim ws As Worksheet
Dim c As Range
Dim m

Set Mws = ActiveSheet

folderPath = "C:\DATA\"
filename = Dir(folderPath & "*.xlsx")

Do While filename <> ""
Set ws = Workbooks.Open(folderPath & filename).Sheets(1)
For Each c In Mws.Range("A1", Mws.UsedRange).Columns("H").Cells
m = Application.Match(c, ws.Columns("H"), 0)
If IsNumeric(m) Then
c.offfset(, 1).Value = ws.Cells(m, "I").Value
End If
Next
ws.Parent.Close False
filename = Dir()
Loop

End Sub

p45cal
10-22-2016, 08:29 AM
For goodness' sake elmnas, read http://www.excelguru.ca/content.php?184 !
You been made aware of the cross posting netiquette several times before.
All forums like this have the same guidelines/rules.

http://www.mrexcel.com/forum/excel-questions/971807-i-am-missing-something-my-code-i-am-not-sure-what.html