Hello People I have made following code.
The code loop through all cells in a master file column "H" for each value(example H cell contains "Hello") , open a new file check if the value is equal to any of the cells in column "H"; if the value is equal(this case "Hello") take the value same row but instead column "I", copy the number to the master file where it found "Hello", same row but column "I".
Now is my issue instead of replace current value in column "I" in the master file I want my macro to add to current value.
Could someone help me to change my code?
Thank you in advance
best regards
The code loop through all cells in a master file column "H" for each value(example H cell contains "Hello") , open a new file check if the value is equal to any of the cells in column "H"; if the value is equal(this case "Hello") take the value same row but instead column "I", copy the number to the master file where it found "Hello", same row but column "I".
Now is my issue instead of replace current value in column "I" in the master file I want my macro to add to current value.
Code:
Sub Use1Work()
Dim MastShRnG As Range
Dim SlavRng As Range
Dim SlaveWb As Workbook
Dim SlaveWs As Worksheet
Dim FileName As String
Dim FolderPath As String
Set MasWb = ActiveWorkbook
Set MasWbs = Worksheets(1)
x = MasWbs.Range("H" & Rows.Count).End(xlUp).Row
Set MastShRnG = MasWbs.Range("H1:H" & x)
FolderPath = "C:\DATA\"
File = Dir(FolderPath)
While (File <> "")
Set SlaveWb = Workbooks.Open(FolderPath & File)
Set SlaveWs = SlaveWb.Worksheets(1)
y = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
Set SlavRng = SlaveWs.Range("H1:H" & y)
For Each cell In SlavRng
If IsNumeric(cell.Offset(0, 1)) And cell.Value <> "" Then
res = Application.Match(cell, MastShRnG, 0)
If Not IsError(res) Then
MasWbs.Cells(res, "I") = cell.Offset(0, 1)
MasWbs.Cells(res, "I").Interior.ColorIndex = 3
Else
x = x + 1
MasWbs.Cells(x, "H") = cell
MasWbs.Cells(x, "I") = cell.Offset(0, 1)
MasWbs.Cells(x, "I").Interior.ColorIndex = 6
End If
End If
Next cell
' MsgBox MasWbs.Cells(x, "H").Value
Workbooks(File).Close SaveChanges:=False
File = Dir
Wend
End Sub
Could someone help me to change my code?
Thank you in advance
best regards