xlUser
12-27-2012, 07:40 AM
Hello I have the code below that looks up a value in row 2 on each column of the workbook CWB and finds it in another workbook (Technicals) and copies over and paste values. This works fine when it finds the value but when no match is found, ie. when rng is nothing I have asked it to do the following. It does this but it also pastes in the incorrect data, i.e follows through with the rest of the code.
If rng Is Nothing Then
Workbooks(CWB).Activate
Sheets("Errors").Range("a1000").End(xlUp).Offset(1, 0).Value = xvar
Sheets("Raw").Select
In every instance where this is happening, it seems to be pasting data it had copied for the last previous match it had found. For example, say i = 5 i.e column 5 of CWB and it finds a match in the technical workbook, then the macro correctly copies over the this data and pastes it into colulmn 5. But say for the next column (i=6), it doesn't find a match in the technicals workbook, for some reason it pastes in the data it had copied previously, i.e data that was menat only for column 5 data into column 6 even though the if criteria is not met.
I'm really confused, anyone know why teh IF statment is not working properly?
Sub Final_Testing()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim datestart, variable, CWB As String
Dim i, col As Integer
Dim xvar
Dim lastcell As Long
Dim sourcewb, datestart1, datestart2 As String
CWB = ActiveWorkbook.Name 'Sets Ref Name for this workbook
Worksheets("raw").Select 'Stores raw data before transformations
lastcell = Range("a5000").End(xlUp).Offset(-30, 0).Row
datestart = Range("a5000").End(xlUp).Offset(-30, 0).Value
lastvar = Range("B1").End(xlToRight).Column
Workbooks.Open Filename:=FilePathX & "Technicals.xls"
Workbooks("Technicals.xls").Activate
Sheets("Monthly").Select
With Sheets("Monthly").Columns(1)
Set rng1 = .Find(What:=datestart, _
After:=Sheets("Monthly").Range("a1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not rng1 Is Nothing Then
Application.Goto rng1, True
Else
MsgBox "Nothing found"
End If
End With
datestart4 = ActiveCell.Row 'define the range to paste results back in
Workbooks(CWB).Activate
For i = 4 To lastvar 'for each populated column
xvar = Cells(2, i).Value
sourcewb = "Technicals.xls"
datestart = datestart4
Workbooks(sourcewb).Activate
Sheets("Monthly").Select
With Sheets("Monthly").Rows("1:1")
Set rng = .Find(What:=xvar, _
After:=Sheets("Monthly").Range("a1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False)
End With
If rng Is Nothing Then
Workbooks(CWB).Activate
Sheets("Errors").Range("a1000").End(xlUp).Offset(1, 0).Value = xvar
Sheets("Raw").Select
'Exit For
Else
'only do if match found in terms of value i
Application.Goto rng, True
y = ActiveCell.Column 'define the range to paste results back in
Range(Cells(datestart, y), Cells(1500, y)).Copy
Workbooks(CWB).Activate
Sheets("raw").Select
Cells(lastcell, i).Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
Next i
End Sub
Thanks,
XLUSER
If rng Is Nothing Then
Workbooks(CWB).Activate
Sheets("Errors").Range("a1000").End(xlUp).Offset(1, 0).Value = xvar
Sheets("Raw").Select
In every instance where this is happening, it seems to be pasting data it had copied for the last previous match it had found. For example, say i = 5 i.e column 5 of CWB and it finds a match in the technical workbook, then the macro correctly copies over the this data and pastes it into colulmn 5. But say for the next column (i=6), it doesn't find a match in the technicals workbook, for some reason it pastes in the data it had copied previously, i.e data that was menat only for column 5 data into column 6 even though the if criteria is not met.
I'm really confused, anyone know why teh IF statment is not working properly?
Sub Final_Testing()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim datestart, variable, CWB As String
Dim i, col As Integer
Dim xvar
Dim lastcell As Long
Dim sourcewb, datestart1, datestart2 As String
CWB = ActiveWorkbook.Name 'Sets Ref Name for this workbook
Worksheets("raw").Select 'Stores raw data before transformations
lastcell = Range("a5000").End(xlUp).Offset(-30, 0).Row
datestart = Range("a5000").End(xlUp).Offset(-30, 0).Value
lastvar = Range("B1").End(xlToRight).Column
Workbooks.Open Filename:=FilePathX & "Technicals.xls"
Workbooks("Technicals.xls").Activate
Sheets("Monthly").Select
With Sheets("Monthly").Columns(1)
Set rng1 = .Find(What:=datestart, _
After:=Sheets("Monthly").Range("a1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not rng1 Is Nothing Then
Application.Goto rng1, True
Else
MsgBox "Nothing found"
End If
End With
datestart4 = ActiveCell.Row 'define the range to paste results back in
Workbooks(CWB).Activate
For i = 4 To lastvar 'for each populated column
xvar = Cells(2, i).Value
sourcewb = "Technicals.xls"
datestart = datestart4
Workbooks(sourcewb).Activate
Sheets("Monthly").Select
With Sheets("Monthly").Rows("1:1")
Set rng = .Find(What:=xvar, _
After:=Sheets("Monthly").Range("a1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False)
End With
If rng Is Nothing Then
Workbooks(CWB).Activate
Sheets("Errors").Range("a1000").End(xlUp).Offset(1, 0).Value = xvar
Sheets("Raw").Select
'Exit For
Else
'only do if match found in terms of value i
Application.Goto rng, True
y = ActiveCell.Column 'define the range to paste results back in
Range(Cells(datestart, y), Cells(1500, y)).Copy
Workbooks(CWB).Activate
Sheets("raw").Select
Cells(lastcell, i).Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
Next i
End Sub
Thanks,
XLUSER