| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| macro to enter data into different line | Excel Programming | |||
| Macro to select cells in column enter data then press enter | New Users to Excel | |||
| enter data in cell which will start macro to move data to sheet2 | Excel Discussion (Misc queries) | |||
| Pause Macro to enter data in cell | Excel Programming | |||
| Prompt User to Enter Data with a macro | Excel Programming | |||
Linear Mode
