Enter Data Macro
 

I need help with a macro. Here is what I am trying to do. Search every cell
in Column B. When the first two numbers are 20 then 2ZZP will be entered to
the left of that cell in Column A. When the first two numbers are 30 then
3ZZP will be entered to the left of that cell in Column A. When the first
two numbers are 40 then 4ZZP will be entered to the left of that cell in
Column A. Can you help me with this?

Enter Data Macro
 

MCheru,

Try this code. Hope it helps.

Sub MyRoutine

nRow_ctr = 1

Do While Worksheets("Sheet1").Range("B" & nRow_ctr) < ""
If Left(Worksheets("Sheet1").Range("B" & nRow_ctr), 2) = 20 Or _
Left(Worksheets("Sheet1").Range("B" & nRow_ctr), 2) = 30 Or _
Left(Worksheets("Sheet1").Range("B" & nRow_ctr), 2) = 40 Then
Worksheets("Sheet1").Range("A" & nRow_ctr) = Left(Worksheets
("Sheet1").Range("B" & nRow_ctr), 1) & "ZZP"
End If

nRow_ctr = nRow_ctr + 1

If Worksheets("Sheet1").Range("B" & nRow_ctr) = "" Then
Exit Sub
End If
Loop

"MCheru" wrote:

I need help with a macro. Here is what I am trying to do. Search every cell
in Column B. When the first two numbers are 20 then 2ZZP will be entered to
the left of that cell in Column A. When the first two numbers are 30 then
3ZZP will be entered to the left of that cell in Column A. When the first
two numbers are 40 then 4ZZP will be entered to the left of that cell in
Column A. Can you help me with this?

Enter Data Macro
 

Sub codeif()
lr = Cells(Rows.Count, "b").End(xlUp).Row
For Each c In Range("b2:b" & lr)
mv = Left(c, 2)
If mv = 20 Or mv = 30 Or mv = 40 Then
c.Offset(, -1) = Left(c, 1) & "ZZP"
End If
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MCheru" wrote in message
...
I need help with a macro. Here is what I am trying to do. Search every
cell
in Column B. When the first two numbers are 20 then 2ZZP will be entered
to
the left of that cell in Column A. When the first two numbers are 30
then
3ZZP will be entered to the left of that cell in Column A. When the first
two numbers are 40 then 4ZZP will be entered to the left of that cell in
Column A. Can you help me with this?

Enter Data Macro
 

This is a very helpful code. It does what I asked for help with. Thank you.

I have one more question now that I probably should've asked initally but it
slipped my mind. Sometimes the numbers change for instance.
20 could become ZZP5
30 could become ZZP1
40 could become ZZP3

How can I change the code to accomodate this change when it happens?

"Francis Ang" wrote:

MCheru,

Try this code. Hope it helps.

Sub MyRoutine

nRow_ctr = 1

Do While Worksheets("Sheet1").Range("B" & nRow_ctr) < ""
If Left(Worksheets("Sheet1").Range("B" & nRow_ctr), 2) = 20 Or _
Left(Worksheets("Sheet1").Range("B" & nRow_ctr), 2) = 30 Or _
Left(Worksheets("Sheet1").Range("B" & nRow_ctr), 2) = 40 Then
Worksheets("Sheet1").Range("A" & nRow_ctr) = Left(Worksheets
("Sheet1").Range("B" & nRow_ctr), 1) & "ZZP"
End If

nRow_ctr = nRow_ctr + 1

If Worksheets("Sheet1").Range("B" & nRow_ctr) = "" Then
Exit Sub
End If
Loop

"MCheru" wrote:

I need help with a macro. Here is what I am trying to do. Search every cell
in Column B. When the first two numbers are 20 then 2ZZP will be entered to
the left of that cell in Column A. When the first two numbers are 30 then
3ZZP will be entered to the left of that cell in Column A. When the first
two numbers are 40 then 4ZZP will be entered to the left of that cell in
Column A. Can you help me with this?

Enter Data Macro
 

Thank you. Like the other code this works very well.

I mentioned to Francis just in case you might be able to help as well. I
have one more question now that I probably should've asked initally but it
slipped my mind. Sometimes the numbers change for instance.
20 could become ZZP5
30 could become ZZP1
40 could become ZZP3

How can I change the code to accomodate this change when it happens?

"Don Guillett" wrote:

Sub codeif()
lr = Cells(Rows.Count, "b").End(xlUp).Row
For Each c In Range("b2:b" & lr)
mv = Left(c, 2)
If mv = 20 Or mv = 30 Or mv = 40 Then
c.Offset(, -1) = Left(c, 1) & "ZZP"
End If
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MCheru" wrote in message
...
I need help with a macro. Here is what I am trying to do. Search every
cell
in Column B. When the first two numbers are 20 then 2ZZP will be entered
to
the left of that cell in Column A. When the first two numbers are 30
then
3ZZP will be entered to the left of that cell in Column A. When the first
two numbers are 40 then 4ZZP will be entered to the left of that cell in
Column A. Can you help me with this?

