Hi,
I would like assistance to write a macro which creates a page center to read
from column data. I would like for example the text string to read "Wasuto
updates for Supervisor ?? data date ??. Wasuto would be placed if column 4
has "WAS" in the 12 character text (example:45WAS51302U35) or if "KEM" is
there instead I'd like the word "Kematsa" instead. Also I'd like the
Supervisor's name if column 5 lists his name greater than 50% of the time.
There could be up to 25 different Supervisors names. Lastly I'd like the
current date behind the word date in the string. I'm using excel 2003. There
could be up to 5000 rows of data. The firts row is the header row.
Any help would be greatly appreciated.
--
By persisting in your path, though you forfeit the little, you gain the
great.

Take a look here to find out how to use the Workbook_BeforePrint event.

http://www.mvps.org/dmcritchie/excel/pathname.htm
--
HTH,
Barb Reinhardt



"DavidH56" wrote:

> Hi,
> I would like assistance to write a macro which creates a page center to read
> from column data. I would like for example the text string to read "Wasuto
> updates for Supervisor ?? data date ??. Wasuto would be placed if column 4
> has "WAS" in the 12 character text (example:45WAS51302U35) or if "KEM" is
> there instead I'd like the word "Kematsa" instead. Also I'd like the
> Supervisor's name if column 5 lists his name greater than 50% of the time.
> There could be up to 25 different Supervisors names. Lastly I'd like the
> current date behind the word date in the string. I'm using excel 2003. There
> could be up to 5000 rows of data. The firts row is the header row.
> Any help would be greatly appreciated.
> --
> By persisting in your path, though you forfeit the little, you gain the
> great.
>

Thanks for your response Barb

There is a lot of good info I get here. I can see how to add the file path
and insert the date in my page header. I still would like to know which code
to use, maybe select case to insert the first word "Wasuto" in the page
center header if column 4 has "WAS" in it. Also pulling Supervisor's names
from column 5 to insert in page header also. Thanks again for your help.
--
By persisting in your path, though you forfeit the little, you gain the
great.



"Barb Reinhardt" wrote:

> Take a look here to find out how to use the Workbook_BeforePrint event.
>
> http://www.mvps.org/dmcritchie/excel/pathname.htm
> --
> HTH,
> Barb Reinhardt
>
>
>
> "DavidH56" wrote:
>
> > Hi,
> > I would like assistance to write a macro which creates a page center to read
> > from column data. I would like for example the text string to read "Wasuto
> > updates for Supervisor ?? data date ??. Wasuto would be placed if column 4
> > has "WAS" in the 12 character text (example:45WAS51302U35) or if "KEM" is
> > there instead I'd like the word "Kematsa" instead. Also I'd like the
> > Supervisor's name if column 5 lists his name greater than 50% of the time.
> > There could be up to 25 different Supervisors names. Lastly I'd like the
> > current date behind the word date in the string. I'm using excel 2003. There
> > could be up to 5000 rows of data. The firts row is the header row.
> > Any help would be greatly appreciated.
> > --
> > By persisting in your path, though you forfeit the little, you gain the
> > great.
> >

Thanks Barb,

This is what I have so far:

Sub FixUpdateHeader()
Dim Sentences
Dim i As Long
Dim iWordPos As Integer

Sentences = Range("D1", Range("D65536").End(xlUp))
lRow = 0
For i = Range("D65536").End(xlUp).Row To 1 Step -1
iWordPos = InStr(LCase(Sentences(i, 1)), LCase("WAS"))
If iWordPos > 0 Then
With ActiveSheet
.PageSetup.CenterHeader = "Wasuto Updates"
End With
End If
Next i
For i = Range("D65536").End(xlUp).Row To 1 Step -1
iWordPos = InStr(LCase(Sentences(i, 1)), LCase("KRS"))
If iWordPos > 0 Then
With ActiveSheet
.PageSetup.CenterHeader = "Kematsa Updates"
End With
End If
Next i
End Sub


--
By persisting in your path, though you forfeit the little, you gain the
great.



"Barb Reinhardt" wrote:

> Take a look here to find out how to use the Workbook_BeforePrint event.
>
> http://www.mvps.org/dmcritchie/excel/pathname.htm
> --
> HTH,
> Barb Reinhardt
>
>
>
> "DavidH56" wrote:
>
> > Hi,
> > I would like assistance to write a macro which creates a page center to read
> > from column data. I would like for example the text string to read "Wasuto
> > updates for Supervisor ?? data date ??. Wasuto would be placed if column 4
> > has "WAS" in the 12 character text (example:45WAS51302U35) or if "KEM" is
> > there instead I'd like the word "Kematsa" instead. Also I'd like the
> > Supervisor's name if column 5 lists his name greater than 50% of the time.
> > There could be up to 25 different Supervisors names. Lastly I'd like the
> > current date behind the word date in the string. I'm using excel 2003. There
> > could be up to 5000 rows of data. The firts row is the header row.
> > Any help would be greatly appreciated.
> > --
> > By persisting in your path, though you forfeit the little, you gain the
> > great.
> >

