IF within an IF - VBA

[pook_666]

Hi magicians,

I'm having issues with an IF VBA script.

I have sheets in a workbook that either contain a value in M3 or L3 - it can never be both.

If a sheet has a value in M3 or L3 of above 0.01, I want to copy & paste particular data.

I've got the below code that isn't working for me where if the value in M3 is less than 0.01, it still copies & pastes the data. However, if I remove the second IF looking at L3 and re-run it, it correctly doesn't pick up less than 0.01 when looking at sheets where it has figures in M3.

Not sure that you can have an IF within an IF like the below or am missing something obvious......any help greatly appreciated!

                If ws.Range("M3").Value >= 0.01 Then
                    lrow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
                    'copy & paste data
                        With wsDestination
                            ws.Range("A7:A" & lrow).Copy Destination:=.Range("B" & .Rows.Count).End(xlUp).Offset(1)
                            ws.Range("B7:B" & lrow).Copy Destination:=.Range("C" & .Rows.Count).End(xlUp).Offset(1)
                            ws.Range("L7:L" & lrow).Copy Destination:=.Range("E" & .Rows.Count).End(xlUp).Offset(1)
                        End With
                Else
                    If ws.Range("L3").Value >= 0.01 Then
                        lrow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
                        'copy & paste data
                            With wsDestination
                                ws.Range("A7:A" & lrow).Copy Destination:=.Range("B" & .Rows.Count).End(xlUp).Offset(1)
                                ws.Range("B7:B" & lrow).Copy Destination:=.Range("C" & .Rows.Count).End(xlUp).Offset(1)
                                ws.Range("L7:L" & lrow).Copy Destination:=.Range("E" & .Rows.Count).End(xlUp).Offset(1)
                            End With
                    End If
                End If

 

[diddi]

try this

                If ws.Range("M3").Value >= 0.01 Or ws.Range("L3").Value >= 0.01 Then
                    lrow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
                    'copy & paste data
                        With wsDestination
                            ws.Range("A7:A" & lrow).Copy Destination:=.Range("B" & .Rows.Count).End(xlUp).Offset(1)
                            ws.Range("B7:B" & lrow).Copy Destination:=.Range("C" & .Rows.Count).End(xlUp).Offset(1)
                            ws.Range("L7:L" & lrow).Copy Destination:=.Range("E" & .Rows.Count).End(xlUp).Offset(1)
                        End With
                End If

 

[Michael M]

Have you tried

 If ws.Range("L3").Value >= 0.01 OR ws.Range("M3").Value >= 0.01Then
                    lrow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
                    'copy & paste data
                        With wsDestination
                            ws.Range("A7:A" & lrow).Copy Destination:=.Range("B" & .Rows.Count).End(xlUp).Offset(1)
                            ws.Range("B7:B" & lrow).Copy Destination:=.Range("C" & .Rows.Count).End(xlUp).Offset(1)
                            ws.Range("L7:L" & lrow).Copy Destination:=.Range("E" & .Rows.Count).End(xlUp).Offset(1)
                        End With
  End If

 

[Michael M]

Aaww....Snap !!

 

[pook_666]

Amazing you both came back with the same answers!!

However, unfortunately it isn't working

I probably should have given a bit more info (sorry!) - on the sheets where the code is looking, there is either text in L3 and figures in M3 or text in K3 and figures in L3.

So running your codes (and mine), if the sheet has text in L3 and figures in M3 and the figure is a negative (for example) it still does the copy & paste.

I presume it's to do with there being text in L3?

 

[Michael M]

Thats a LOT more than first requested !!
AND you are now including K3 in the equation ??

 

[diddi]

too slow MM

 

[pook_666]

@Michael M - apologies, I didn't think that having the text would make a difference.....clearly I was wrong! Apologies

 

[Michael M]

I think you need provide a step by step list of what MIGHT be resident in the cells...ALL variations would be preferred..
Text and numbers are treated differently in a lot of cases !

 

[pook_666]

It's only these two scenarios that occur. Sorry for the confusion!