How to add IF ELSE into a nested set of For & With loops?

LlebKcir

Board Regular
Joined
Oct 8, 2018
Messages
219
I have a functioning set of code to copy data from one worksheet to another in a nested format to both figure out how many data sets to copy as well as transpose to the other end.

I am wanting to add some qualifying code that checks the value of the cell to be copied before it is moved. If it contains the boolean FALSE statement copy said data, else iterate the loops and continue to the next cell.

The working code is as follows:

VBA Code:
          ' ================================================================
          ' Code to copy cells from Overview sheet to new group Worksheet.
          ' ================================================================

        j = 2       ' Reset j back to 2.
                    ' Sets starting point for copy/paste of data to correct Cell.
                    Set rFoundCell = Worksheets("overview").Range("A1")

               ' Starts for loop to search for 'start' vaule on the Overview Worksheet in the cells column.
               For lCount = 1 To Application.WorksheetFunction.CountIf(Worksheets("overview").Range("a1:a200"), start)
               ' Finds the 'start' value on the Overview Worksheet in the cells column.
               Set rFoundCell = Worksheets("overview").Columns(1).Find(what:=start, after:=rFoundCell, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)

                     ' Start of the copy/paste data from Overview to new Group # Worksheet in a transpose form.
                     With rFoundCell
                         ' The offset will move the source from column A to column AE, column count -1 = number below.  A = 0, B = 1, C = 2, etc...
                         Worksheets("overview").Range(rFoundCell.Address).Offset(, 30).Copy
                         Worksheets("group " & start).Activate
                         '                           .Cells([row], [column]).   .Cells(34, j) moves down to Row 52.
                         Worksheets("group " & start).Cells(34, j).PasteSpecial xlPasteValues
                         j = j + 1
     
                     End With

               Next lCount ' End loop for copy cells.

In the With loop is were I would like to add the IF ELSE code to check if the cell contains FALSE.

Code currently does the following:

Input:
TRUE
FALSE
FALSE
FALSE
output:
TRUEFALSEFALSETRUE
desired output:
FALSEFALSE

Thank you in advance
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

LlebKcir

Board Regular
Joined
Oct 8, 2018
Messages
219
I tried the following and Excel blows up stated end with without with, but its in a with loop

VBA Code:
                     With rFoundCell
                         ' The offset will move the source from column A to column AJ, column count -1 = number below.  A = 0, B = 1, C = 2, etc...
                         ' testing IF/ELSE statement to exit WITH loop and itereate when boolean = true <28 Jan. 2022: RLB>
                        If Worksheets("overview").Range(rFoundCell.Address).Offset(, 35).Value = True Then End With
                        Else

Why is Excel not accepting that the IF/ELSE is inside the WITH Loop?
 

Forum statistics

Threads
1,176,293
Messages
5,902,348
Members
434,966
Latest member
_p_j_b_

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