Simple VBA IF THEN to fill a cell with a value

NYEXCEL1

Board Regular
Joined
Apr 17, 2013
Messages
121
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,077
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
121
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
46,077
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
121

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
46,077
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
121

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
46,077
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
121
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
46,077
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,624
Messages
5,523,966
Members
409,547
Latest member
AW2020

This Week's Hot Topics

Top