I think I'd replace some of the For i = ... next i with this

Call DefineHeader("KRS","Kematsa Updates")

Sub DefineHeader(myAcronym as string, myUpdates as string)
For i = Range("D65536").End(xlUp).Row To 1 Step -1
iWordPos = InStr(LCase(Sentences(i, 1)), LCase(myacronym))
If iWordPos > 0 Then
With ActiveSheet
.PageSetup.CenterHeader = myupdates
End With
End If
Next i
end sub

--
HTH,
Barb Reinhardt



"DavidH56" wrote:

> Thanks Barb,
>
> This is what I have so far:
>
> Sub FixUpdateHeader()
> Dim Sentences
> Dim i As Long
> Dim iWordPos As Integer
>
> Sentences = Range("D1", Range("D65536").End(xlUp))
> lRow = 0
> For i = Range("D65536").End(xlUp).Row To 1 Step -1
> iWordPos = InStr(LCase(Sentences(i, 1)), LCase("WAS"))
> If iWordPos > 0 Then
> With ActiveSheet
> .PageSetup.CenterHeader = "Wasuto Updates"
> End With
> End If
> Next i
> For i = Range("D65536").End(xlUp).Row To 1 Step -1
> iWordPos = InStr(LCase(Sentences(i, 1)), LCase("KRS"))
> If iWordPos > 0 Then
> With ActiveSheet
> .PageSetup.CenterHeader = "Kematsa Updates"
> End With
> End If
> Next i
> End Sub
>
>
> --
> By persisting in your path, though you forfeit the little, you gain the
> great.
>
>
>
> "Barb Reinhardt" wrote:
>
> > Take a look here to find out how to use the Workbook_BeforePrint event.
> >
> > http://www.mvps.org/dmcritchie/excel/pathname.htm
> > --
> > HTH,
> > Barb Reinhardt
> >
> >
> >
> > "DavidH56" wrote:
> >
> > > Hi,
> > > I would like assistance to write a macro which creates a page center to read
> > > from column data. I would like for example the text string to read "Wasuto
> > > updates for Supervisor ?? data date ??. Wasuto would be placed if column 4
> > > has "WAS" in the 12 character text (example:45WAS51302U35) or if "KEM" is
> > > there instead I'd like the word "Kematsa" instead. Also I'd like the
> > > Supervisor's name if column 5 lists his name greater than 50% of the time.
> > > There could be up to 25 different Supervisors names. Lastly I'd like the
> > > current date behind the word date in the string. I'm using excel 2003. There
> > > could be up to 5000 rows of data. The firts row is the header row.
> > > Any help would be greatly appreciated.
> > > --
> > > By persisting in your path, though you forfeit the little, you gain the
> > > great.
> > >

Barb,
I get a type mismatch on this line of code.
iWordPos = InStr(LCase(Sentences(i, 1)), LCase(myAcronym))

Thanks
--
By persisting in your path, though you forfeit the little, you gain the
great.



"Barb Reinhardt" wrote:

