I am trying to fill the column starting at R2 going down based on the data
contained in cell E2 going down. I want something like an If statement ot
fill the cells going down based the R column starting at R2. If the value in
E2 says infant then I would need R2 filled with Daycare otherwise the cell
would need to be filled with preschool.

Hi Bill

in cell R2
=IF(E2="infant","Daycare","preschool")
this can then be filled down column R

however, please note, if there is nothing in E2 you will still get
"preschool" - so the following formula might be better
=IF(E2="","",IF(E2="infant","Daycare","preschool") )

Regards
JuileD


"Bill" wrote in message
...
I am trying to fill the column starting at R2 going down based on the data
contained in cell E2 going down. I want something like an If statement ot
fill the cells going down based the R column starting at R2. If the value
in
E2 says infant then I would need R2 filled with Daycare otherwise the cell
would need to be filled with preschool.

Is there a wat I can put this into VB code so that it search the columns and
fills the columns when the sheet is populate each time. Each time we load
the sheet the data and rows vary.

"JulieD" wrote:

Hi Bill

in cell R2
=IF(E2="infant","Daycare","preschool")
this can then be filled down column R

however, please note, if there is nothing in E2 you will still get
"preschool" - so the following formula might be better
=IF(E2="","",IF(E2="infant","Daycare","preschool") )

Regards
JuileD


"Bill" wrote in message
...
I am trying to fill the column starting at R2 going down based on the data
contained in cell E2 going down. I want something like an If statement ot
fill the cells going down based the R column starting at R2. If the value
in
E2 says infant then I would need R2 filled with Daycare otherwise the cell
would need to be filled with preschool.

Bill, try this:

Sub addType()
Dim eCnt&, X&
eCnt = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row
'Assuming Headings in 1st row...
For X = 2 To eCnt
If UCase(Cells(X, 5).Text) = "INFANT" Then
Cells(X, 18) = "Daycare"
Else
Cells(X, 18) = "Preschool"
End If
Next X
End Sub

HTH--Lonnie M.

Hi Bill

yes there probably is ... however if you don't mind, i'ld like to know a bit
more about the sheet - when you say you 'load' the sheet - where does the
data fill ... is it all columns or only say columns A through E. Also what
is in column Q, does column Q always have data in it for the same number of
rows as column E?

Additionally, how are you "loading" the data - is it an import or a copy &
paste or is some poor person typing it all in?

Cheers
JulieD



"Bill" wrote in message
...
Is there a wat I can put this into VB code so that it search the columns
and
fills the columns when the sheet is populate each time. Each time we load
the sheet the data and rows vary.

"JulieD" wrote:

Hi Bill

in cell R2
=IF(E2="infant","Daycare","preschool")
this can then be filled down column R

however, please note, if there is nothing in E2 you will still get
"preschool" - so the following formula might be better
=IF(E2="","",IF(E2="infant","Daycare","preschool") )

Regards
JuileD


"Bill" wrote in message
...
I am trying to fill the column starting at R2 going down based on the
data
contained in cell E2 going down. I want something like an If statement
ot
fill the cells going down based the R column starting at R2. If the
value
in
E2 says infant then I would need R2 filled with Daycare otherwise the
cell
would need to be filled with preschool.

Bill, if you want it to do this each time you open the workbook, along
with the example above in a standard module, add this within the
'ThisWorkbook' module:
Public Sub Workbook_Open()
addType
End Sub

If you want it to update every time that sheet changes use a change
event in that sheet's module:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 5 Then
Application.EnableEvents = False
Call addType
Application.EnableEvents = True
End If
End Sub

Lonnie this filled the column. However Row 2 contained infant and the word
Daycare was inserted into row 18. Row 3 contained child but infant instead
of Preschool was listed in column 18. All the cells in the column displayed
infant no matter what column was listed in column E cells

"Lonnie M." wrote:

Bill, try this:

