Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to Search, Insert Column and Sum
I have an Excel app that generates the dates for the month using NETWORKDAYS
across the top row. What I'm struggling with now is how to loop through all the columns searching for "FRIDAY" and inserting a blank column to sum the weeks numbers. Any help would be greatly appreciated. Thx |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to Search, Insert Column and Sum
Do the cells in row 1 contain dates--or the actual text Friday. I don't mean
how the values are formatted. I mean what's really in the cell? I'm guessing that it's a date: Option Explicit Sub testme() Dim iCol As Long Dim wks As Worksheet Set wks = Worksheets("Sheet1") With wks For iCol = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column If Weekday(.Cells(1, iCol).Value) = vbFriday Then .Columns(iCol + 1).Insert End If Next iCol End With End Sub Richard wrote: I have an Excel app that generates the dates for the month using NETWORKDAYS across the top row. What I'm struggling with now is how to loop through all the columns searching for "FRIDAY" and inserting a blank column to sum the weeks numbers. Any help would be greatly appreciated. Thx -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to Search, Insert Column and Sum
Hi Richard,
a small suggestion since you are adding columns -- use what Dave gave you but loop from the last to the first... For iCol = .Cells(1, .Columns.Count).End(xlToLeft).Column to 1 Step -1 Warm Regards, Crystal * (: have an awesome day :) * MVP Access Remote Programming and Training strive4peace2006 at yahoo.com * Dave Peterson wrote: Do the cells in row 1 contain dates--or the actual text Friday. I don't mean how the values are formatted. I mean what's really in the cell? I'm guessing that it's a date: Option Explicit Sub testme() Dim iCol As Long Dim wks As Worksheet Set wks = Worksheets("Sheet1") With wks For iCol = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column If Weekday(.Cells(1, iCol).Value) = vbFriday Then .Columns(iCol + 1).Insert End If Next iCol End With End Sub Richard wrote: I have an Excel app that generates the dates for the month using NETWORKDAYS across the top row. What I'm struggling with now is how to loop through all the columns searching for "FRIDAY" and inserting a blank column to sum the weeks numbers. Any help would be greatly appreciated. Thx |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to Search, Insert Column and Sum
Thanks for the correction.
I intended to do just that, but screwed up! strive4peace wrote: Hi Richard, a small suggestion since you are adding columns -- use what Dave gave you but loop from the last to the first... For iCol = .Cells(1, .Columns.Count).End(xlToLeft).Column to 1 Step -1 Warm Regards, Crystal * (: have an awesome day :) * MVP Access Remote Programming and Training strive4peace2006 at yahoo.com * Dave Peterson wrote: Do the cells in row 1 contain dates--or the actual text Friday. I don't mean how the values are formatted. I mean what's really in the cell? I'm guessing that it's a date: Option Explicit Sub testme() Dim iCol As Long Dim wks As Worksheet Set wks = Worksheets("Sheet1") With wks For iCol = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column If Weekday(.Cells(1, iCol).Value) = vbFriday Then .Columns(iCol + 1).Insert End If Next iCol End With End Sub Richard wrote: I have an Excel app that generates the dates for the month using NETWORKDAYS across the top row. What I'm struggling with now is how to loop through all the columns searching for "FRIDAY" and inserting a blank column to sum the weeks numbers. Any help would be greatly appreciated. Thx -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to Search, Insert Column and Sum
you're welcome, Dave ;)
Warm Regards, Crystal * (: have an awesome day :) * MVP Access Remote Programming and Training strive4peace2006 at yahoo.com * Dave Peterson wrote: Thanks for the correction. I intended to do just that, but screwed up! strive4peace wrote: Hi Richard, a small suggestion since you are adding columns -- use what Dave gave you but loop from the last to the first... For iCol = .Cells(1, .Columns.Count).End(xlToLeft).Column to 1 Step -1 Warm Regards, Crystal * (: have an awesome day :) * MVP Access Remote Programming and Training strive4peace2006 at yahoo.com * Dave Peterson wrote: Do the cells in row 1 contain dates--or the actual text Friday. I don't mean how the values are formatted. I mean what's really in the cell? I'm guessing that it's a date: Option Explicit Sub testme() Dim iCol As Long Dim wks As Worksheet Set wks = Worksheets("Sheet1") With wks For iCol = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column If Weekday(.Cells(1, iCol).Value) = vbFriday Then .Columns(iCol + 1).Insert End If Next iCol End With End Sub Richard wrote: I have an Excel app that generates the dates for the month using NETWORKDAYS across the top row. What I'm struggling with now is how to loop through all the columns searching for "FRIDAY" and inserting a blank column to sum the weeks numbers. Any help would be greatly appreciated. Thx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search for a column based on the column header and then past data from it to another column in another workbook | Excel Programming | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Programming | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
search for worksheet, insert new if doesn't exist | Excel Programming | |||
Cell Text Search & Insert Value in Next Column | Excel Programming |