Finding empty cells
in [Programming]
|
From: leimst on 18 Jul 2008 18:36 I have a workbook with 10 worksheets (tabs) each with a Header Row with the month of July dates across the top and a Header Column with other information running down the side. What macro would search all 10 sheets for the last date (column) that might have an entry from any of the rows, search back from there through July 1st and identify all blank cells or cells with a red "fill" and consolidate all of them on a new worksheet? Thanks for any help, Brian
From: Joel on 19 Jul 2008 02:57 Sub combinesheets() First = True For Each Sht In ThisWorkbook.Sheets If First = True Then 'create new summary worksheet Set SummarySht = Worksheets.Add(after:=Sheets(Sheets.Count)) 'copy header Row to new sheet Sht.Rows(1).Copy Destination = SummarySht.Rows(1) NewRow = 2 First = False End If LastCol = Sht.Cells(1, Columns.Count).End(xlToLeft).Column LastRow = Sht.Range("A" & Rows.Count).End(xlUp).Row For RowCount = 2 To LastRow AddedRow = False For ColCount = 2 To LastCol If Sht.Cells(RowCount, ColCount) = "" Or _ Sht.Cells(RowCount, ColCount). _ Interior.ColorIndex <> xlNone Then If AddedRow = False Then 'add header column SummarySht.Range("A" & NewRow).Value = _ Sht.Range("A" & RowCount).Value AddedRow = True End If If Sht.Cells(RowCount, ColCount) = "" Then 'Add X for empty cells SummarySht.Cells(NewRow, ColCount) = "X" Else NewSht.Cells(RowCount, ColCount).Copy _ Destination:=SummarySht.Cells(NewRow, ColCount) End If End If Next ColCount If AddedRow = True Then AddedRow = AddedRow + 1 End If Next RowCount Next Sht End Sub "leimst" wrote: > I have a workbook with 10 worksheets (tabs) each with a Header Row with the > month of July dates across the top and a Header Column with other > information running down the side. What macro would search all 10 sheets > for the last date (column) that might have an entry from any of the rows, > search back from there through July 1st and identify all blank cells or > cells with a red "fill" and consolidate all of them on a new worksheet? > > Thanks for any help, > > Brian > > >
From: leimst on 20 Jul 2008 09:58 Joel, Thanks for the response. This is my first attempt at using a macro though! I took your script and copied and pasted it in as a module. I then wnet to "Tools", "Macros", saw the "Combinesheets" macro and hit "Run". It came back though and said "Compile Error: Variable Not Defined" and the "First = True" statement at the top was grayed out. Am I doing something wrong in loading or execution? Thanks, Brian "Joel" <Joel(a)discussions.microsoft.com> wrote in message news:7C0589DC-3073-4D51-A6BB-EE5C115CA98C(a)microsoft.com... > Sub combinesheets() > > First = True > > For Each Sht In ThisWorkbook.Sheets > If First = True Then > 'create new summary worksheet > Set SummarySht = Worksheets.Add(after:=Sheets(Sheets.Count)) > 'copy header Row to new sheet > Sht.Rows(1).Copy Destination = SummarySht.Rows(1) > NewRow = 2 > First = False > End If > LastCol = Sht.Cells(1, Columns.Count).End(xlToLeft).Column > LastRow = Sht.Range("A" & Rows.Count).End(xlUp).Row > For RowCount = 2 To LastRow > AddedRow = False > For ColCount = 2 To LastCol > If Sht.Cells(RowCount, ColCount) = "" Or _ > Sht.Cells(RowCount, ColCount). _ > Interior.ColorIndex <> xlNone Then > > If AddedRow = False Then > 'add header column > SummarySht.Range("A" & NewRow).Value = _ > Sht.Range("A" & RowCount).Value > AddedRow = True > End If > If Sht.Cells(RowCount, ColCount) = "" Then > 'Add X for empty cells > SummarySht.Cells(NewRow, ColCount) = "X" > Else > NewSht.Cells(RowCount, ColCount).Copy _ > Destination:=SummarySht.Cells(NewRow, ColCount) > End If > End If > Next ColCount > If AddedRow = True Then > AddedRow = AddedRow + 1 > End If > Next RowCount > Next Sht > > End Sub > > > "leimst" wrote: > >> I have a workbook with 10 worksheets (tabs) each with a Header Row with >> the >> month of July dates across the top and a Header Column with other >> information running down the side. What macro would search all 10 sheets >> for the last date (column) that might have an entry from any of the rows, >> search back from there through July 1st and identify all blank cells or >> cells with a red "fill" and consolidate all of them on a new worksheet? >> >> Thanks for any help, >> >> Brian >> >> >>
From: Joel on 20 Jul 2008 12:53 Depending on the way your workbook is set up you are required to define variables. My workbook doesn't. I added the definitaions below Sub combinesheets() Dim First As Boolean Dim Sht As Sheets Dim SummarySht As Sheets Dim NewRow As Long Dim LastCol As Long Dim LastRow As Long Dim AddedRow As Long First = True For Each Sht In ThisWorkbook.Sheets If First = True Then 'create new summary worksheet Set SummarySht = Worksheets.Add(after:=Sheets(Sheets.Count)) 'copy header Row to new sheet Sht.Rows(1).Copy Destination = SummarySht.Rows(1) NewRow = 2 First = False End If LastCol = Sht.Cells(1, Columns.Count).End(xlToLeft).Column LastRow = Sht.Range("A" & Rows.Count).End(xlUp).Row For RowCount = 2 To LastRow AddedRow = False For ColCount = 2 To LastCol If Sht.Cells(RowCount, ColCount) = "" Or _ Sht.Cells(RowCount, ColCount). _ Interior.ColorIndex <> xlNone Then If AddedRow = False Then 'add header column SummarySht.Range("A" & NewRow).Value = _ Sht.Range("A" & RowCount).Value AddedRow = True End If If Sht.Cells(RowCount, ColCount) = "" Then 'Add X for empty cells SummarySht.Cells(NewRow, ColCount) = "X" Else NewSht.Cells(RowCount, ColCount).Copy _ Destination:=SummarySht.Cells(NewRow, ColCount) End If End If Next ColCount If AddedRow = True Then AddedRow = AddedRow + 1 End If Next RowCount Next Sht End Sub "leimst" wrote: > Joel, > > Thanks for the response. This is my first attempt at using a macro though! > I took your script and copied and pasted it in as a module. I then wnet to > "Tools", "Macros", saw the "Combinesheets" macro and hit "Run". It came > back though and said "Compile Error: Variable Not Defined" and the "First = > True" statement at the top was grayed out. Am I doing something wrong in > loading or execution? > > Thanks, > > Brian > > "Joel" <Joel(a)discussions.microsoft.com> wrote in message > news:7C0589DC-3073-4D51-A6BB-EE5C115CA98C(a)microsoft.com... > > Sub combinesheets() > > > > First = True > > > > For Each Sht In ThisWorkbook.Sheets > > If First = True Then > > 'create new summary worksheet > > Set SummarySht = Worksheets.Add(after:=Sheets(Sheets.Count)) > > 'copy header Row to new sheet > > Sht.Rows(1).Copy Destination = SummarySht.Rows(1) > > NewRow = 2 > > First = False > > End If > > LastCol = Sht.Cells(1, Columns.Count).End(xlToLeft).Column > > LastRow = Sht.Range("A" & Rows.Count).End(xlUp).Row > > For RowCount = 2 To LastRow > > AddedRow = False > > For ColCount = 2 To LastCol > > If Sht.Cells(RowCount, ColCount) = "" Or _ > > Sht.Cells(RowCount, ColCount). _ > > Interior.ColorIndex <> xlNone Then > > > > If AddedRow = False Then > > 'add header column > > SummarySht.Range("A" & NewRow).Value = _ > > Sht.Range("A" & RowCount).Value > > AddedRow = True > > End If > > If Sht.Cells(RowCount, ColCount) = "" Then > > 'Add X for empty cells > > SummarySht.Cells(NewRow, ColCount) = "X" > > Else > > NewSht.Cells(RowCount, ColCount).Copy _ > > Destination:=SummarySht.Cells(NewRow, ColCount) > > End If > > End If > > Next ColCount > > If AddedRow = True Then > > AddedRow = AddedRow + 1 > > End If > > Next RowCount > > Next Sht > > > > End Sub > > > > > > "leimst" wrote: > > > >> I have a workbook with 10 worksheets (tabs) each with a Header Row with > >> the > >> month of July dates across the top and a Header Column with other > >> information running down the side. What macro would search all 10 sheets > >> for the last date (column) that might have an entry from any of the rows, > >> search back from there through July 1st and identify all blank cells or > >> cells with a red "fill" and consolidate all of them on a new worksheet? > >> > >> Thanks for any help, > >> > >> Brian > >> > >> > >> > > >
From: leimst on 23 Jul 2008 22:24
I've tried this again but am still getting a Compile Error. Any suggestions on maybe some settings I should change? Thanks, Brian "Joel" <Joel(a)discussions.microsoft.com> wrote in message news:4156F487-2BB7-42D9-9A8E-92F34EA724AF(a)microsoft.com... > Depending on the way your workbook is set up you are required to define > variables. My workbook doesn't. I added the definitaions below > > > Sub combinesheets() > Dim First As Boolean > Dim Sht As Sheets > Dim SummarySht As Sheets > Dim NewRow As Long > Dim LastCol As Long > Dim LastRow As Long > Dim AddedRow As Long > > > First = True > > For Each Sht In ThisWorkbook.Sheets > If First = True Then > 'create new summary worksheet > Set SummarySht = Worksheets.Add(after:=Sheets(Sheets.Count)) > 'copy header Row to new sheet > Sht.Rows(1).Copy Destination = SummarySht.Rows(1) > NewRow = 2 > First = False > End If > LastCol = Sht.Cells(1, Columns.Count).End(xlToLeft).Column > LastRow = Sht.Range("A" & Rows.Count).End(xlUp).Row > For RowCount = 2 To LastRow > AddedRow = False > For ColCount = 2 To LastCol > If Sht.Cells(RowCount, ColCount) = "" Or _ > Sht.Cells(RowCount, ColCount). _ > Interior.ColorIndex <> xlNone Then > > If AddedRow = False Then > 'add header column > SummarySht.Range("A" & NewRow).Value = _ > Sht.Range("A" & RowCount).Value > AddedRow = True > End If > If Sht.Cells(RowCount, ColCount) = "" Then > 'Add X for empty cells > SummarySht.Cells(NewRow, ColCount) = "X" > Else > NewSht.Cells(RowCount, ColCount).Copy _ > Destination:=SummarySht.Cells(NewRow, ColCount) > End If > End If > Next ColCount > If AddedRow = True Then > AddedRow = AddedRow + 1 > End If > Next RowCount > Next Sht > > End Sub > > > "leimst" wrote: > >> Joel, >> >> Thanks for the response. This is my first attempt at using a macro >> though! >> I took your script and copied and pasted it in as a module. I then wnet >> to >> "Tools", "Macros", saw the "Combinesheets" macro and hit "Run". It came >> back though and said "Compile Error: Variable Not Defined" and the "First >> = >> True" statement at the top was grayed out. Am I doing something wrong in >> loading or execution? >> >> Thanks, >> >> Brian >> >> "Joel" <Joel(a)discussions.microsoft.com> wrote in message >> news:7C0589DC-3073-4D51-A6BB-EE5C115CA98C(a)microsoft.com... >> > Sub combinesheets() >> > >> > First = True >> > >> > For Each Sht In ThisWorkbook.Sheets >> > If First = True Then >> > 'create new summary worksheet >> > Set SummarySht = Worksheets.Add(after:=Sheets(Sheets.Count)) >> > 'copy header Row to new sheet >> > Sht.Rows(1).Copy Destination = SummarySht.Rows(1) >> > NewRow = 2 >> > First = False >> > End If >> > LastCol = Sht.Cells(1, Columns.Count).End(xlToLeft).Column >> > LastRow = Sht.Range("A" & Rows.Count).End(xlUp).Row >> > For RowCount = 2 To LastRow >> > AddedRow = False >> > For ColCount = 2 To LastCol >> > If Sht.Cells(RowCount, ColCount) = "" Or _ >> > Sht.Cells(RowCount, ColCount). _ >> > Interior.ColorIndex <> xlNone Then >> > >> > If AddedRow = False Then >> > 'add header column >> > SummarySht.Range("A" & NewRow).Value = _ >> > Sht.Range("A" & RowCount).Value >> > AddedRow = True >> > End If >> > If Sht.Cells(RowCount, ColCount) = "" Then >> > 'Add X for empty cells >> > SummarySht.Cells(NewRow, ColCount) = "X" >> > Else >> > NewSht.Cells(RowCount, ColCount).Copy _ >> > Destination:=SummarySht.Cells(NewRow, ColCount) >> > End If >> > End If >> > Next ColCount >> > If AddedRow = True Then >> > AddedRow = AddedRow + 1 >> > End If >> > Next RowCount >> > Next Sht >> > >> > End Sub >> > >> > >> > "leimst" wrote: >> > >> >> I have a workbook with 10 worksheets (tabs) each with a Header Row >> >> with >> >> the >> >> month of July dates across the top and a Header Column with other >> >> information running down the side. What macro would search all 10 >> >> sheets >> >> for the last date (column) that might have an entry from any of the >> >> rows, >> >> search back from there through July 1st and identify all blank cells >> >> or >> >> cells with a red "fill" and consolidate all of them on a new >> >> worksheet? >> >> >> >> Thanks for any help, >> >> >> >> Brian >> >> >> >> >> >> >> >> >> |