Sub addType()
Dim eCnt&, X&
eCnt = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row
'Assuming Headings in 1st row...
For X = 2 To eCnt
If UCase(Cells(X, 5).Text) = "INFANT" Then
Cells(X, 18) = "Daycare"
Else
Cells(X, 18) = "Preschool"
End If
Next X
End Sub

HTH--Lonnie M.

Julie

All the column are populated again from other workbooks. This worbook
serves as a centralized workbook for the information. The other workbooks
are adjusted throughout the day. So the number of rows may go up or down each
day.

"JulieD" wrote:

Hi Bill

yes there probably is ... however if you don't mind, i'ld like to know a bit
more about the sheet - when you say you 'load' the sheet - where does the
data fill ... is it all columns or only say columns A through E. Also what
is in column Q, does column Q always have data in it for the same number of
rows as column E?

Additionally, how are you "loading" the data - is it an import or a copy &
paste or is some poor person typing it all in?

Cheers
JulieD



"Bill" wrote in message
...
Is there a wat I can put this into VB code so that it search the columns
and
fills the columns when the sheet is populate each time. Each time we load
the sheet the data and rows vary.

"JulieD" wrote:

Hi Bill

in cell R2
=IF(E2="infant","Daycare","preschool")
this can then be filled down column R

however, please note, if there is nothing in E2 you will still get
"preschool" - so the following formula might be better
=IF(E2="","",IF(E2="infant","Daycare","preschool") )

Regards
JuileD


"Bill" wrote in message
...
I am trying to fill the column starting at R2 going down based on the
data
contained in cell E2 going down. I want something like an If statement
ot
fill the cells going down based the R column starting at R2. If the
value
in
E2 says infant then I would need R2 filled with Daycare otherwise the
cell
would need to be filled with preschool.

Lonnie

We put it in the workbook_open

Lonnie this filled the column. However Row 2 contained infant and the word
Daycare was inserted into row 18. Row 3 contained child but infant instead
of Preschool was listed in column 18. All the cells in the column displayed
infant no matter what column was listed in column E cells


"Lonnie M." wrote:

Bill, if you want it to do this each time you open the workbook, along
with the example above in a standard module, add this within the
'ThisWorkbook' module:
Public Sub Workbook_Open()
addType
End Sub

If you want it to update every time that sheet changes use a change
event in that sheet's module:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 5 Then
Application.EnableEvents = False
Call addType
Application.EnableEvents = True
End If
End Sub

Hi, thanks for your feedback, I just tested it again and it worked just
fine for me.
If I placed "infant" in "E2", "R2" correctly placed "Daycare"
If I placed "child" in "E3", "R3" correctly placed "Preschool"
If I placed "6" in "E4", "R4" correctly placed "Preschool"

I'm not sure what went wrong for you. You may want to make sure that
the value in the else statement wasn't changed to "infant"
HTH--Lonnie M.

Hi Joe

this code will run each time something on the sheet is changed:
---
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Columns(18).Value = Null

For i = 2 To ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row
If UCase(Cells(i, 5).Text) = "INFANT" Then
Cells(i, 18) = "Daycare"
ElseIf Cells(i, 5).Text < "" Then
Cells(i, 18) = "Preschool"
End If
Next
Application.EnableEvents = True
End Sub
---

to use, right mouse click on the sheet tab, choose view code - copy & paste
the code into the right hand side of the screen.

Hope this helps
Cheers
JulieD



"Joe" wrote in message
...
Julie

All the column are populated again from other workbooks. This worbook
serves as a centralized workbook for the information. The other workbooks
are adjusted throughout the day. So the number of rows may go up or down
each
day.

"JulieD" wrote:

Hi Bill

yes there probably is ... however if you don't mind, i'ld like to know a
bit
more about the sheet - when you say you 'load' the sheet - where does the
data fill ... is it all columns or only say columns A through E. Also
what
is in column Q, does column Q always have data in it for the same number
of
rows as column E?

Additionally, how are you "loading" the data - is it an import or a copy
&
paste or is some poor person typing it all in?