> I think I'd replace some of the For i = ... next i with this
>
> Call DefineHeader("KRS","Kematsa Updates")
>
> Sub DefineHeader(myAcronym as string, myUpdates as string)
> For i = Range("D65536").End(xlUp).Row To 1 Step -1
> iWordPos = InStr(LCase(Sentences(i, 1)), LCase(myacronym))
> If iWordPos > 0 Then
> With ActiveSheet
> .PageSetup.CenterHeader = myupdates
> End With
> End If
> Next i
> end sub
>
> --
> HTH,
> Barb Reinhardt
>
>
>
> "DavidH56" wrote:
>
> > Thanks Barb,
> >
> > This is what I have so far:
> >
> > Sub FixUpdateHeader()
> > Dim Sentences
> > Dim i As Long
> > Dim iWordPos As Integer
> >
> > Sentences = Range("D1", Range("D65536").End(xlUp))
> > lRow = 0
> > For i = Range("D65536").End(xlUp).Row To 1 Step -1
> > iWordPos = InStr(LCase(Sentences(i, 1)), LCase("WAS"))
> > If iWordPos > 0 Then
> > With ActiveSheet
> > .PageSetup.CenterHeader = "Wasuto Updates"
> > End With
> > End If
> > Next i
> > For i = Range("D65536").End(xlUp).Row To 1 Step -1
> > iWordPos = InStr(LCase(Sentences(i, 1)), LCase("KRS"))
> > If iWordPos > 0 Then
> > With ActiveSheet
> > .PageSetup.CenterHeader = "Kematsa Updates"
> > End With
> > End If
> > Next i
> > End Sub
> >
> >
> > --
> > By persisting in your path, though you forfeit the little, you gain the
> > great.
> >
> >
> >
> > "Barb Reinhardt" wrote:
> >
> > > Take a look here to find out how to use the Workbook_BeforePrint event.
> > >
> > > http://www.mvps.org/dmcritchie/excel/pathname.htm
> > > --
> > > HTH,
> > > Barb Reinhardt
> > >
> > >
> > >
> > > "DavidH56" wrote:
> > >
> > > > Hi,
> > > > I would like assistance to write a macro which creates a page center to read
> > > > from column data. I would like for example the text string to read "Wasuto
> > > > updates for Supervisor ?? data date ??. Wasuto would be placed if column 4
> > > > has "WAS" in the 12 character text (example:45WAS51302U35) or if "KEM" is
> > > > there instead I'd like the word "Kematsa" instead. Also I'd like the
> > > > Supervisor's name if column 5 lists his name greater than 50% of the time.
> > > > There could be up to 25 different Supervisors names. Lastly I'd like the
> > > > current date behind the word date in the string. I'm using excel 2003. There
> > > > could be up to 5000 rows of data. The firts row is the header row.
> > > > Any help would be greatly appreciated.
> > > > --
> > > > By persisting in your path, though you forfeit the little, you gain the
> > > > great.
> > > >

It appears that I forgot to dimension Sentences and define it at the
beginning of the sub.
--
HTH,
Barb Reinhardt



"DavidH56" wrote:

> Barb,
> I get a type mismatch on this line of code.
> iWordPos = InStr(LCase(Sentences(i, 1)), LCase(myAcronym))
>
> Thanks
> --
> By persisting in your path, though you forfeit the little, you gain the
> great.
>
>
>
> "Barb Reinhardt" wrote:
>
> > I think I'd replace some of the For i = ... next i with this
> >
> > Call DefineHeader("KRS","Kematsa Updates")
> >
> > Sub DefineHeader(myAcronym as string, myUpdates as string)
> > For i = Range("D65536").End(xlUp).Row To 1 Step -1
> > iWordPos = InStr(LCase(Sentences(i, 1)), LCase(myacronym))
> > If iWordPos > 0 Then
> > With ActiveSheet
> > .PageSetup.CenterHeader = myupdates
> > End With
> > End If
> > Next i
> > end sub
> >
> > --
> > HTH,
> > Barb Reinhardt
> >
> >
> >
> > "DavidH56" wrote:
> >
> > > Thanks Barb,
> > >
> > > This is what I have so far:
> > >
> > > Sub FixUpdateHeader()
> > > Dim Sentences
> > > Dim i As Long
> > > Dim iWordPos As Integer
> > >
> > > Sentences = Range("D1", Range("D65536").End(xlUp))
> > > lRow = 0
> > > For i = Range("D65536").End(xlUp).Row To 1 Step -1
> > > iWordPos = InStr(LCase(Sentences(i, 1)), LCase("WAS"))
> > > If iWordPos > 0 Then
> > > With ActiveSheet
> > > .PageSetup.CenterHeader = "Wasuto Updates"
> > > End With
> > > End If
> > > Next i
> > > For i = Range("D65536").End(xlUp).Row To 1 Step -1
> > > iWordPos = InStr(LCase(Sentences(i, 1)), LCase("KRS"))
> > > If iWordPos > 0 Then
> > > With ActiveSheet
> > > .PageSetup.CenterHeader = "Kematsa Updates"
> > > End With
> > > End If
> > > Next i
> > > End Sub
> > >
> > >
> > > --
> > > By persisting in your path, though you forfeit the little, you gain the
> > > great.
> > >
> > >
> > >
> > > "Barb Reinhardt" wrote:
> > >
> > > > Take a look here to find out how to use the Workbook_BeforePrint event.
> > > >
> > > > http://www.mvps.org/dmcritchie/excel/pathname.htm
> > > > --
> > > > HTH,
> > > > Barb Reinhardt
> > > >
> > > >
> > > >
> > > > "DavidH56" wrote:
> > > >
> > > > > Hi,
> > > > > I would like assistance to write a macro which creates a page center to read
> > > > > from column data. I would like for example the text string to read "Wasuto
> > > > > updates for Supervisor ?? data date ??. Wasuto would be placed if column 4
> > > > > has "WAS" in the 12 character text (example:45WAS51302U35) or if "KEM" is
> > > > > there instead I'd like the word "Kematsa" instead. Also I'd like the
> > > > > Supervisor's name if column 5 lists his name greater than 50% of the time.
> > > > > There could be up to 25 different Supervisors names. Lastly I'd like the
> > > > > current date behind the word date in the string. I'm using excel 2003. There
> > > > > could be up to 5000 rows of data. The firts row is the header row.
> > > > > Any help would be greatly appreciated.
> > > > > --
> > > > > By persisting in your path, though you forfeit the little, you gain the
> > > > > great.
> > > > >

