PDA

View Full Version : IF statement not working probably



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

Teeroy
01-03-2013, 03:59 AM
There is a lot of unnecessary selecting and activation but nothing I can see obviously wrong. Can you post a sample workbook with representative data that triggers the incorrect paste for troubleshooting?

BrianMH
01-03-2013, 06:56 AM
Sample workbooks would really help.

For further suggestions for ease of use and readability of code I suggest using option explicit and early binding. It really does make coding easier.

Instead of repeatedly referring to workbooks and worksheets by name I would set them early on and then use the objects. You get intellisence then and it is so much easier to code. I speak from experience, my early code looked much like yours.

I'll give you an example of what I mean.


Dim wbCWB as workbook
dim wsRaw as worksheet

set wbWBC = thisworkbook 'this is where the code resides and
'I would use this instead of activeworkbook unless
'you actually mean a different workbook than where the code is
Set wsRaw = wbCWB.Sheets("raw")


now if you type in the vba editor wsRaw and a fullstop you will get a list of actions available to it as well as wbCWB. It is just so much easier to use and read.

Also I noticed that you made another mistake I made early on.


Dim i, col As Integer



I bet you think those are both dimmed as an integer right? Nope, only col is an integer, i is a variant. If you want to do everything on one line you need to do

Dim i as integer, Dim col as integer


Hopefully this helps as these are things I really wish someone had explained to me when I first started out. Post us some examples of the files and we can try to help. Also if you explain what you are trying to achieve we can possibly provide a better solution.

PS the variable FilePathX is that a constant you defined elsewhere?