Cheers
JulieD



"Bill" wrote in message
...
Is there a wat I can put this into VB code so that it search the
columns
and
fills the columns when the sheet is populate each time. Each time we
load
the sheet the data and rows vary.

"JulieD" wrote:

Hi Bill

in cell R2
=IF(E2="infant","Daycare","preschool")
this can then be filled down column R

however, please note, if there is nothing in E2 you will still get
"preschool" - so the following formula might be better
=IF(E2="","",IF(E2="infant","Daycare","preschool") )

Regards
JuileD


"Bill" wrote in message
...
I am trying to fill the column starting at R2 going down based on the
data
contained in cell E2 going down. I want something like an If
statement
ot
fill the cells going down based the R column starting at R2. If the
value
in
E2 says infant then I would need R2 filled with Daycare otherwise
the
cell
would need to be filled with preschool.

Lonnie this filled the column. However Row 2 contained infant and the
word
Daycare was inserted into row 18. Row 3 contained child but infant
instead
of Preschool was listed in column 18.

This doesn't make much sense. The first time through the loop it
inserted the first result in "Row" 18? Which Column?
Then the next time through the loop it placed the result in the proper
row & column?

All the cells in the column displayed
infant no matter what column was listed in column E cells

Did you change any of my example? If so please post the code just as
you used it.

Thanks, Lonnie M.

For i = 2 To ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row
If Cells(i, 5).Text = "DAY CARE" Then
Cells(i, 18) = "INFANT"
Else
Cells(i, 18) = "CHILD"
End If
Next



"Lonnie M." wrote:

Hi, thanks for your feedback, I just tested it again and it worked just
fine for me.
If I placed "infant" in "E2", "R2" correctly placed "Daycare"
If I placed "child" in "E3", "R3" correctly placed "Preschool"
If I placed "6" in "E4", "R4" correctly placed "Preschool"

I'm not sure what went wrong for you. You may want to make sure that
the value in the else statement wasn't changed to "infant"
HTH--Lonnie M.

Both sets of code produce the same results. I gave you the variables in the
wrong spot. I think the problem is that Day Care is two words in our system.
If I make it one word it works great but our system has it listed as two
words.

For mm = 2 To ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row
If Cells(mm, 5).Text = "DAY CARE" Then
Cells(mm, 18) = "INFANT"
Else
Cells(mm, 18) = "CHILD"
End If
Next


"Lonnie M." wrote:

Hi, thanks for your feedback, I just tested it again and it worked just
fine for me.
If I placed "infant" in "E2", "R2" correctly placed "Daycare"
If I placed "child" in "E3", "R3" correctly placed "Preschool"
If I placed "6" in "E4", "R4" correctly placed "Preschool"

I'm not sure what went wrong for you. You may want to make sure that
the value in the else statement wasn't changed to "infant"
HTH--Lonnie M.

That code is backwards from what you described:

If the value in
E2 says infant then I would need R2 filled with Daycare otherwise the cell
would need to be filled with preschool.

Also, notice how you have Daycare as one word.

--
Regards,
Tom Ogilvy


"Bill" wrote in message
...
Both sets of code produce the same results. I gave you the variables in
the
wrong spot. I think the problem is that Day Care is two words in our
system.
If I make it one word it works great but our system has it listed as two
words.

For mm = 2 To ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row
If Cells(mm, 5).Text = "DAY CARE" Then
Cells(mm, 18) = "INFANT"
Else
Cells(mm, 18) = "CHILD"
End If
Next


"Lonnie M." wrote:

Hi, thanks for your feedback, I just tested it again and it worked just
fine for me.
If I placed "infant" in "E2", "R2" correctly placed "Daycare"
If I placed "child" in "E3", "R3" correctly placed "Preschool"
If I placed "6" in "E4", "R4" correctly placed "Preschool"

I'm not sure what went wrong for you. You may want to make sure that
the value in the else statement wasn't changed to "infant"
HTH--Lonnie M.