Thanks again Barb,

I've got it now.
--
By persisting in your path, though you forfeit the little, you gain the
great.



"Barb Reinhardt" wrote:

> It appears that I forgot to dimension Sentences and define it at the
> beginning of the sub.
> --
> HTH,
> Barb Reinhardt
>
>
>
> "DavidH56" wrote:
>
> > Barb,
> > I get a type mismatch on this line of code.
> > iWordPos = InStr(LCase(Sentences(i, 1)), LCase(myAcronym))
> >
> > Thanks
> > --
> > By persisting in your path, though you forfeit the little, you gain the
> > great.
> >
> >
> >
> > "Barb Reinhardt" wrote:
> >
> > > I think I'd replace some of the For i = ... next i with this
> > >
> > > Call DefineHeader("KRS","Kematsa Updates")
> > >
> > > Sub DefineHeader(myAcronym as string, myUpdates as string)
> > > For i = Range("D65536").End(xlUp).Row To 1 Step -1
> > > iWordPos = InStr(LCase(Sentences(i, 1)), LCase(myacronym))
> > > If iWordPos > 0 Then
> > > With ActiveSheet
> > > .PageSetup.CenterHeader = myupdates
> > > End With
> > > End If
> > > Next i
> > > end sub
> > >
> > > --
> > > HTH,
> > > Barb Reinhardt
> > >
> > >
> > >
> > > "DavidH56" wrote:
> > >
> > > > Thanks Barb,
> > > >
> > > > This is what I have so far:
> > > >
> > > > Sub FixUpdateHeader()
> > > > Dim Sentences
> > > > Dim i As Long
> > > > Dim iWordPos As Integer
> > > >
> > > > Sentences = Range("D1", Range("D65536").End(xlUp))
> > > > lRow = 0
> > > > For i = Range("D65536").End(xlUp).Row To 1 Step -1
> > > > iWordPos = InStr(LCase(Sentences(i, 1)), LCase("WAS"))
> > > > If iWordPos > 0 Then
> > > > With ActiveSheet
> > > > .PageSetup.CenterHeader = "Wasuto Updates"
> > > > End With
> > > > End If
> > > > Next i
> > > > For i = Range("D65536").End(xlUp).Row To 1 Step -1
> > > > iWordPos = InStr(LCase(Sentences(i, 1)), LCase("KRS"))
> > > > If iWordPos > 0 Then
> > > > With ActiveSheet
> > > > .PageSetup.CenterHeader = "Kematsa Updates"
> > > > End With
> > > > End If
> > > > Next i
> > > > End Sub
> > > >
> > > >
> > > > --
> > > > By persisting in your path, though you forfeit the little, you gain the
> > > > great.
> > > >
> > > >
> > > >
> > > > "Barb Reinhardt" wrote:
> > > >
> > > > > Take a look here to find out how to use the Workbook_BeforePrint event.
> > > > >
> > > > > http://www.mvps.org/dmcritchie/excel/pathname.htm
> > > > > --
> > > > > HTH,
> > > > > Barb Reinhardt
> > > > >
> > > > >
> > > > >
> > > > > "DavidH56" wrote:
> > > > >
> > > > > > Hi,
> > > > > > I would like assistance to write a macro which creates a page center to read
> > > > > > from column data. I would like for example the text string to read "Wasuto
> > > > > > updates for Supervisor ?? data date ??. Wasuto would be placed if column 4
> > > > > > has "WAS" in the 12 character text (example:45WAS51302U35) or if "KEM" is
> > > > > > there instead I'd like the word "Kematsa" instead. Also I'd like the
> > > > > > Supervisor's name if column 5 lists his name greater than 50% of the time.
> > > > > > There could be up to 25 different Supervisors names. Lastly I'd like the
> > > > > > current date behind the word date in the string. I'm using excel 2003. There
> > > > > > could be up to 5000 rows of data. The firts row is the header row.
> > > > > > Any help would be greatly appreciated.
> > > > > > --
> > > > > > By persisting in your path, though you forfeit the little, you gain the
> > > > > > great.
> > > > > >