Enter Data Macro
 

Based on what you say:

Sub codeif_1()
lr = Cells(Rows.Count, "b").End(xlUp).Row
For Each c In Range("b2:b" & lr)
mv = Left(c, 2)
If mv = 20 Or mv = 30 Or mv = 40 Then
Select Case mv
Case 20: y = 5
Case 30: y = 1
Case 40: y = 3
Case Else
End Select
c.Offset(, -1) = Left(c, 1) & "ZZP" & y
End If
Next c
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MCheru" wrote in message
...
Thank you. Like the other code this works very well.

I mentioned to Francis just in case you might be able to help as well. I
have one more question now that I probably should've asked initally but it
slipped my mind. Sometimes the numbers change for instance.
20 could become ZZP5
30 could become ZZP1
40 could become ZZP3

How can I change the code to accomodate this change when it happens?

"Don Guillett" wrote:

Sub codeif()
lr = Cells(Rows.Count, "b").End(xlUp).Row
For Each c In Range("b2:b" & lr)
mv = Left(c, 2)
If mv = 20 Or mv = 30 Or mv = 40 Then
c.Offset(, -1) = Left(c, 1) & "ZZP"
End If
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MCheru" wrote in message
...
I need help with a macro. Here is what I am trying to do. Search every
cell
in Column B. When the first two numbers are 20 then 2ZZP will be
entered
to
the left of that cell in Column A. When the first two numbers are 30
then
3ZZP will be entered to the left of that cell in Column A. When the
first
two numbers are 40 then 4ZZP will be entered to the left of that cell
in
Column A. Can you help me with this?

Enter Data Macro
 

This is very good. Thank you.

"Don Guillett" wrote:

Based on what you say:

Sub codeif_1()
lr = Cells(Rows.Count, "b").End(xlUp).Row
For Each c In Range("b2:b" & lr)
mv = Left(c, 2)
If mv = 20 Or mv = 30 Or mv = 40 Then
Select Case mv
Case 20: y = 5
Case 30: y = 1
Case 40: y = 3
Case Else
End Select
c.Offset(, -1) = Left(c, 1) & "ZZP" & y
End If
Next c
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MCheru" wrote in message
...
Thank you. Like the other code this works very well.

I mentioned to Francis just in case you might be able to help as well. I
have one more question now that I probably should've asked initally but it
slipped my mind. Sometimes the numbers change for instance.
20 could become ZZP5
30 could become ZZP1
40 could become ZZP3

How can I change the code to accomodate this change when it happens?

"Don Guillett" wrote:

Sub codeif()
lr = Cells(Rows.Count, "b").End(xlUp).Row
For Each c In Range("b2:b" & lr)
mv = Left(c, 2)
If mv = 20 Or mv = 30 Or mv = 40 Then
c.Offset(, -1) = Left(c, 1) & "ZZP"
End If
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MCheru" wrote in message
...
I need help with a macro. Here is what I am trying to do. Search every
cell
in Column B. When the first two numbers are 20 then 2ZZP will be
entered
to
the left of that cell in Column A. When the first two numbers are 30
then
3ZZP will be entered to the left of that cell in Column A. When the
first
two numbers are 40 then 4ZZP will be entered to the left of that cell
in
Column A. Can you help me with this?

Enter Data Macro
 

Glad it helped


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MCheru" wrote in message
...
This is very good. Thank you.

"Don Guillett" wrote:

Based on what you say:

Sub codeif_1()
lr = Cells(Rows.Count, "b").End(xlUp).Row
For Each c In Range("b2:b" & lr)
mv = Left(c, 2)
If mv = 20 Or mv = 30 Or mv = 40 Then
Select Case mv
Case 20: y = 5
Case 30: y = 1
Case 40: y = 3
Case Else
End Select
c.Offset(, -1) = Left(c, 1) & "ZZP" & y
End If
Next c
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MCheru" wrote in message
...
Thank you. Like the other code this works very well.

I mentioned to Francis just in case you might be able to help as well.
I
have one more question now that I probably should've asked initally but
it
slipped my mind. Sometimes the numbers change for instance.
20 could become ZZP5
30 could become ZZP1
40 could become ZZP3

How can I change the code to accomodate this change when it happens?

"Don Guillett" wrote:

Sub codeif()
lr = Cells(Rows.Count, "b").End(xlUp).Row
For Each c In Range("b2:b" & lr)
mv = Left(c, 2)
If mv = 20 Or mv = 30 Or mv = 40 Then
c.Offset(, -1) = Left(c, 1) & "ZZP"
End If
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MCheru" wrote in message
...
I need help with a macro. Here is what I am trying to do. Search
every
cell
in Column B. When the first two numbers are 20 then 2ZZP will be
entered
to
the left of that cell in Column A. When the first two numbers are
30
then
3ZZP will be entered to the left of that cell in Column A. When the
first
two numbers are 40 then 4ZZP will be entered to the left of that
cell
in
Column A. Can you help me with this?