Changing Value in Column B Based on Values in Column D

[kescco]

A	B	C	D
1	2	3	4

<tbody>
</tbody>

I am looking for an effective VBA solution to change B to 3 if D is either 5, 6, or 7.

My actual spreadsheet is different but I am trying to get the functional argument. I will change the VBA
accordingly if someone can help.

Thank You,

KESCCO

 

[kescco]

Thanks so much to both of you. This will really help me with a project I have been working on for weeks.

May I ask if you think it is possible to add another Column to the code presented? While examining my project, I realized I am going to need to be able
to do a change based on values in two columns.

A B C D
1 2 3 4

I need to be able to say if C & D are 3 & 5 for B to change. In fact, there are three vaiables in real life.

If C is either 1000, 2000, and 3000 and B is 20, 60, or 10 I need to change B to 311FA.

I know this is complicated and I understand if you do not have time to help any further. Maybe I can post it
with an attached graphic in another post to make it easier to understand.

Once again, I appreciate all your help already. It is appreciated.

Kescco

 

[Rick Rothstein]

So, adjusting for different variable name lengths, optional arguments that I included and the range .Value property that I included we have a two very similar looking codes that differ by 6 characters in written form and for my testing up to about 10,000 rows showed no discernible speed difference. So not much difference at all really as far as I can see.

Six characters is six characters... sound significant to me. By the way, we can both save some characters (you more than me) by calculating the Column B range address this way...

AddrB = Replace(AddrD, "D", "B")

 

[Rick Rothstein]

I need to be able to say if C & D are 3 & 5 for B to change. In fact, there are three vaiables in real life.

If C is either 1000, 2000, and 3000 and B is 20, 60, or 10 I need to change B to 311FA.

What happened to the Column D values in that second line? Also I don't see how the "3 & 5" relate to the "1000, 2000 and 3000".

 

[hiker95]

kescco,

am now really trying to learn more about VBA.

See if something, in the below link, to my most up to date list will help you:


Training / Books / Sites as of 10/08/2014

http://www.mrexcel.com/forum/excel-...applications-merging-columns.html#post3961033

 

[Peter_SSs]

By the way, we can both save some characters (you more than me) by calculating the Column B range address this way...

AddrB = Replace(AddrD, "D", "B")

We could do that, but I wouldn't do it because it's shorter. I might do it because it is faster, though if I decided to change for that reason I might go for the longer but marginally faster again:

Dim lr As Long
Dim AddrB As String, AddrD As String

lr = Cells(Rows.Count, 4).End(xlUp).Row
AddrD = "D2:D" & lr
AddrB = "B2:B" & lr

Still only takes one copy/paste from the forum