Macro to create a new value

[Ucpaul]

I appreciate if you could help me with a macro to handle this: I have a column A with Unit number & column B with name. I need to assign a new value to column C:

if Unit =1 & Label = James, then value in column C = 1;
If Unit = 1 & Label = Gregory, then value in column C = 2;
........... (continued for all rows)

[HaHoBe]

Hi, Ucpaul,

resetting the number to start with 1 on each change in Column A may look like this:

Please Login or Register  to view this content.

Ciao,
Holger

[jindon]

I don't know why you need this a macro....

Please Login or Register  to view this content.

[Ucpaul]

I don't know why you need this a macro....

Please Login or Register  to view this content.

Thank you, Hahobe & Jindon for the macro. I have a bout million of records and need macro for a faster job.

Anyway, both of you missed my question, please see example below:

if Unit =1 & Label = James, then value in column C = 1;
If Unit = 1 & Label = Gregory, then value in column C = 2;
...........................

It means that if Label is not James or Gregore, then value in column C will be empty.

[HaHoBe]

Hi, Ucpaul,

I can only speak for myself: why did you post an image that doesnīt show the wanted output which somehow has influenced the answers to your question?

Please Login or Register  to view this content.

Ciao,
Holger

[jindon]

why did you post an image that doesnīt show the wanted output which somehow has influenced the answers to your question?

Agreed

Please Login or Register  to view this content.

[Ucpaul]

Agreed

Please Login or Register  to view this content.

Hello Guys,

Sorry that I confused you with my previous attached. I revised it and the answer should be something like below (I "borrowed" your macro and tried rewiting it. I appreciate if you could make it be shorter and work). Thank you for help and kindness.

revised.jpg
Range("a2", Range("a" & Rows.Count).End(xlUp)).Columns(3).Formula = "=if(and(a2=1,b2={""James""}),1,"""")"
Range("a2", Range("a" & Rows.Count).End(xlUp)).Columns(3).Formula = "=if(and(a2=1,b2={""Gregory""}),2,"""")"
Range("a2", Range("a" & Rows.Count).End(xlUp)).Columns(3).Formula = "=if(and(a2=2,b2={""Donovan""}),1,"""")"
Range("a2", Range("a" & Rows.Count).End(xlUp)).Columns(3).Formula = "=if(and(a2=2,b2={""John""}),4,"""")"

[Ucpaul]

Need help with this: if I run the above, it just run the last command.

[HaHoBe]

Hi, Ucpaul,

the solution jindon delivered was certainly based on the last sentence in post #4 (the readers of this post do not know what the dots stand for):

It means that if Label is not James or Gregore, then value in column C will be empty.

You still publish pictures - itīs true: a picture is worth a thousand words. But is your Excel able to make values from these pictures? Or show more than a static view from a word document? A workbook to have a look at both the data as well as the wanted outcome would have been helpful in the opening post as a lot of efforts would have been directed into the right direction from the start. For my part: sorry but I didnīt get the information from your posts until after I had answered while I should have known them before to adapt into my "efforts".

it just run the last command.

Thatīs what you told VBA to do as you referenced the very same range four times. Maybe alter the formula in the first cell to suit your needs and then have that formula being written to the immediate window after entering

Please Login or Register  to view this content.

and hitting enter. Please mind that any " inside the formula would need to be doubled up.

But I wonder why you would want VBA to insert this formula which could easily be dragged down by double-clicking on the lower right corner of the cell containing the correct formula.

Slow and clumpsy but the code I provided could be amended without problems.

Ciao,
Holger