Changing Value in Column B Based on Values in Column D

kescco

Board Regular
Joined
Sep 14, 2009
Messages
174
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Column B will have the actual value in it. Thus, the formula does not seem to make sense to me.
 
Upvote 0
So column B will have a value, but when you run your VBA cobe via a button i'm assuming, that's when you will want it to change? Is that correct?
 
Upvote 0
Test this in a copy of your workbook.

It assumes data starts in row 2.

Code:
[color=darkblue]Sub[/color] ChangeValues()
  [color=darkblue]Dim[/color] Dadr [color=darkblue]As[/color] [color=darkblue]String[/color], Badr [color=darkblue]As[/color] [color=darkblue]String[/color]
  
  Dadr = "D2:D" & Range("D" & Rows.Count).End(xlUp).Row
  Badr = "B2:B" & Range("D" & Rows.Count).End(xlUp).Row
  Range(Badr).Value = Evaluate(Replace(Replace("if(#=5,3,if(#=6,3,if(#=7,3,if(len(@),@,""""))))", "@", Badr, 1, -1, 1), "#", Dadr, 1, -1, 1))
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Last edited:
Upvote 0
I will try the code, and try to learn from it. I have used Excel for some time, but am now really trying to learn more about VBA.

Thank you so much for your help.

I appreciate it very much.
 
Upvote 0
Test this in a copy of your workbook.

It assumes data starts in row 2.

Code:
[COLOR=darkblue]Sub[/COLOR] ChangeValues()
  [COLOR=darkblue]Dim[/COLOR] Dadr [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], Badr [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
  
  Dadr = "D2:D" & Range("D" & Rows.Count).End(xlUp).Row
  Badr = "B2:B" & Range("D" & Rows.Count).End(xlUp).Row
  Range(Badr).Value = Evaluate(Replace(Replace("if(#=5,3,if(#=6,3,if(#=7,3,if(len(@),@,""""))))", "@", Badr, 1, -1, 1), "#", Dadr, 1, -1, 1))
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
Here is another way to write that code...
Code:
Sub ChangeValues()
  Dim Addr As String
  Addr = "D2:D" & Cells(Rows.Count, "D").End(xlUp).Row
  Range(Addr).Offset(, -2) = Evaluate(Replace("IF((@=5)+(@=6)+(@=7),3,OFFSET(@,,-2))", "@", Addr))
End Sub
 
Last edited:
Upvote 0
It is not quite the same. If column D is not 5, 6 or 7 and column B is blank, your code replaces the blank with a zero.
Good catch. This is the modified code to handle that..
Code:
Sub ChangeValues()
  Dim AddrB As String, AddrD As String
  AddrD = "D2:D" & Cells(Rows.Count, "D").End(xlUp).Row
  AddrB = Range(AddrD).Offset(, -2).Address
  Range(AddrB) = Evaluate(Replace(Replace("IF((@=5)+(@=6)+(@=7),3,IF($="""","""",$))", "@", AddrD), "$", AddrB))
End Sub
 
Upvote 0
Good catch. This is the modified code to handle that..
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. :)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top