Simple VBA IF THEN to fill a cell with a value

NYEXCEL1

Board Regular
Joined
Apr 17, 2013
Messages
123
I dont have much experience with VBA but know this would be the best way to populate a long document.

If I have a value in column B beginning with cell B2 put the value "2019" in cell J2

Thats it!!



Additionally, i currently use a formula to remove all the data in a cell after a particular character ("!"), using the FIND and LEFT functions, can you add the same formula to VBA and replace the cell with the updated value?

thanks all
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,043
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Sub Nyexcel1()
   With Range("J2", Range("B" & Rows.Count).End(xlUp).Offset(, 8))
      .Value = Evaluate("if(" & .Offset(, -8).Address & "<>"""",2019,"""")")
   End With
End Sub
 

NYEXCEL1

Board Regular
Joined
Apr 17, 2013
Messages
123
Unfortunately i see that blank cells in Column B have a space in them which is causing the the 2019 to be populated in every cell in column J. Is there a way to exclude those cells with this blank space criteria? I upload this template so i would rather not just use TRIM to get rid of these as I do not know what effect it will have


thanks!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,043
Office Version
  1. 365
Platform
  1. Windows
Just add a space as shown
Code:
Sub Nyexcel1()
   With Range("J2", Range("B" & Rows.Count).End(xlUp).Offset(, 8))
      .Value = Evaluate("if(" & .Offset(, -8).Address & "<>[COLOR=#ff0000]"" ""[/COLOR],2019,"""")")
   End With
End Sub
 

NYEXCEL1

Board Regular
Joined
Apr 17, 2013
Messages
123

ADVERTISEMENT

Thanks for the quick reply. Still not working and it populates cells that have a blank. could you walk me through the VBA ?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,043
Office Version
  1. 365
Platform
  1. Windows
The best thing would be to make the blanks totally empty, otherwise you will probably get problems further down the line.
If you do not want to do that we need to find out what is in those "blank" cells.
Point these formulae at one of the "blanks"
=isblank(a1)
=len(a1)
=code(a1)
what do they return?
 

NYEXCEL1

Board Regular
Joined
Apr 17, 2013
Messages
123

ADVERTISEMENT

For the blank cell i get the following formula answers in your order: TRUE, 0, #VALUE
For the cell i referenced with the space: FALSE, 1, 32
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,043
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
Code:
Sub Nyexcel1()
   With Range("J2", Range("B" & Rows.Count).End(xlUp).Offset(, 8))
      .Value = Evaluate(Replace("if((@ ="" "")+(@=""""),"""",2019)", "@", .Offset(, -8).Address))
   End With
End Sub
 

NYEXCEL1

Board Regular
Joined
Apr 17, 2013
Messages
123
thank you that worked. could you explain the change, specifically the "@" symbol . what does that do?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,043
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
The @ is just a "place holder" & is replaced by .Offset(, -8).Address courtesy of the Replace function.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,598
Messages
5,832,644
Members
430,150
Latest member
amitk1

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
Top