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

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

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

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

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