IF within an IF - VBA

pook_666

Board Regular
Joined
Aug 16, 2018
Messages
80
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!


VBA Code:
                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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,297
Office Version
  1. 2010
Platform
  1. Windows
try this
VBA Code:
                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

Well-known Member
Joined
Oct 27, 2005
Messages
20,116
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Have you tried
VBA Code:
 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

Well-known Member
Joined
Oct 27, 2005
Messages
20,116
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Aaww....Snap !!
 

pook_666

Board Regular
Joined
Aug 16, 2018
Messages
80

ADVERTISEMENT

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

Well-known Member
Joined
Oct 27, 2005
Messages
20,116
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Thats a LOT more than first requested !!
AND you are now including K3 in the equation ??
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,297
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

too slow MM :p
 

pook_666

Board Regular
Joined
Aug 16, 2018
Messages
80
@Michael M - apologies, I didn't think that having the text would make a difference.....clearly I was wrong! Apologies
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,116
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
I think you need provide a step by step list of what MIGHT be resident in the cells...ALL variations would be preferred.. (y)
Text and numbers are treated differently in a lot of cases !
 

Watch MrExcel Video

Forum statistics

Threads
1,129,382
Messages
5,635,937
Members
416,889
Latest member
dhegs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top